eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WIP_WS_EMBEDDED_ANALYTICS_PK dependencies on WIP_WS_FPY

Line 107: INSERT INTO wip_ws_fpy

103:
104: x_return_status := FND_API.G_RET_STS_SUCCESS;
105:
106: /* Original Query before passing Department when calculating shift.
107: INSERT INTO wip_ws_fpy
108: (
109: ORGANIZATION_ID,
110: DEPARTMENT_ID,
111: WIP_ENTITY_ID,

Line 227: INSERT INTO wip_ws_fpy

223: AND wdj.organization_id = p_org_id;
224:
225: */
226:
227: INSERT INTO wip_ws_fpy
228: (
229: ORGANIZATION_ID,
230: DEPARTMENT_ID,
231: WIP_ENTITY_ID,

Line 357: wip_ws_fpy fpy

353:
354: wip_ws_util.trace_log('Finish Inserting QUANTITY_COMPLETED');
355: --------------------------- Update QUANTITY_SCRAPPED ------------------------------------
356: UPDATE
357: wip_ws_fpy fpy
358: SET
359: QUANTITY_SCRAPPED =
360: nvl((SELECT
361: 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)

Line 450: wip_ws_fpy fpy

446:
447: ------------------------------------ update QUANTITY_REJECTED ------------------------------------
448:
449: UPDATE
450: wip_ws_fpy fpy
451: SET
452: QUANTITY_REJECTED =
453: -- Formula = Sum(IN) - Sum(OUT BW)
454: -- OUT BW = Move backword from REJECT except moving to the 'TOMOVE' within the same opeation

Line 602: wip_ws_fpy fpy

598: wip_ws_util.trace_log('Finish Updating QUANTITY_REJECTED');
599: ------------------------------------ update QUANTITY_FIRST_PASS ------------------------------------
600: /*
601: UPDATE
602: wip_ws_fpy fpy
603: SET
604: QUANTITY_FIRST_PASS = QUANTITY_COMPLETED - QUANTITY_REJECTED - QUANTITY_SCRAPPED
605: WHERE
606: LAST_UPDATE_DATE = p_execution_date

Line 652: UPDATE wip_ws_fpy wwf

648: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
649: p_proc_name => l_proc_name,
650: p_return_status => l_return_status);
651:
652: UPDATE wip_ws_fpy wwf
653: SET
654: SCRAP_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
655: else round(100*(wwf.QUANTITY_SCRAPPED/wwf.QUANTITY_COMPLETED),2) end),
656: REJECT_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0

Line 688: INSERT INTO wip_ws_fpy (

684: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
685: p_proc_name => l_proc_name,
686: p_return_status => l_return_status);
687:
688: INSERT INTO wip_ws_fpy (
689: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
690: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
691: SHIFT_NUM, SHIFT_DATE,
692: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 717: FROM WIP_WS_FPY wwf

713: (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,
714: (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,
715: (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,
716: sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
717: FROM WIP_WS_FPY wwf
718: WHERE organization_id =p_org_id
719: AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
720: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
721: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 750: INSERT INTO wip_ws_fpy (

746: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
747: p_proc_name => l_proc_name,
748: p_return_status => l_return_status);
749:
750: INSERT INTO wip_ws_fpy (
751: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
752: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
753: SHIFT_NUM, SHIFT_DATE,
754: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 779: FROM WIP_WS_FPY wwf

775: (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,
776: (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,
777: (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,
778: sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
779: FROM WIP_WS_FPY wwf
780: WHERE organization_id =p_org_id
781: AND wwf.SHIFT_DATE >= trunc(p_execution_date)-6
782: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
783: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 812: INSERT INTO wip_ws_fpy (

808: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
809: p_proc_name => l_proc_name,
810: p_return_status => l_return_status);
811:
812: INSERT INTO wip_ws_fpy (
813: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
814: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
815: SHIFT_NUM, SHIFT_DATE,
816: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 841: FROM WIP_WS_FPY wwf

837: (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,
838: (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,
839: (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,
840: sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
841: FROM WIP_WS_FPY wwf
842: WHERE organization_id =p_org_id
843: AND wwf.SHIFT_DATE >= trunc(p_execution_date-6)
844: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
845: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 899: INSERT INTO wip_ws_fpy (

895: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
896: p_proc_name => l_proc_name,
897: p_return_status => l_return_status);
898:
899: INSERT INTO wip_ws_fpy (
900: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
901: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
902: SHIFT_NUM, SHIFT_DATE,
903: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 932: FROM WIP_WS_FPY wwf

928: SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
929: SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
930: wwf.WIP_ENTITY_ID,
931: TRUNC(shift_date) as SHIFT_DATE, wwf.INVENTORY_ITEM_ID
932: FROM WIP_WS_FPY wwf
933: WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
934: AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NULL
935: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
936: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 947: FROM WIP_WS_FPY wwf

943: SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
944: SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --BUG 7114765
945: wwf.WIP_ENTITY_ID,
946: TRUNC(shift_date) as SHIFT_DATE, wwf.INVENTORY_ITEM_ID
947: FROM WIP_WS_FPY wwf
948: WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
949: AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NULL
950: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
951: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 981: INSERT INTO wip_ws_fpy (

977: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
978: p_proc_name => l_proc_name,
979: p_return_status => l_return_status);
980:
981: INSERT INTO wip_ws_fpy (
982: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
983: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
984: SHIFT_NUM, SHIFT_DATE,
985: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1014: FROM WIP_WS_FPY wwf

1010: SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1011: SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1012: wwf.WIP_ENTITY_ID,
1013: TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
1014: FROM WIP_WS_FPY wwf
1015: WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
1016: AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
1017: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1018: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1030: FROM WIP_WS_FPY wwf

1026: SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1027: SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1028: wwf.WIP_ENTITY_ID,
1029: TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
1030: FROM WIP_WS_FPY wwf
1031: WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
1032: AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
1033: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1034: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1042: INSERT INTO wip_ws_fpy (

1038: WIP_DISCRETE_JOBS wdj
1039: WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
1040: AND wdj.organization_id = p_org_id) pre_calc;
1041: /*
1042: INSERT INTO wip_ws_fpy (
1043: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1044: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1045: SHIFT_NUM, SHIFT_DATE,
1046: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1069: FROM WIP_WS_FPY wwf

1065: ( 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,
1066: (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT FROM
1067: ( SELECT as FPY_PERCENT, sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP, wwf.WIP_ENTITY_ID,
1068: TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
1069: FROM WIP_WS_FPY wwf
1070: WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
1071: AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
1072: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1073: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1103: INSERT INTO wip_ws_fpy (

1099: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1100: p_proc_name => l_proc_name,
1101: p_return_status => l_return_status);
1102:
1103: INSERT INTO wip_ws_fpy (
1104: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1105: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1106: SHIFT_NUM, SHIFT_DATE,
1107: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1135: FROM WIP_WS_FPY wwf

1131: round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
1132: sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1133: sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1134: wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
1135: FROM WIP_WS_FPY wwf
1136: WHERE wwf.organization_id = p_org_id
1137: AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NULL
1138: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1139: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1149: FROM WIP_WS_FPY wwf

1145: SELECT 0 as FPY_PERCENT,
1146: sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1147: sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1148: wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
1149: FROM WIP_WS_FPY wwf
1150: WHERE wwf.organization_id = p_org_id
1151: AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NULL
1152: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1153: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1184: INSERT INTO wip_ws_fpy (

1180: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1181: p_proc_name => l_proc_name,
1182: p_return_status => l_return_status);
1183:
1184: INSERT INTO wip_ws_fpy (
1185: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1186: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1187: SHIFT_NUM, SHIFT_DATE,
1188: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1216: FROM WIP_WS_FPY wwf

1212: round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
1213: sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1214: sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1215: wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
1216: FROM WIP_WS_FPY wwf
1217: WHERE wwf.organization_id = p_org_id
1218: AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NOT NULL
1219: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1220: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1230: FROM WIP_WS_FPY wwf

1226: SELECT 0 as FPY_PERCENT,
1227: sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1228: sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1229: wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
1230: FROM WIP_WS_FPY wwf
1231: WHERE wwf.organization_id = p_org_id
1232: AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NOT NULL
1233: AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1234: AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL

Line 1265: INSERT INTO wip_ws_fpy (

1261: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1262: p_proc_name => l_proc_name,
1263: p_return_status => l_return_status);
1264:
1265: INSERT INTO wip_ws_fpy (
1266: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1267: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1268: SHIFT_NUM, SHIFT_DATE,
1269: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1287: FROM wip_ws_fpy wwf

1283: g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1284: p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1285: p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1286: g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1287: FROM wip_ws_fpy wwf
1288: WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1289: and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1290: and wwf.SHIFT_DATE is NOT NULL and wwf.SHIFT_NUM is NOT NULL
1291: and wwf.ORGANIZATION_ID = p_org_id

Line 1319: INSERT INTO wip_ws_fpy (

1315: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1316: p_proc_name => l_proc_name,
1317: p_return_status => l_return_status);
1318:
1319: INSERT INTO wip_ws_fpy (
1320: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1321: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1322: SHIFT_NUM, SHIFT_DATE,
1323: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1341: FROM wip_ws_fpy wwf

1337: g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1338: p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1339: p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1340: g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1341: FROM wip_ws_fpy wwf
1342: WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1343: and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1344: and wwf.SHIFT_DATE is NOT NULL and wwf.SHIFT_NUM is NULL
1345: and wwf.ORGANIZATION_ID = p_org_id

Line 1372: INSERT INTO wip_ws_fpy (

1368: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1369: p_proc_name => l_proc_name,
1370: p_return_status => l_return_status);
1371:
1372: INSERT INTO wip_ws_fpy (
1373: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1374: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1375: SHIFT_NUM, SHIFT_DATE,
1376: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1394: FROM wip_ws_fpy wwf

1390: g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1391: p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1392: p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1393: g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1394: FROM wip_ws_fpy wwf
1395: WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1396: and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1397: and wwf.SHIFT_DATE is NULL and wwf.SHIFT_NUM is NOT NULL
1398: and wwf.ORGANIZATION_ID = p_org_id

Line 1424: INSERT INTO wip_ws_fpy (

1420: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1421: p_proc_name => l_proc_name,
1422: p_return_status => l_return_status);
1423:
1424: INSERT INTO wip_ws_fpy (
1425: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1426: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1427: SHIFT_NUM, SHIFT_DATE,
1428: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1446: FROM wip_ws_fpy wwf

1442: g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1443: p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1444: p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1445: g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1446: FROM wip_ws_fpy wwf
1447: WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1448: and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1449: and wwf.SHIFT_DATE is NULL and wwf.SHIFT_NUM is NULL
1450: and wwf.ORGANIZATION_ID = p_org_id

Line 1477: INSERT INTO wip_ws_fpy (

1473: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1474: p_proc_name => l_proc_name,
1475: p_return_status => l_return_status);
1476:
1477: INSERT INTO wip_ws_fpy (
1478: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1479: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1480: SHIFT_NUM, SHIFT_DATE,
1481: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1503: FROM WIP_WS_FPY wwf

1499: ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,wwf.SHIFT_NUM,
1500: (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,
1501: (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,
1502: (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
1503: FROM WIP_WS_FPY wwf
1504: WHERE organization_id =p_org_id
1505: AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1506: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1507: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1535: INSERT INTO wip_ws_fpy (

1531: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1532: p_proc_name => l_proc_name,
1533: p_return_status => l_return_status);
1534:
1535: INSERT INTO wip_ws_fpy (
1536: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1537: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1538: SHIFT_NUM, SHIFT_DATE,
1539: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1561: FROM WIP_WS_FPY wwf

1557: ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,
1558: (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,
1559: (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,
1560: (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
1561: FROM WIP_WS_FPY wwf
1562: WHERE organization_id =p_org_id
1563: AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1564: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1565: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1594: INSERT INTO wip_ws_fpy (

1590: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1591: p_proc_name => l_proc_name,
1592: p_return_status => l_return_status);
1593:
1594: INSERT INTO wip_ws_fpy (
1595: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1596: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1597: SHIFT_NUM, SHIFT_DATE,
1598: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1620: FROM WIP_WS_FPY wwf

1616: ( SELECT wwf.DEPARTMENT_ID,wwf.SHIFT_NUM,
1617: (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,
1618: (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,
1619: (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
1620: FROM WIP_WS_FPY wwf
1621: WHERE organization_id =p_org_id
1622: AND wwf.shift_date >= TRUNC(p_execution_date)-6
1623: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1624: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1651: INSERT INTO wip_ws_fpy (

1647: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1648: p_proc_name => l_proc_name,
1649: p_return_status => l_return_status);
1650:
1651: INSERT INTO wip_ws_fpy (
1652: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1653: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1654: SHIFT_NUM, SHIFT_DATE,
1655: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1677: FROM WIP_WS_FPY wwf

1673: ( SELECT wwf.DEPARTMENT_ID,
1674: (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,
1675: (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,
1676: (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
1677: FROM WIP_WS_FPY wwf
1678: WHERE organization_id =p_org_id
1679: AND wwf.shift_date >= TRUNC(p_execution_date)-6
1680: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1681: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1708: INSERT INTO wip_ws_fpy (

1704: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1705: p_proc_name => l_proc_name,
1706: p_return_status => l_return_status);
1707:
1708: INSERT INTO wip_ws_fpy (
1709: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1710: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1711: SHIFT_NUM, SHIFT_DATE,
1712: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1736: FROM WIP_WS_FPY wwf

1732: ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE ,wwf.SHIFT_NUM,
1733: (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,
1734: (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,
1735: (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
1736: FROM WIP_WS_FPY wwf
1737: WHERE organization_id =p_org_id
1738: AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1739: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1740: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1767: INSERT INTO wip_ws_fpy (

1763: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1764: p_proc_name => l_proc_name,
1765: p_return_status => l_return_status);
1766:
1767: INSERT INTO wip_ws_fpy (
1768: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1769: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1770: SHIFT_NUM, SHIFT_DATE,
1771: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1795: FROM WIP_WS_FPY wwf

1791: ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,
1792: (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,
1793: (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,
1794: (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
1795: FROM WIP_WS_FPY wwf
1796: WHERE organization_id =p_org_id
1797: AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1798: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1799: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1828: INSERT INTO wip_ws_fpy (

1824: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1825: p_proc_name => l_proc_name,
1826: p_return_status => l_return_status);
1827:
1828: INSERT INTO wip_ws_fpy (
1829: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1830: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1831: SHIFT_NUM, SHIFT_DATE,
1832: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1856: FROM WIP_WS_FPY wwf

1852: ( SELECT wwf.SHIFT_NUM,
1853: (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,
1854: (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,
1855: (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
1856: FROM WIP_WS_FPY wwf
1857: WHERE organization_id =p_org_id
1858: AND wwf.shift_date >= TRUNC(p_execution_date)-6
1859: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1860: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 1887: INSERT INTO wip_ws_fpy (

1883: begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1884: p_proc_name => l_proc_name,
1885: p_return_status => l_return_status);
1886:
1887: INSERT INTO wip_ws_fpy (
1888: ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1889: WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1890: SHIFT_NUM, SHIFT_DATE,
1891: QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,

Line 1915: FROM WIP_WS_FPY wwf

1911: ( SELECT
1912: (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,
1913: (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,
1914: (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
1915: FROM WIP_WS_FPY wwf
1916: WHERE organization_id =p_org_id
1917: AND wwf.shift_date >= TRUNC(p_execution_date)-6
1918: AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1919: AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL

Line 2212: FROM wip_ws_fpy wwf

2208:
2209:
2210: BEGIN
2211: SELECT max(shift_date) INTO l_last_shift_date
2212: FROM wip_ws_fpy wwf
2213: WHERE organization_id = p_org_id
2214: AND wwf.operation_seq_num is NOT NULL
2215: AND wwf.wip_entity_id is NOT NULL
2216: AND wwf.inventory_item_id is NOT NULL

Line 2225: FROM wip_ws_fpy wwf

2221: IF l_last_shift_date is NULL THEN
2222: l_second_last_shift_date := l_last_shift_date;
2223: ELSE
2224: SELECT max(shift_date) INTO l_second_last_shift_date
2225: FROM wip_ws_fpy wwf
2226: WHERE organization_id = p_org_id
2227: AND shift_date < l_last_shift_date
2228: AND wwf.operation_seq_num is NOT NULL
2229: AND wwf.wip_entity_id is NOT NULL

Line 2251: SELECT MAX(CREATION_DATE) INTO l_last_calculation_date from WIP_WS_FPY;

2247: WHEN others THEN
2248: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2249: END;
2250:
2251: SELECT MAX(CREATION_DATE) INTO l_last_calculation_date from WIP_WS_FPY;
2252: IF l_last_calculation_date is NULL THEN
2253: l_last_calculation_date := l_retention_boundary;
2254: ELSE
2255: IF l_last_calculation_date < l_retention_boundary THEN

Line 2360: DELETE FROM wip_ws_fpy

2356: begin_log (p_params => set_calc_param(p_execution_date => p_calc_start_date,p_cutoff_date => p_retention_boundary,p_org_id => p_org_id),
2357: p_proc_name => l_proc_name,
2358: p_return_status => l_return_status);
2359:
2360: DELETE FROM wip_ws_fpy
2361: WHERE shift_date < p_retention_boundary
2362: AND organization_id = p_org_id;
2363:
2364: DELETE FROM wip_ws_fpy wwf

Line 2364: DELETE FROM wip_ws_fpy wwf

2360: DELETE FROM wip_ws_fpy
2361: WHERE shift_date < p_retention_boundary
2362: AND organization_id = p_org_id;
2363:
2364: DELETE FROM wip_ws_fpy wwf
2365: WHERE organization_id = p_org_id
2366: AND shift_date is NULL;
2367:
2368: DELETE FROM wip_ws_fpy

Line 2368: DELETE FROM wip_ws_fpy

2364: DELETE FROM wip_ws_fpy wwf
2365: WHERE organization_id = p_org_id
2366: AND shift_date is NULL;
2367:
2368: DELETE FROM wip_ws_fpy
2369: WHERE shift_date >= p_calc_start_date
2370: AND organization_id = p_org_id;
2371:
2372: DELETE FROM wip_ws_fpy

Line 2372: DELETE FROM wip_ws_fpy

2368: DELETE FROM wip_ws_fpy
2369: WHERE shift_date >= p_calc_start_date
2370: AND organization_id = p_org_id;
2371:
2372: DELETE FROM wip_ws_fpy
2373: WHERE TRUNC(shift_date) >= TRUNC(p_calc_start_date)
2374: AND organization_id = p_org_id
2375: AND (operation_seq_num is NULL
2376: OR wip_entity_id is NULL

Line 2389: PROCEDURE wip_ws_fpykpi_conc_prog(

2385: exception_log(l_proc_name,p_return_status => x_return_status);
2386: END delete_old_and_replacing_data;
2387:
2388:
2389: PROCEDURE wip_ws_fpykpi_conc_prog(
2390: errbuf OUT NOCOPY VARCHAR2,
2391: retcode OUT NOCOPY VARCHAR2,
2392: p_org_id IN NUMBER) IS
2393: /*

Line 2408: l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.wip_ws_fpykpi_conc_prog';

2404: l_calc_start_date DATE;
2405:
2406: l_return_status VARCHAR2(1);
2407: l_params wip_logger.param_tbl_t;
2408: l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.wip_ws_fpykpi_conc_prog';
2409:
2410: l_concurrent_count NUMBER;
2411: l_conc_status boolean;
2412:

Line 2428: SAVEPOINT wip_ws_fpykpi_calc;

2424:
2425: wip_ws_util.trace_log('Org id = '||to_char(p_org_id));
2426: wip_ws_util.trace_log('Start time = '||to_char(l_concurrent_execution_date));
2427:
2428: SAVEPOINT wip_ws_fpykpi_calc;
2429:
2430:
2431: l_concurrent_count := wip_ws_util.get_no_of_running_concurrent(
2432: p_program_application_id => fnd_global.prog_appl_id,

Line 2445: delete wip_ws_fpy where organization_id = p_org_id;

2441: return;
2442: end if;
2443:
2444:
2445: delete wip_ws_fpy where organization_id = p_org_id;
2446: l_calc_start_date := trunc(l_concurrent_execution_date-g_fpy_data_retention);
2447:
2448: calc_fpy_for_jobop_all(
2449: p_execution_date => l_concurrent_execution_date,

Line 2490: wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_fpykpi_conc_prog',

2486: wip_ws_util.trace_log('populate_fpy_raw_data is successfull');
2487: END IF;
2488:
2489: IF (g_logLevel <= wip_constants.trace_logging) then
2490: wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_fpykpi_conc_prog',
2491: p_procReturnStatus => l_return_status,
2492: p_msg => 'processed successfully',
2493: x_returnStatus => l_return_status);
2494: END IF;

Line 2501: ROLLBACK TO wip_ws_fpykpi_calc;

2497: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2498: l_return_status := 2;
2499: exception_log(l_proc_name,p_return_status => l_return_status);
2500:
2501: ROLLBACK TO wip_ws_fpykpi_calc;
2502: retcode := 2; -- End with error
2503: WHEN FND_API.G_EXC_ERROR THEN
2504: retcode := 1;
2505: wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');

Line 2505: wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');

2501: ROLLBACK TO wip_ws_fpykpi_calc;
2502: retcode := 2; -- End with error
2503: WHEN FND_API.G_EXC_ERROR THEN
2504: retcode := 1;
2505: wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');
2506: ROLLBACK TO wip_ws_fpykpi_calc;
2507: WHEN others THEN
2508: wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');
2509: ROLLBACK TO wip_ws_fpykpi_calc;

Line 2506: ROLLBACK TO wip_ws_fpykpi_calc;

2502: retcode := 2; -- End with error
2503: WHEN FND_API.G_EXC_ERROR THEN
2504: retcode := 1;
2505: wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');
2506: ROLLBACK TO wip_ws_fpykpi_calc;
2507: WHEN others THEN
2508: wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');
2509: ROLLBACK TO wip_ws_fpykpi_calc;
2510: retcode := 2; -- End with error

Line 2508: wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');

2504: retcode := 1;
2505: wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');
2506: ROLLBACK TO wip_ws_fpykpi_calc;
2507: WHEN others THEN
2508: wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');
2509: ROLLBACK TO wip_ws_fpykpi_calc;
2510: retcode := 2; -- End with error
2511: END wip_ws_fpykpi_conc_prog;
2512:

Line 2509: ROLLBACK TO wip_ws_fpykpi_calc;

2505: wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');
2506: ROLLBACK TO wip_ws_fpykpi_calc;
2507: WHEN others THEN
2508: wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');
2509: ROLLBACK TO wip_ws_fpykpi_calc;
2510: retcode := 2; -- End with error
2511: END wip_ws_fpykpi_conc_prog;
2512:
2513: FUNCTION get_shift_info(p_org_id IN NUMBER,

Line 2511: END wip_ws_fpykpi_conc_prog;

2507: WHEN others THEN
2508: wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');
2509: ROLLBACK TO wip_ws_fpykpi_calc;
2510: retcode := 2; -- End with error
2511: END wip_ws_fpykpi_conc_prog;
2512:
2513: FUNCTION get_shift_info(p_org_id IN NUMBER,
2514: p_department_id IN NUMBER,
2515: p_transaction_date IN DATE)