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.CSD_RECALLS_PVT dependencies on CSD_RECALL_METRICS

Line 1231: FROM csd_recall_metrics

1227: l_metric_ids.EXTEND;
1228: BEGIN
1229: SELECT metric_id
1230: INTO l_metric_ids(1)
1231: FROM csd_recall_metrics
1232: WHERE recall_number = p_recall_number;
1233: EXCEPTION
1234: WHEN NO_DATA_FOUND THEN
1235: l_metric_ids(1) := -1;

Line 1239: INSERT INTO csd_recall_metrics(metric_id,recall_number) values(csd_recall_metrics_s1.NEXTVAL,p_recall_number)

1235: l_metric_ids(1) := -1;
1236: END;
1237: IF l_metric_ids(1) = -1
1238: THEN
1239: INSERT INTO csd_recall_metrics(metric_id,recall_number) values(csd_recall_metrics_s1.NEXTVAL,p_recall_number)
1240: RETURNING metric_id INTO l_metric_ids(1);
1241: END IF;
1242: ELSE
1243: INSERT INTO csd_recall_metrics(metric_id,recall_number)

Line 1243: INSERT INTO csd_recall_metrics(metric_id,recall_number)

1239: INSERT INTO csd_recall_metrics(metric_id,recall_number) values(csd_recall_metrics_s1.NEXTVAL,p_recall_number)
1240: RETURNING metric_id INTO l_metric_ids(1);
1241: END IF;
1242: ELSE
1243: INSERT INTO csd_recall_metrics(metric_id,recall_number)
1244: (SELECT csd_recall_metrics_s1.NEXTVAL,recall_number
1245: FROM csd_recall_headers_b,csd_recall_statuses_b crs
1246: WHERE recall_flow_status_id = crs.status_id
1247: AND crs.status_code <> 'C'

Line 1244: (SELECT csd_recall_metrics_s1.NEXTVAL,recall_number

1240: RETURNING metric_id INTO l_metric_ids(1);
1241: END IF;
1242: ELSE
1243: INSERT INTO csd_recall_metrics(metric_id,recall_number)
1244: (SELECT csd_recall_metrics_s1.NEXTVAL,recall_number
1245: FROM csd_recall_headers_b,csd_recall_statuses_b crs
1246: WHERE recall_flow_status_id = crs.status_id
1247: AND crs.status_code <> 'C'
1248: AND NOT EXISTS ( SELECT 'exists'

Line 1249: FROM csd_recall_metrics crm

1245: FROM csd_recall_headers_b,csd_recall_statuses_b crs
1246: WHERE recall_flow_status_id = crs.status_id
1247: AND crs.status_code <> 'C'
1248: AND NOT EXISTS ( SELECT 'exists'
1249: FROM csd_recall_metrics crm
1250: WHERE crm.recall_number = recall_number )
1251: AND recall_id in (SELECT MAX(recall_id) FROM csd_recall_headers_b
1252: GROUP BY recall_number ));
1253:

Line 1256: FROM csd_recall_metrics crm,

1252: GROUP BY recall_number ));
1253:
1254: SELECT DISTINCT metric_id
1255: BULK COLLECT INTO l_metric_ids
1256: FROM csd_recall_metrics crm,
1257: csd_recall_headers_b crh,
1258: csd_recall_statuses_b crs
1259: WHERE crh.recall_number = crm.recall_number
1260: AND crh.recall_flow_status_id = crs.status_id

Line 1268: FROM csd_recall_metrics crm,

1264:
1265: -- number of units recalled.
1266: SELECT crh.recall_number,SUM(cii.quantity)
1267: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1268: FROM csd_recall_metrics crm,
1269: csd_recall_headers_b crh,
1270: csd_recall_lines crl,
1271: csi_item_instances cii
1272: WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))

Line 1281: UPDATE csd_recall_metrics SET recalled_units = l_gen_num_tbl(i)

1277:
1278: --dbms_output.put_line('first bulk collect');
1279:
1280: FORALL i IN 1 ..l_recall_numbers.COUNT
1281: UPDATE csd_recall_metrics SET recalled_units = l_gen_num_tbl(i)
1282: WHERE recall_number = l_recall_numbers(i);
1283:
1284: --dbms_output.put_line('after for all update');
1285:

Line 1288: FROM csd_recall_metrics crm,

1284: --dbms_output.put_line('after for all update');
1285:
1286: SELECT crh.recall_number, COUNT(DISTINCT crl.owner_party_id)
1287: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1288: FROM csd_recall_metrics crm,
1289: csd_recall_headers_b crh,
1290: csd_recall_lines crl
1291: WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1292: AND crm.recall_number = crh.recall_number

Line 1299: UPDATE csd_recall_metrics SET customers_impacted = l_gen_num_tbl(i)

1295:
1296: --dbms_output.put_line('2nd bulk collect');
1297: -- number of customers impacted.
1298: FORALL i IN 1 ..l_recall_numbers.COUNT
1299: UPDATE csd_recall_metrics SET customers_impacted = l_gen_num_tbl(i)
1300: WHERE recall_number = l_recall_numbers(i);
1301:
1302: --dbms_output.put_line('after for all update');
1303:

Line 1307: FROM csd_recall_metrics crm,

1303:
1304: SELECT crh.recall_number,
1305: SUM(wpb.tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in)
1306: BULK COLLECT INTO l_recall_numbers, l_accumulated_cost
1307: FROM csd_recall_metrics crm,
1308: csd_recall_headers_b crh,
1309: csd_recall_lines crl,
1310: csd_repair_job_xref crj,
1311: wip_period_balances wpb

Line 1324: UPDATE csd_recall_metrics SET accumulated_costs = l_accumulated_cost(i)

1320:
1321: --dbms_output.put_line('3rd bulk collect');
1322:
1323: FORALL i IN 1 ..l_recall_numbers.COUNT
1324: UPDATE csd_recall_metrics SET accumulated_costs = l_accumulated_cost(i)
1325: WHERE recall_number = l_recall_numbers(i);
1326:
1327: -- WIP jobs which have not been costed yet.Take the sum of remaining quantity.
1328: SELECT recall_number,

Line 1334: FROM csd_recall_metrics crm,

1330: BULK COLLECT INTO l_recall_numbers, l_wip_jobs_without_costs
1331: FROM
1332: (SELECT crh.recall_number,
1333: SUM(crj.quantity) quantity
1334: FROM csd_recall_metrics crm,
1335: csd_recall_headers_b crh,
1336: csd_recall_lines crl,
1337: csd_repair_job_xref crj
1338: WHERE crm.metric_id IN

Line 1359: FROM csd_recall_metrics crm,

1355: UNION ALL
1356:
1357: SELECT crh.recall_number,
1358: SUM(cii.quantity) quantity
1359: FROM csd_recall_metrics crm,
1360: csd_recall_headers_b crh,
1361: csd_recall_lines crl,
1362: csi_item_instances cii,
1363: cs_incidents_all_b sr,

Line 1409: UPDATE csd_recall_metrics SET remaining_cost = (l_wip_jobs_without_costs(i) * (accumulated_costs/l_wip_jobs_with_costs(i)))

1405: ORDER BY crh.recall_number;
1406:
1407: -- update the actual cost, and estimated cost remaining.
1408: FORALL i IN 1 ..l_recall_numbers.COUNT
1409: UPDATE csd_recall_metrics SET remaining_cost = (l_wip_jobs_without_costs(i) * (accumulated_costs/l_wip_jobs_with_costs(i)))
1410: WHERE recall_number = l_recall_numbers(i);
1411:
1412: -- remediated units.
1413:

Line 1417: FROM csd_recall_metrics crm,

1413:
1414: SELECT crh.recall_number,
1415: SUM(cii.quantity)
1416: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1417: FROM csd_recall_metrics crm,
1418: csd_recall_headers_b crh,
1419: csd_recall_lines crl,
1420: cs_incidents_all_b sr,
1421: cs_incident_statuses_b cis,

Line 1436: UPDATE csd_recall_metrics SET remediated_units = 0

1432:
1433: IF l_recall_numbers.COUNT = 0 OR l_recall_numbers.COUNT < l_metric_ids.COUNT
1434: THEN
1435: FORALL i IN 1 ..l_metric_ids.COUNT
1436: UPDATE csd_recall_metrics SET remediated_units = 0
1437: WHERE metric_id = l_metric_ids(i)
1438: AND recall_number NOT IN
1439: (SELECT * FROM TABLE(CAST(l_recall_numbers AS JTF_VARCHAR2_TABLE_100))) ;
1440: END IF;

Line 1445: UPDATE csd_recall_metrics SET remediated_units = l_gen_num_tbl(i)

1441:
1442: IF l_recall_numbers.COUNT > 0
1443: THEN
1444: FORALL i IN 1 ..l_recall_numbers.COUNT
1445: UPDATE csd_recall_metrics SET remediated_units = l_gen_num_tbl(i)
1446: WHERE recall_number = l_recall_numbers(i);
1447: END IF;
1448: -- un - remediated units.
1449:

Line 1451: UPDATE csd_recall_metrics SET un_remediated_units = (recalled_units-nvl(remediated_units,0))

1447: END IF;
1448: -- un - remediated units.
1449:
1450: FORALL i IN 1 ..l_metric_ids.COUNT
1451: UPDATE csd_recall_metrics SET un_remediated_units = (recalled_units-nvl(remediated_units,0))
1452: WHERE metric_id = l_metric_ids(i);
1453:
1454: -- remediated custoemers.
1455:

Line 1461: FROM csd_recall_metrics crm,

1457: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1458: FROM (
1459: SELECT crh.recall_number,
1460: COUNT(crl.owner_party_id) party_id
1461: FROM csd_recall_metrics crm,
1462: csd_recall_headers_b crh,
1463: csd_recall_lines crl,
1464: cs_incidents_all_b sr,
1465: cs_incident_statuses_b cis

Line 1479: csd_recall_metrics crm1

1475: FROM csd_recall_headers_b crh1,
1476: csd_recall_lines crl1,
1477: cs_incidents_all_b sr1,
1478: cs_incident_statuses_b cis1,
1479: csd_recall_metrics crm1
1480: WHERE crm1.metric_id IN
1481: (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1482: AND crm1.recall_number = crh1.recall_number
1483: AND crh1.recall_id = crl1.recall_id

Line 1495: UPDATE csd_recall_metrics SET customers_remediated = 0

1491:
1492: IF l_recall_numbers.COUNT = 0 OR l_recall_numbers.COUNT < l_metric_ids.COUNT
1493: THEN
1494: FORALL i IN 1 ..l_metric_ids.COUNT
1495: UPDATE csd_recall_metrics SET customers_remediated = 0
1496: WHERE metric_id = l_metric_ids(i)
1497: AND recall_number NOT IN
1498: (SELECT * FROM TABLE(CAST(l_recall_numbers AS JTF_VARCHAR2_TABLE_100))) ;
1499: END IF;

Line 1504: UPDATE csd_recall_metrics SET customers_remediated = NVL(l_gen_num_tbl(i),0)

1500:
1501: IF l_recall_numbers.COUNT <> 0
1502: THEN
1503: FORALL i IN 1 ..l_recall_numbers.COUNT
1504: UPDATE csd_recall_metrics SET customers_remediated = NVL(l_gen_num_tbl(i),0)
1505: WHERE recall_number = l_recall_numbers(i);
1506: END IF;
1507:
1508: -- customers un remediated. also update the WHO columns.

Line 1510: UPDATE csd_recall_metrics SET customers_un_remediated = (customers_impacted-nvl(customers_remediated,0)),

1506: END IF;
1507:
1508: -- customers un remediated. also update the WHO columns.
1509: FORALL i IN 1 ..l_metric_ids.COUNT
1510: UPDATE csd_recall_metrics SET customers_un_remediated = (customers_impacted-nvl(customers_remediated,0)),
1511: last_update_date = sysdate,
1512: last_updated_by = fnd_global.user_id,
1513: last_update_login = fnd_global.user_id,
1514: object_version_number = object_version_number+1