[Home] [Help]
451: CURSOR Schedule_Matrl_cur (C_SCH_MAT_ID IN NUMBER) IS
452: --Added by sowsubra - status needs be fetched
453: SELECT scheduled_material_id,uom,status,
454: organization_id,visit_task_id, requested_quantity --SKPATHAK :: Bug 12833742 :: 11-AUG-2011
455: FROM ahl_schedule_materials
456: WHERE scheduled_material_id = C_SCH_MAT_ID;
457:
458: CURSOR Item_Des_cur(c_item_id IN NUMBER, c_org_id IN NUMBER) IS
459: SELECT CONCATENATED_SEGMENTS
965: /*
966: -- Changed for fixing perf bug:4919540
967: select DECODE( SIGN( trunc(scheduled_date) - trunc(requested_date)),1,scheduled_date,null) scheduled_date,
968: scheduled_quantity
969: from ahl_schedule_materials asmt,
970: AHL_VISIT_TASKS_B tsk
971: where TSK.VISIT_ID = ASMT.VISIT_ID
972: AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
973: AND NVL(ASMT.STATUS,' ') <> 'DELETED'
976: */
977: SELECT scheduled_date ,
978: status, --Added by sowsubra
979: scheduled_quantity
980: FROM ahl_schedule_materials asmt,
981: AHL_VISIT_TASKS_B tsk
982: WHERE TSK.VISIT_ID = ASMT.VISIT_ID
983: AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
984: AND NVL(ASMT.STATUS,' ') <> 'DELETED'
1221: END IF;
1222: MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1223:
1224: --SKPATHAK :: Bug 8392521 :: 02-APR-2009
1225: --Update ahl_schedule_materials table with the ship_date even if the error is not zero
1226: IF (x_atp_table.ship_date(i) IS NOT NULL)THEN
1227: UPDATE ahl_schedule_materials
1228: SET scheduled_date = x_atp_table.ship_date(i),
1229: object_version_number = object_version_number + 1
1223:
1224: --SKPATHAK :: Bug 8392521 :: 02-APR-2009
1225: --Update ahl_schedule_materials table with the ship_date even if the error is not zero
1226: IF (x_atp_table.ship_date(i) IS NOT NULL)THEN
1227: UPDATE ahl_schedule_materials
1228: SET scheduled_date = x_atp_table.ship_date(i),
1229: object_version_number = object_version_number + 1
1230: WHERE scheduled_material_id = x_atp_table.identifier(i);
1231: END IF;
1269: l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1270:
1271: --yazhou 12-May-2006 starts
1272: --Bug fix #5223772
1273: UPDATE ahl_schedule_materials
1274: SET scheduled_date = x_atp_table.ship_date(i),
1275: scheduled_quantity = l_planned_matrl_tbl(i).required_quantity,
1276: object_version_number = object_version_number + 1
1277: WHERE scheduled_material_id = x_atp_table.identifier(i);
1395: schm.uom,
1396: schm.requested_date,
1397: schm.status mat_status, --Added by sowsubra
1398: avtm.item_number --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1399: FROM ahl_schedule_materials schm,
1400: ahl_visit_task_matrl_v avtm
1401: WHERE schm.scheduled_material_id = avtm.schedule_material_id
1402: AND avtm.schedule_material_id = c_sch_mat_id;
1403: */
1408: schm.uom,
1409: schm.requested_date,
1410: schm.status mat_status,
1411: avtm.CONCATENATED_SEGMENTS
1412: FROM ahl_schedule_materials schm,
1413: mtl_system_items_kfv avtm
1414: WHERE SCHM.INVENTORY_ITEM_ID = AVTM.INVENTORY_ITEM_ID
1415: AND AVTM.ORGANIZATION_ID = SCHM.ORGANIZATION_ID
1416: AND SCHM.SCHEDULED_MATERIAL_ID = c_sch_mat_id;
1442: ASMT.VISIT_TASK_ID,
1443: AVTL.VISIT_TASK_NAME,
1444: ASMT.REQUESTED_QUANTITY,
1445: (SELECT scheduled_date
1446: FROM ahl_schedule_materials asml
1447: WHERE asml.scheduled_material_id = asmt.scheduled_material_id
1448: AND TRUNC(SCHEDULED_DATE) > TRUNC(REQUESTED_DATE)
1449: ) SCHEDULED_DATE,
1450: ASMT.SCHEDULED_QUANTITY,
1454: KFV.PRIMARY_UNIT_OF_MEASURE UOM,
1455: ASMT.REQUESTED_DATE,
1456: AVTL.STATUS_CODE TASK_STATUS_CODE,
1457: FLV.MEANING
1458: FROM AHL_SCHEDULE_MATERIALS ASMT,
1459: AHL_VISIT_TASKS_VL AVTL,
1460: MTL_SYSTEM_ITEMS_KFV KFV,
1461: FND_LOOKUP_VALUES_VL FLV
1462: WHERE SCHEDULED_MATERIAL_ID = c_sch_mat_id
1719: schm.inventory_item_id,
1720: schm.requested_date,
1721: schm.requested_quantity,
1722: mtl.concatenated_segments
1723: FROM ahl_schedule_materials schm,
1724: mtl_system_items_vl mtl
1725: WHERE schm.inventory_item_id = mtl.inventory_item_id
1726: AND schm.organization_id = mtl.organization_id
1727: --SKPATHAK :: Bug 8429732 :: 17-APR-2009