[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO wip_ws_fpy
(
ORGANIZATION_ID,
DEPARTMENT_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
INVENTORY_ITEM_ID,
SHIFT_NUM,
SHIFT_DATE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
QUANTITY_FIRST_PASS
)
SELECT
wdj.organization_id,
completed_info.department_id,
completed_info.wip_entity_id as wip_entity_id,
completed_info.operation_seq_num as operation_seq_num,
wdj.primary_item_id,
completed_info.shift_num as shift_num,
completed_info.shift_start_date as shift_date,
0 as quantity_rejected,
0 as quantity_scraped,
completed_info.quantity_compelted,
g_request_id as REQUEST_ID,
p_execution_date as LAST_UPDATE_DATE,
g_user_id as LAST_UPDATED_BY,
p_execution_date as CREATION_DATE,
g_user_id as CREATED_BY,
g_login_id as LAST_UPDATE_LOGIN,
g_prog_id as PROGRAM_ID,
g_prog_appid as PROGRAM_APPLICATION_ID,
p_execution_date as PROGRAM_UPDATE_DATE,
0 as QUANTITY_FIRST_PASS
FROM
(
SELECT
wop.department_id,
wmt.shift_start_date,
wmt.shift_num,
wop.wip_entity_id,
wop.operation_seq_num,
NVL(SUM(wmt.primary_quantity *
DECODE(SIGN(wmt.to_operation_seq_num -wmt.fm_operation_seq_num),
0,DECODE(SIGN(wmt.fm_intraoperation_step_type -WIP_CONSTANTS.RUN),
0,DECODE(SIGN(wmt.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
-1,DECODE(SIGN(wmt.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
1,-1),
1, 1,
-1,-1)
),0) quantity_compelted
FROM
(
SELECT
transaction_date+ mod(shift_info,1)*1000 shift_start_date,
abs(mod(trunc(shift_info),100)) as shift_num,
wip_entity_id,
primary_quantity,
to_operation_seq_num,
to_intraoperation_step_type,
fm_operation_seq_num,
fm_intraoperation_step_type
FROM
(
SELECT
wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id, wmt.transaction_date) as shift_info,
wmt.*
FROM
wip_move_transactions wmt
WHERE
wmt.transaction_date > p_cutoff_date
AND wmt.organization_id = p_org_id
)
) wmt,
wip_operations wop
WHERE
wop.organization_id = p_org_id
AND wop.wip_entity_id = wmt.wip_entity_id
AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR wop.operation_seq_num = wmt.fm_operation_seq_num
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
OR
(wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
OR (wop.operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))))
GROUP BY wop.department_id, wmt.shift_start_date, wmt.shift_num, wop.wip_entity_id, wop.operation_seq_num
) completed_info,
WIP_DISCRETE_JOBS wdj
WHERE wdj.wip_entity_id = completed_info.wip_entity_id
AND wdj.organization_id = p_org_id;
INSERT INTO wip_ws_fpy
(
ORGANIZATION_ID,
DEPARTMENT_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
INVENTORY_ITEM_ID,
SHIFT_NUM,
SHIFT_DATE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
QUANTITY_FIRST_PASS
)
SELECT
wdj.organization_id,
completed_info.department_id,
completed_info.wip_entity_id,
completed_info.operation_seq_num,
wdj.primary_item_id,
completed_info.shift_num,
completed_info.shift_start_date,
0 as quantity_rejected,
0 as quantity_scraped,
completed_info.quantity_compelted,
g_request_id as REQUEST_ID,
p_execution_date as LAST_UPDATE_DATE,
g_user_id as LAST_UPDATED_BY,
p_execution_date as CREATION_DATE,
g_user_id as CREATED_BY,
g_login_id as LAST_UPDATE_LOGIN,
g_prog_id as PROGRAM_ID,
g_prog_appid as PROGRAM_APPLICATION_ID,
p_execution_date as PROGRAM_UPDATE_DATE,
0 as QUANTITY_FIRST_PASS
FROM
(
SELECT
post_cal.department_id,
post_cal.shift_start_date,
post_cal.shift_num,
post_cal.wip_entity_id,
post_cal.operation_seq_num,
NVL(SUM(post_cal.primary_quantity *
DECODE(SIGN(post_cal.to_operation_seq_num -post_cal.fm_operation_seq_num),
0,DECODE(SIGN(post_cal.fm_intraoperation_step_type -WIP_CONSTANTS.RUN),
0,DECODE(SIGN(post_cal.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
-1,DECODE(SIGN(post_cal.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
1,-1),
1, 1,
-1,-1)
),0) quantity_compelted
FROM
(
SELECT
pre_cal.department_id,
pre_cal.primary_quantity,
pre_cal.wip_entity_id,
pre_cal.operation_seq_num,
(case when (pre_cal.shift_info is not null) then pre_cal.transaction_date+ mod(pre_cal.shift_info,1)*1000 else trunc(pre_cal.transaction_date) end )as shift_start_date,
(case when (pre_cal.shift_info is not null) then abs(mod(trunc(pre_cal.shift_info),100)) else -1 end)as shift_num,
pre_cal.fm_operation_seq_num,
pre_cal.to_operation_seq_num,
pre_cal.fm_intraoperation_step_type,
pre_cal.to_intraoperation_step_type
FROM
(
SELECT
wop.department_id,
wmt.wip_entity_id,
wmt.transaction_id,
wop.operation_seq_num,
wmt.fm_operation_seq_num,
wmt.to_operation_seq_num,
wmt.fm_intraoperation_step_type,
wmt.to_intraoperation_step_type,
wmt.primary_quantity,
wmt.transaction_date,
wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id,wop.department_id, wmt.transaction_date) as shift_info
FROM
wip_move_transactions wmt,
wip_operations wop
WHERE
wmt.transaction_date >= p_cutoff_date
AND wmt.organization_id = p_org_id
AND wop.organization_id = p_org_id
AND wop.wip_entity_id = wmt.wip_entity_id
AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR wop.operation_seq_num = wmt.fm_operation_seq_num
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
OR
(wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
OR (wop.operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))))
) pre_cal
) post_cal
GROUP BY post_cal.department_id, post_cal.shift_start_date, post_cal.shift_num, post_cal.wip_entity_id, post_cal.operation_seq_num
) completed_info,
WIP_DISCRETE_JOBS wdj
WHERE wdj.wip_entity_id = completed_info.wip_entity_id
AND wdj.organization_id = p_org_id;
wip_ws_util.trace_log('Finish Inserting QUANTITY_COMPLETED');
UPDATE
wip_ws_fpy fpy
SET
QUANTITY_SCRAPPED =
nvl((SELECT
NVL(SUM(DECODE(wop.operation_seq_num,wmt.to_operation_seq_num,DECODE(wmt.to_intraoperation_step_type,WIP_CONSTANTS.SCRAP, wmt.primary_quantity,0),0)
- DECODE(wop.operation_seq_num, wmt.fm_operation_seq_num,DECODE(wmt.fm_intraoperation_step_type,WIP_CONSTANTS.SCRAP,wmt.primary_quantity,0),0)),0) as quantity_scrap
FROM
(
SELECT
(case when (shift_info is not null) then transaction_date+ mod(shift_info,1)*1000 else trunc(transaction_date) end )as shift_start_date,
(case when (shift_info is not null) then abs(mod(trunc(shift_info),100)) else -1 end)as shift_num,
wip_entity_id,
primary_quantity,
to_operation_seq_num,
to_intraoperation_step_type,
fm_operation_seq_num,
fm_intraoperation_step_type
FROM
(
SELECT
wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id
,(case when wmt.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP then wmt.to_department_id else wmt.fm_department_id end)
,wmt.transaction_date) as shift_info,
transaction_date,
wip_entity_id,
primary_quantity,
to_operation_seq_num,
to_intraoperation_step_type,
fm_operation_seq_num,
fm_intraoperation_step_type
FROM
wip_move_transactions wmt
WHERE
transaction_date >= p_cutoff_date
AND wmt.organization_id = p_org_id
AND (wmt.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP
OR wmt.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP)
) wmt_raw_shift_info
) wmt,
wip_operations wop
WHERE
wop.wip_entity_id = wmt.wip_entity_id
AND wop.organization_id = p_org_id
AND ((wmt.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP AND wmt.fm_operation_seq_num = wop.operation_seq_num)
OR (wmt.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP AND wmt.to_operation_seq_num = wop.operation_seq_num))
AND fpy.wip_entity_id = wop.wip_entity_id
AND fpy.operation_seq_num = wop.operation_seq_num
AND fpy.shift_num = wmt.shift_num
AND fpy.shift_date = wmt.shift_start_date
AND fpy.organization_id = p_org_id
GROUP BY wmt.shift_start_date, wmt.shift_num, wop.wip_entity_id, wop.operation_seq_num),0);
SELECT
NVL(SUM(DECODE(operation_seq_num,to_operation_seq_num,DECODE(to_intraoperation_step_type,WIP_CONSTANTS.SCRAP, primary_quantity,0),0)
- DECODE(operation_seq_num, fm_operation_seq_num,DECODE(fm_intraoperation_step_type,WIP_CONSTANTS.SCRAP,primary_quantity,0),0)),0) as quantity_scrap
FROM (
SELECT
transaction_date,primary_quantity,to_intraoperation_step_type,fm_intraoperation_step_type,fm_operation_seq_num,to_operation_seq_num,
transaction_date+ mod(shift_info,1)*1000 as shift_start_date,
abs(mod(trunc(shift_info),100)) as shift_num,
wip_entity_id,operation_seq_num
FROM (
SELECT
wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id,(
case when (wmt.TO_DEPARTMENT_ID =WIP_CONSTANTS.SCRAP AND wop.operation_seq_num = wmt.fm_operation_seq_num) then wmt.to_department_id else wmt.fm_department_id end),wmt.transaction_date) as shift_info,
wop.wip_entity_id, wop.operation_seq_num,
wmt.transaction_date,wmt.primary_quantity,wmt.to_intraoperation_step_type,wmt.fm_intraoperation_step_type,fm_operation_seq_num,to_operation_seq_num
FROM
wip_move_transactions wmt,
wip_operations wop
WHERE
wop.wip_entity_id = wmt.wip_entity_id
AND wmt.organization_id = p_org_id
AND wop.organization_id = p_org_id
AND transaction_date >= p_cutoff_date
AND ((wmt.fm_intraoperation_step_type = 5 AND wmt.fm_operation_seq_num = wop.operation_seq_num)
OR (wmt.to_intraoperation_step_type = 5 AND wmt.to_operation_seq_num = wop.operation_seq_num))
)pre_cal
) post_cal
GROUP BY shift_start_date, shift_num, wip_entity_id, operation_seq_num */
------------------------------------ update QUANTITY_REJECTED ------------------------------------
UPDATE
wip_ws_fpy fpy
SET
QUANTITY_REJECTED =
-- Formula = Sum(IN) - Sum(OUT BW)
-- OUT BW = Move backword from REJECT except moving to the 'TOMOVE' within the same opeation
nvl((SELECT
NVL(SUM(DECODE(wop.operation_seq_num,wmt.to_operation_seq_num,
DECODE(wmt.to_intraoperation_step_type,WIP_CONSTANTS.REJECT, wmt.primary_quantity,0),0)
),0)
- NVL(SUM(DECODE(wop.operation_seq_num,fm_operation_seq_num,
DECODE(wmt.fm_intraoperation_step_type,WIP_CONSTANTS.REJECT,
DECODE(SIGN(wmt.fm_operation_seq_num-wmt.to_operation_seq_num),
1,wmt.primary_quantity, -- Out Backward different operation seq
0,DECODE(wmt.to_intraoperation_step_type,WIP_CONSTANTS.TOMOVE,0,wmt.primary_quantity),0) -- Out Backward within same operation seq
),0)
),0) as quantity_reject
FROM
(
SELECT
(case when (shift_info is not null) then transaction_date+ mod(shift_info,1)*1000 else trunc(transaction_date) end )as shift_start_date,
(case when (shift_info is not null) then abs(mod(trunc(shift_info),100)) else -1 end)as shift_num,
wip_entity_id,
primary_quantity,
to_operation_seq_num,
to_intraoperation_step_type,
fm_operation_seq_num,
fm_intraoperation_step_type
FROM
(
SELECT
wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id
,(case when wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT then wmt.to_department_id else wmt.fm_department_id end)
,wmt.transaction_date) as shift_info ,
wmt.*
FROM
wip_move_transactions wmt
WHERE
transaction_date > p_cutoff_date
AND wmt.organization_id = p_org_id
AND (wmt.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT
OR wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT)
) wmt_raw_shift_info
) wmt,
wip_operations wop
WHERE
wop.wip_entity_id = wmt.wip_entity_id
AND wop.organization_id = p_org_id
AND ((wmt.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT AND wmt.fm_operation_seq_num = wop.operation_seq_num )
OR (wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT AND wmt.to_operation_seq_num = wop.operation_seq_num))
AND fpy.wip_entity_id = wop.wip_entity_id
AND fpy.operation_seq_num = wop.operation_seq_num
AND fpy.shift_num = wmt.shift_num
AND fpy.shift_date = wmt.shift_start_date
AND fpy.organization_id = p_org_id
GROUP BY wmt.shift_start_date, wmt.shift_num, wop.wip_entity_id, wop.operation_seq_num),0);
nvl((SELECT
NVL(SUM(DECODE(wop.operation_seq_num,post_cal.to_operation_seq_num,
DECODE(post_cal.to_intraoperation_step_type,WIP_CONSTANTS.REJECT, post_cal.primary_quantity,0),0)
),0)
- NVL(SUM(DECODE(wop.operation_seq_num,fm_operation_seq_num,
DECODE(post_cal.fm_intraoperation_step_type,WIP_CONSTANTS.REJECT,
DECODE(SIGN(post_cal.fm_operation_seq_num-post_cal.to_operation_seq_num),
1,post_cal.primary_quantity, -- Out Backward different operation seq
0,DECODE(post_cal.to_intraoperation_step_type,WIP_CONSTANTS.TOMOVE,0,post_cal.primary_quantity),0) -- Out Backward within same operation seq
),0)
),0) as quantity_reject
FROM
(
SELECT
pre_cal.department_id,
pre_cal.primary_quantity,
pre_cal.wip_entity_id,
pre_cal.operation_seq_num,
pre_cal.transaction_date+ mod(pre_cal.shift_info,1)*1000 as shift_start_date,
abs(mod(trunc(pre_cal.shift_info),100)) as shift_num,
pre_cal.fm_operation_seq_num,
pre_cal.to_operation_seq_num,
pre_cal.fm_intraoperation_step_type,
pre_cal.to_intraoperation_step_type
FROM
(
SELECT
wop.department_id,
wmt.wip_entity_id,
wmt.transaction_id,
wop.operation_seq_num,
wmt.fm_operation_seq_num,
wmt.to_operation_seq_num,
wmt.fm_intraoperation_step_type,
wmt.to_intraoperation_step_type,
wmt.primary_quantity,
wmt.transaction_date,
wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id,null, wmt.transaction_date) as shift_info
FROM
wip_move_transactions wmt,
wip_operations wop
WHERE
wmt.transaction_date >= p_cutoff_date
AND wmt.organization_id = p_org_id
AND wop.organization_id = p_org_id
AND wop.wip_entity_id = wmt.wip_entity_id
AND (wmt.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT
OR wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT)
AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR wop.operation_seq_num = wmt.fm_operation_seq_num
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
OR
(wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
OR (wop.operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))))
) pre_cal
) post_cal,
wip_operations wop
WHERE
wop.wip_entity_id = post_cal.wip_entity_id
AND wop.organization_id = p_org_id
AND ((post_cal.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT AND post_cal.fm_operation_seq_num = wop.operation_seq_num )
OR (post_cal.to_intraoperation_step_type = WIP_CONSTANTS.REJECT AND post_cal.to_operation_seq_num = wop.operation_seq_num))
AND fpy.wip_entity_id = wop.wip_entity_id
AND fpy.operation_seq_num = wop.operation_seq_num
AND fpy.shift_num = post_cal.shift_num
AND fpy.shift_date = post_cal.shift_start_date
AND fpy.organization_id = p_org_id
GROUP BY post_cal.shift_start_date, post_cal.shift_num, wop.wip_entity_id, wop.operation_seq_num),0);
UPDATE
wip_ws_fpy fpy
SET
QUANTITY_FIRST_PASS = QUANTITY_COMPLETED - QUANTITY_REJECTED - QUANTITY_SCRAPPED
WHERE
LAST_UPDATE_DATE = p_execution_date
AND organization_id = p_org_id;
UPDATE wip_ws_fpy wwf
SET
SCRAP_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
else round(100*(wwf.QUANTITY_SCRAPPED/wwf.QUANTITY_COMPLETED),2) end),
REJECT_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
else round(100*(wwf.QUANTITY_REJECTED/wwf.QUANTITY_COMPLETED),2) end),
FPY_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
else round(100*(wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED,2) end)
WHERE
LAST_UPDATE_DATE = p_execution_date
AND wwf.organization_id = p_org_id
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, pre_cal.INVENTORY_ITEM_ID,
pre_cal.WIP_ENTITY_ID, pre_cal.OPERATION_SEQ_NUM,
null as SHIFT_NUM, pre_cal.SHIFT_DATE,
pre_cal.QUANTITY_REJECTED AS QUANTITY_REJECTED, pre_cal.QUANTITY_SCRAPPED AS QUANTITY_SCRAPPED, pre_cal.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT wwf.WIP_ENTITY_ID,wwf.OPERATION_SEQ_NUM,Trunc(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT,
sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY wwf.OPERATION_SEQ_NUM,trunc(wwf.SHIFT_DATE),wwf.WIP_ENTITY_ID,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, pre_cal.INVENTORY_ITEM_ID,
pre_cal.WIP_ENTITY_ID, pre_cal.OPERATION_SEQ_NUM,
null as SHIFT_NUM, null SHIFT_DATE,
pre_cal.QUANTITY_REJECTED AS QUANTITY_REJECTED, pre_cal.QUANTITY_SCRAPPED AS QUANTITY_SCRAPPED, pre_cal.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT wwf.WIP_ENTITY_ID,wwf.OPERATION_SEQ_NUM,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT,
sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND wwf.SHIFT_DATE >= trunc(p_execution_date)-6
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY wwf.OPERATION_SEQ_NUM,wwf.WIP_ENTITY_ID,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, pre_cal.INVENTORY_ITEM_ID,
pre_cal.WIP_ENTITY_ID, pre_cal.OPERATION_SEQ_NUM,
pre_cal.SHIFT_NUM AS SHIFT_NUM, NULL SHIFT_DATE,
pre_cal.QUANTITY_REJECTED AS QUANTITY_REJECTED, pre_cal.QUANTITY_SCRAPPED AS QUANTITY_SCRAPPED, pre_cal.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM
( SELECT wwf.WIP_ENTITY_ID,wwf.OPERATION_SEQ_NUM,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,wwf.SHIFT_NUM,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) AS SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) AS REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT,
sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND wwf.SHIFT_DATE >= trunc(p_execution_date-6)
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY wwf.OPERATION_SEQ_NUM,wwf.WIP_ENTITY_ID,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,wwf.SHIFT_NUM
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, pre_calc .SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM
( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.SHIFT_DATE,day_sum.FPY_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
FROM
( SELECT
round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID,
TRUNC(shift_date) as SHIFT_DATE, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) > 0
UNION
SELECT 0 as FPY_PERCENT,
SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --BUG 7114765
wwf.WIP_ENTITY_ID,
TRUNC(shift_date) as SHIFT_DATE, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) <= 0
) day_sum,
WIP_DISCRETE_JOBS wdj
WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
AND wdj.organization_id = p_org_id) pre_calc;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_calc.SHIFT_NUM, pre_calc.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM
( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.SHIFT_DATE, day_sum.SHIFT_NUM, day_sum.FPY_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
FROM
( SELECT
round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID,
TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.SHIFT_NUM ,wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) > 0
UNION
SELECT
0 as FPY_PERCENT,
SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID,
TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.SHIFT_NUM ,wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) <= 0
) day_sum,
WIP_DISCRETE_JOBS wdj
WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
AND wdj.organization_id = p_org_id) pre_calc;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_calc.SHIFT_NUM, pre_calc.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
100*(1 - pre_calc.FPY_PERCENT- pre_calc.SCRAP_PERCENT) AS REJECT_PERCENT,
pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM
( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.SHIFT_DATE, day_sum.SHIFT_NUM, day_sum.FPY_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT FROM
( SELECT as FPY_PERCENT, sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP, wwf.WIP_ENTITY_ID,
TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.SHIFT_NUM ,wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) = 0) day_sum,
WIP_DISCRETE_JOBS wdj
WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
AND wdj.organization_id = p_org_id) pre_calc;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM
( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.FPY_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
FROM
( SELECT
round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id
AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) > 0
UNION
SELECT 0 as FPY_PERCENT,
sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id
AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
HAVING MIN(FPY_PERCENT) <= 0
) day_sum,
WIP_DISCRETE_JOBS wdj
WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
AND wdj.organization_id = p_org_id) pre_calc;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_calc.SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM
( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.FPY_PERCENT, day_sum.SHIFT_NUM,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
(CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
FROM
( SELECT
round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id
AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NOT NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
HAVING MIN(FPY_PERCENT) > 0
UNION
SELECT 0 as FPY_PERCENT,
sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
FROM WIP_WS_FPY wwf
WHERE wwf.organization_id = p_org_id
AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NOT NULL
AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
HAVING MIN(FPY_PERCENT) <= 0
) day_sum,
WIP_DISCRETE_JOBS wdj
WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
AND wdj.organization_id = p_org_id) pre_calc;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
wwf.SHIFT_NUM, wwf.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM wip_ws_fpy wwf
WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
and wwf.SHIFT_DATE is NOT NULL and wwf.SHIFT_NUM is NOT NULL
and wwf.ORGANIZATION_ID = p_org_id
and TRUNC(wwf.SHIFT_DATE) >= TRUNC(p_cutoff_date)
GROUP BY wwf.Inventory_Item_id, wwf.SHIFT_DATE, wwf.SHIFT_NUM;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, wwf.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM wip_ws_fpy wwf
WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
and wwf.SHIFT_DATE is NOT NULL and wwf.SHIFT_NUM is NULL
and wwf.ORGANIZATION_ID = p_org_id
and TRUNC(wwf.SHIFT_DATE) >= TRUNC(p_cutoff_date)
GROUP BY wwf.Inventory_Item_id, wwf.SHIFT_DATE;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
wwf.SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM wip_ws_fpy wwf
WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
and wwf.SHIFT_DATE is NULL and wwf.SHIFT_NUM is NOT NULL
and wwf.ORGANIZATION_ID = p_org_id
GROUP BY wwf.Inventory_Item_id, wwf.SHIFT_NUM;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
) SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
(CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
FROM wip_ws_fpy wwf
WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
and wwf.SHIFT_DATE is NULL and wwf.SHIFT_NUM is NULL
and wwf.ORGANIZATION_ID = p_org_id
GROUP BY wwf.Inventory_Item_id;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_cal.SHIFT_NUM, pre_cal.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,wwf.SHIFT_NUM,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY TRUNC(wwf.SHIFT_DATE),wwf.DEPARTMENT_ID,wwf.SHIFT_NUM
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, pre_cal.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY TRUNC(wwf.SHIFT_DATE),wwf.DEPARTMENT_ID
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_cal.SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT wwf.DEPARTMENT_ID,wwf.SHIFT_NUM,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND wwf.shift_date >= TRUNC(p_execution_date)-6
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY wwf.DEPARTMENT_ID,wwf.SHIFT_NUM
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT wwf.DEPARTMENT_ID,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND wwf.shift_date >= TRUNC(p_execution_date)-6
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY wwf.DEPARTMENT_ID
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_cal.SHIFT_NUM, pre_cal.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE ,wwf.SHIFT_NUM,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY TRUNC(wwf.SHIFT_DATE),wwf.SHIFT_NUM
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, pre_cal.SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY TRUNC(wwf.SHIFT_DATE)
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
pre_cal.SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT wwf.SHIFT_NUM,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND wwf.shift_date >= TRUNC(p_execution_date)-6
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
GROUP BY wwf.SHIFT_NUM
) pre_cal;
INSERT INTO wip_ws_fpy (
ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM,
SHIFT_NUM, SHIFT_DATE,
QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
REQUEST_ID, PROGRAM_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
)
SELECT
p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
(case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
FROM
( SELECT
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
(case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
FROM WIP_WS_FPY wwf
WHERE organization_id =p_org_id
AND wwf.shift_date >= TRUNC(p_execution_date)-6
AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
) pre_cal;
SELECT max(shift_date) INTO l_last_shift_date
FROM wip_ws_fpy wwf
WHERE organization_id = p_org_id
AND wwf.operation_seq_num is NOT NULL
AND wwf.wip_entity_id is NOT NULL
AND wwf.inventory_item_id is NOT NULL
AND wwf.department_id is NOT NULL
AND wwf.shift_date is NOT NULL
AND wwf.shift_num is NOT NULL;
SELECT max(shift_date) INTO l_second_last_shift_date
FROM wip_ws_fpy wwf
WHERE organization_id = p_org_id
AND shift_date < l_last_shift_date
AND wwf.operation_seq_num is NOT NULL
AND wwf.wip_entity_id is NOT NULL
AND wwf.inventory_item_id is NOT NULL
AND wwf.department_id is NOT NULL
AND wwf.shift_date is NOT NULL
AND wwf.shift_num is NOT NULL;
SELECT MAX(CREATION_DATE) INTO l_last_calculation_date from WIP_WS_FPY;
SELECT transaction_date INTO l_start_move_tran_date_to_calc
FROM wip_move_transactions
WHERE creation_date >= l_last_calculation_date
AND organization_id = p_org_id
AND ROWNUM = 1;
SELECT wmt.transaction_date, wop.department_id into l_start_move_tran_date_to_calc, l_department_id
FROM wip_move_transactions wmt,
wip_operations wop
WHERE
wmt.creation_date >= l_last_calculation_date
AND wmt.organization_id = p_org_id
AND wop.organization_id = p_org_id
AND ROWNUM = 1
AND wop.wip_entity_id = wmt.wip_entity_id
AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR wop.operation_seq_num = wmt.fm_operation_seq_num
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
OR
(wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
OR (wop.operation_seq_num = wmt.to_operation_seq_num
AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
OR (wop.operation_seq_num = wmt.fm_operation_seq_num
AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))));
PROCEDURE delete_old_and_replacing_data(
p_calc_start_date DATE,
p_retention_boundary DATE,
p_org_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1);
l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.delete_old_and_replacing_data';
DELETE FROM wip_ws_fpy
WHERE shift_date < p_retention_boundary
AND organization_id = p_org_id;
DELETE FROM wip_ws_fpy wwf
WHERE organization_id = p_org_id
AND shift_date is NULL;
DELETE FROM wip_ws_fpy
WHERE shift_date >= p_calc_start_date
AND organization_id = p_org_id;
DELETE FROM wip_ws_fpy
WHERE TRUNC(shift_date) >= TRUNC(p_calc_start_date)
AND organization_id = p_org_id
AND (operation_seq_num is NULL
OR wip_entity_id is NULL
OR inventory_item_id is NULL
OR department_id is NULL
OR shift_num is NULL);
END delete_old_and_replacing_data;
delete wip_ws_fpy where organization_id = p_org_id;
DELETE FROM wip_ws_ppm_defects
WHERE organization_id = p_org_id;
INSERT INTO wip_ws_ppm_defects(
ORGANIZATION_ID,
WIP_ENTITY_ID,
INVENTORY_ITEM_ID,
SHIFT_NUM,
SHIFT_DATE,
QUANTITY_DEFECTED,
QUANTITY_PRODUCED,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE
)
select
wdj.organization_id, -- ORGANIZATION_ID
wdj.wip_entity_id, -- WIP_ENTITY_ID
wdj.primary_item_id, -- INVENTORY_ITEM_ID
WIP_WS_EMBEDDED_ANALYTICS_PK.get_shift_num(shift_info) shift_num, -- SHIFT_NUM
WIP_WS_EMBEDDED_ANALYTICS_PK.get_shift_start_date(shift_info) shift_date, -- SHIFT_DATE
wdj.quantity_scrapped, -- qty_defected
wdj.quantity_completed + wdj.quantity_scrapped, -- qty_produced
sysdate, --LAST_UPDATE_DATE,
g_user_id, --LAST_UPDATED_BY,
g_login_id, --LAST_UPDATE_LOGIN,
sysdate, --CREATION_DATE,
g_user_id, --CREATED_BY,
g_request_id, --REQUEST_ID,
g_prog_id, --PROGRAM_ID,
g_prog_appid,--PROGRAM_APPLICATION_ID,
sysdate --PROGRAM_UPDATE_DATE
from
(select
WIP_WS_EMBEDDED_ANALYTICS_PK.get_shift_info_for_date(
wdj1.organization_id, null, null,
nvl(wdj1.date_completed, wdj1.date_closed)) shift_info,
wdj1.*
from wip_discrete_jobs wdj1
where wdj1.date_completed > p_start_date
and wdj1.organization_id = p_org_id
and wdj1.status_type in (WIP_CONSTANTS.CLOSED, WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.COMP_NOCHRG)
and wdj1.quantity_completed > 0) wdj;