[Home] [Help]
133: AND activity_type_id=p_activity_id;
134:
135: CURSOR c_operation_instance_exists IS
136: SELECT 1
137: FROM WMS_OP_OPERATION_INSTANCES
138: WHERE source_task_id=p_source_task_id
139: AND activity_type_id=p_activity_id;
140:
141:
147: l_mmtt_rec MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE:=NULL;
148: l_operation_plan_rec c_operation_plan%ROWTYPE:=NULL;
149: l_operation_plan_detail_rec c_plan_detail%ROWTYPE:=NULL;
150: l_op_plan_instance_rec WMS_OP_PLAN_INSTANCES%ROWTYPE:=NULL;
151: l_operation_instance_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE :=NULL;
152: l_operation_plan_id NUMBER;
153: l_operation_exists NUMBER;
154: l_plan_exists NUMBER;
155: l_source_task_id NUMBER;
329:
330:
331: l_progress:=100;
332:
333: /* Query WMS_OP_OPERATION_INSTANCES for p_source_task_id. This should error out in case if
334: * the p_source_task_id is already an operation instance.If so raise unexpected error condition
335: */
336:
337: OPEN c_operation_instance_exists;
548: ELSIF (x_return_status<>g_ret_sts_success) THEN
549: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550: END IF;
551:
552: /*Create WMS_OP_OPERATION_INSTANCES (WOOI) PL/SQL record for the first operation.,
553: with proper operation_type_ID, source_task_ID (ID which is returned by the document hanlder),
554: etc. Status field as 'Pending'*/
555:
556: l_operation_instance_rec.operation_plan_detail_id := l_operation_plan_detail_rec.operation_plan_detail_id;
585: print_debug('operation_type_id ==>'||l_operation_instance_rec.operation_type_id,l_module_name,4);
586: print_debug('Source Task Id ==>'||l_operation_instance_rec.source_task_id,l_module_name,4);
587: END IF;
588:
589: /*Call WMS_OP_OPERATION_INSTANCES table handler, WMS_OP_RUNTIME_PVT_APIS.INSERT_OPERATION_INSTANCE,
590: to insert record into the WMS_OP_OPERATION_INSTANCES table.*/
591:
592: WMS_OP_RUNTIME_PVT_APIS.insert_operation_instance
593: ( x_return_status => x_return_status,
586: print_debug('Source Task Id ==>'||l_operation_instance_rec.source_task_id,l_module_name,4);
587: END IF;
588:
589: /*Call WMS_OP_OPERATION_INSTANCES table handler, WMS_OP_RUNTIME_PVT_APIS.INSERT_OPERATION_INSTANCE,
590: to insert record into the WMS_OP_OPERATION_INSTANCES table.*/
591:
592: WMS_OP_RUNTIME_PVT_APIS.insert_operation_instance
593: ( x_return_status => x_return_status,
594: x_msg_count => x_msg_count,
796: wopd.lpn_selection_criteria lpn_selection_criteria,
797: wopd.lpn_selection_api_id lpn_selection_api_id,
798: wopd.operation_type operation_type,
799: nvl(wopd.is_in_inventory,'N') is_in_inventory
800: FROM wms_op_operation_instances wooi,
801: wms_op_plan_details wopd
802: WHERE wooi.activity_type_id = p_activity_type_id
803: AND wooi.source_task_id = p_source_task_id
804: AND wooi.operation_status IN (g_op_ins_stat_pending,g_op_ins_stat_active)
1716: mmtt.parent_line_id,
1717: mmtt.primary_quantity,
1718: mmtt.move_order_line_id
1719: FROM mtl_material_transactions_temp mmtt,
1720: wms_op_operation_instances wooi,
1721: wms_op_plan_details wopd
1722: WHERE mmtt.transaction_temp_id=p_source_task_id
1723: AND mmtt.transaction_temp_id = wooi.source_task_id (+)
1724: AND wooi.operation_plan_detail_id = wopd.operation_plan_detail_id(+);
1728: operation_status,
1729: op_plan_instance_id,
1730: operation_plan_detail_id,
1731: operation_instance_id
1732: FROM wms_op_operation_instances
1733: WHERE source_task_id=p_source_task_id
1734: AND operation_status IN (G_OP_INS_STAT_PENDING,G_OP_INS_STAT_ACTIVE)
1735: ORDER BY operation_sequence DESC;
1736:
1735: ORDER BY operation_sequence DESC;
1736:
1737: CURSOR c_operation_sequence(v_op_plan_instance_id NUMBER) IS
1738: SELECT count(op_plan_instance_id) count
1739: FROM wms_op_operation_instances
1740: WHERE op_plan_instance_id=v_op_plan_instance_id
1741: GROUP BY op_plan_instance_id;
1742:
1743: CURSOR c_effective_date(v_person_id NUMBER) IS
1753: l_mmtt_rec MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE :=NULL;
1754: l_opertn_instance_details c_operation_instance_details%ROWTYPE := NULL;
1755: l_op_plan_instance_id NUMBER;
1756: l_operation_count NUMBER;
1757: l_wooi_rec wms_op_operation_instances%ROWTYPE := NULL;
1758: l_wopi_rec wms_op_plan_instances%ROWTYPE := NULL;
1759: l_sug_sub_code VARCHAR2(10);
1760: l_sug_location_id NUMBER;
1761: l_sug_to_lpn_id NUMBER;
2052: END IF;/*Non ATF Case*/
2053:
2054: END IF;/*Activity Inbound*/
2055:
2056: /* Query WMS_OP_OPERATION_INSTANCES where source_task_ID = P_Source_Task_ID
2057: * with highest operation_sequence; populate this record into PL/SQL record.
2058: * Raise unexpected error if it does not exist, and return appropriate error code.
2059: */
2060: l_progress:=70;
2614: wooi.sug_to_sub_code,
2615: wooi.sug_to_locator_id,
2616: wooi.organization_id,
2617: wopd.operation_plan_id
2618: FROM wms_op_operation_instances wooi,
2619: wms_op_plan_details wopd
2620: WHERE wooi.source_task_id = p_source_task_id
2621: AND wooi.activity_type_id = p_activity_id
2622: AND wooi.operation_plan_detail_id = wopd.operation_plan_detail_id
2624: wooi.operation_type_id DESC; -- this is added for crossdock, the last two operations will have the same operation_sequence. But we want to show drop first in this query
2625: -- AND operation_status = g_op_ins_stat_active;
2626:
2627: l_wooi_data_rec c_wooi_data_rec%ROWTYPE;
2628: l_wooi_rec wms_op_operation_instances%ROWTYPE;
2629:
2630: -- cursor to get the plan detail for the next operation
2631: CURSOR c_next_plan_detail(v_operation_plan_id NUMBER,
2632: v_current_detail_sequence NUMBER) IS
4120:
4121: CURSOR c_operation_instance(v_source_task_id NUMBER) IS
4122: SELECT wopi.operation_type_id,
4123: wopd.operation_type wopd_op_type_id
4124: FROM wms_op_operation_instances wopi,
4125: wms_op_plan_details wopd
4126: WHERE wopi.source_task_id = v_source_task_id
4127: AND wopi.operation_status IN (G_OP_INS_STAT_PENDING,G_OP_INS_STAT_ACTIVE)
4128: AND wopi.operation_plan_detail_id = wopd.operation_plan_detail_id
5099: ATTRIBUTE13,
5100: ATTRIBUTE14,
5101: ATTRIBUTE15
5102: */
5103: FROM wms_op_operation_instances
5104: WHERE op_plan_instance_id = v_op_plan_instance_id
5105: ORDER BY operation_sequence DESC, operation_type_id DESC;
5106: --Bug 5749206. Added another order by "operation_type_id"
5107:
6188: /* END GRACE11_02_2003 */
6189:
6190: l_wooi_new_rec.op_plan_instance_id := l_wopi_split_rec.op_plan_instance_id;
6191: l_progress := 210;
6192: --Call wms_op_operation_instances table handler to insert the record
6193: SELECT wms_op_instance_s.NEXTVAL
6194: INTO l_wooi_new_rec.operation_instance_id
6195: FROM dual;
6196: l_progress := 220;
6481: operation_sequence,
6482: sug_to_sub_code,
6483: sug_to_locator_id,
6484: organization_id
6485: FROM wms_op_operation_instances
6486: WHERE source_task_id = p_source_task_id
6487: AND activity_type_id = p_activity_type_id
6488: AND operation_status = g_op_ins_stat_active;
6489:
6487: AND activity_type_id = p_activity_type_id
6488: AND operation_status = g_op_ins_stat_active;
6489:
6490: l_wooi_data_rec c_wooi_data_rec%ROWTYPE:=NULL;
6491: l_wooi_rec wms_op_operation_instances%ROWTYPE:=NULL;
6492:
6493: l_wopi_update_rec wms_op_plan_instances%ROWTYPE:=NULL;
6494: BEGIN
6495:
6942: SELECT operation_instance_id,
6943: operation_status,
6944: op_plan_instance_id,
6945: operation_type_id
6946: FROM WMS_OP_OPERATION_INSTANCES
6947: WHERE source_task_id = p_source_task_id
6948: AND activity_type_id = p_activity_type_id
6949: AND operation_status <> g_op_ins_stat_completed
6950: ORDER BY operation_sequence DESC;
6952:
6953: l_inbound_doc c_inbound_doc%ROWTYPE;
6954: l_operation_instance_rec c_operation_instance%ROWTYPE;
6955: l_plan_status NUMBER := -1;
6956: l_wooi_update_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE;
6957: l_wopi_update_rec WMS_OP_PLAN_INSTANCES%ROWTYPE;
6958: l_parent_source_task_id NUMBER;
6959:
6960:
7391: CURSOR c_operation_instance IS
7392: SELECT operation_instance_id,
7393: operation_status,
7394: op_plan_instance_id
7395: FROM WMS_OP_OPERATION_INSTANCES
7396: WHERE source_task_id = p_source_task_id
7397: AND operation_status <> G_OP_INS_STAT_COMPLETED
7398: AND activity_type_id = p_activity_type_id
7399: ORDER BY operation_sequence DESC;
7413: l_inbound_doc c_inbound_doc%ROWTYPE;
7414: l_operation_plan_id NUMBER;
7415: l_operation_instance c_operation_instance%ROWTYPE;
7416: l_plan_inst_status NUMBER := -1;
7417: l_wooi_update_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE:=NULL;
7418: l_wopi_update_rec WMS_OP_PLAN_INSTANCES%ROWTYPE:=NULL;
7419: l_parent_source_task_id NUMBER;
7420: l_document_rec mtl_material_transactions_temp%ROWTYPE;
7421:
7983: operation_type_id,
7984: operation_sequence,
7985: operation_plan_detail_id,
7986: operation_status
7987: FROM wms_op_operation_instances
7988: WHERE source_task_id = p_source_task_id
7989: AND activity_type_id = p_activity_type_id
7990: ORDER BY 4;
7991:
8001: l_inbound_document c_inbound_document_details%ROWTYPE;
8002: l_wooi_data_rec c_operation_instances%ROWTYPE;
8003: l_min_opertn_sequence NUMBER := -1;
8004: l_op_plan_instance_id NUMBER;
8005: l_wooi_rec wms_op_operation_instances%ROWTYPE;
8006: l_plan_detail_id NUMBER;
8007: l_first_opertn_seq NUMBER;
8008: l_wopi_rec wms_op_plan_instances%ROWTYPE;
8009: l_op_plan_id NUMBER;
8571: CURSOR op_plan_status IS
8572: SELECT wopi.status
8573: FROM wms_op_plan_instances wopi
8574: WHERE wopi.op_plan_instance_id = (SELECT op_plan_instance_id
8575: FROM wms_op_operation_instances wooi
8576: WHERE wooi.source_task_id = p_source_task_id
8577: AND wooi.activity_type_id = p_activity_type_id
8578: AND ROWNUM =1);
8579: