[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;
2033: END IF;/*Non ATF Case*/
2034:
2035: END IF;/*Activity Inbound*/
2036:
2037: /* Query WMS_OP_OPERATION_INSTANCES where source_task_ID = P_Source_Task_ID
2038: * with highest operation_sequence; populate this record into PL/SQL record.
2039: * Raise unexpected error if it does not exist, and return appropriate error code.
2040: */
2041: l_progress:=70;
2577: wooi.sug_to_sub_code,
2578: wooi.sug_to_locator_id,
2579: wooi.organization_id,
2580: wopd.operation_plan_id
2581: FROM wms_op_operation_instances wooi,
2582: wms_op_plan_details wopd
2583: WHERE wooi.source_task_id = p_source_task_id
2584: AND wooi.activity_type_id = p_activity_id
2585: AND wooi.operation_plan_detail_id = wopd.operation_plan_detail_id
2587: 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
2588: -- AND operation_status = g_op_ins_stat_active;
2589:
2590: l_wooi_data_rec c_wooi_data_rec%ROWTYPE;
2591: l_wooi_rec wms_op_operation_instances%ROWTYPE;
2592:
2593: -- cursor to get the plan detail for the next operation
2594: CURSOR c_next_plan_detail(v_operation_plan_id NUMBER,
2595: v_current_detail_sequence NUMBER) IS
4080:
4081: CURSOR c_operation_instance(v_source_task_id NUMBER) IS
4082: SELECT wopi.operation_type_id,
4083: wopd.operation_type wopd_op_type_id
4084: FROM wms_op_operation_instances wopi,
4085: wms_op_plan_details wopd
4086: WHERE wopi.source_task_id = v_source_task_id
4087: AND wopi.operation_status IN (G_OP_INS_STAT_PENDING,G_OP_INS_STAT_ACTIVE)
4088: AND wopi.operation_plan_detail_id = wopd.operation_plan_detail_id
4987: ATTRIBUTE13,
4988: ATTRIBUTE14,
4989: ATTRIBUTE15
4990: */
4991: FROM wms_op_operation_instances
4992: WHERE op_plan_instance_id = v_op_plan_instance_id
4993: ORDER BY operation_sequence DESC, operation_type_id DESC;
4994: --Bug 5749206. Added another order by "operation_type_id"
4995:
6058: /* END GRACE11_02_2003 */
6059:
6060: l_wooi_new_rec.op_plan_instance_id := l_wopi_split_rec.op_plan_instance_id;
6061: l_progress := 210;
6062: --Call wms_op_operation_instances table handler to insert the record
6063: SELECT wms_op_instance_s.NEXTVAL
6064: INTO l_wooi_new_rec.operation_instance_id
6065: FROM dual;
6066: l_progress := 220;
6351: operation_sequence,
6352: sug_to_sub_code,
6353: sug_to_locator_id,
6354: organization_id
6355: FROM wms_op_operation_instances
6356: WHERE source_task_id = p_source_task_id
6357: AND activity_type_id = p_activity_type_id
6358: AND operation_status = g_op_ins_stat_active;
6359:
6357: AND activity_type_id = p_activity_type_id
6358: AND operation_status = g_op_ins_stat_active;
6359:
6360: l_wooi_data_rec c_wooi_data_rec%ROWTYPE:=NULL;
6361: l_wooi_rec wms_op_operation_instances%ROWTYPE:=NULL;
6362:
6363: l_wopi_update_rec wms_op_plan_instances%ROWTYPE:=NULL;
6364: BEGIN
6365:
6812: SELECT operation_instance_id,
6813: operation_status,
6814: op_plan_instance_id,
6815: operation_type_id
6816: FROM WMS_OP_OPERATION_INSTANCES
6817: WHERE source_task_id = p_source_task_id
6818: AND activity_type_id = p_activity_type_id
6819: AND operation_status <> g_op_ins_stat_completed
6820: ORDER BY operation_sequence DESC;
6822:
6823: l_inbound_doc c_inbound_doc%ROWTYPE;
6824: l_operation_instance_rec c_operation_instance%ROWTYPE;
6825: l_plan_status NUMBER := -1;
6826: l_wooi_update_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE;
6827: l_wopi_update_rec WMS_OP_PLAN_INSTANCES%ROWTYPE;
6828: l_parent_source_task_id NUMBER;
6829:
6830:
7261: CURSOR c_operation_instance IS
7262: SELECT operation_instance_id,
7263: operation_status,
7264: op_plan_instance_id
7265: FROM WMS_OP_OPERATION_INSTANCES
7266: WHERE source_task_id = p_source_task_id
7267: AND operation_status <> G_OP_INS_STAT_COMPLETED
7268: AND activity_type_id = p_activity_type_id
7269: ORDER BY operation_sequence DESC;
7283: l_inbound_doc c_inbound_doc%ROWTYPE;
7284: l_operation_plan_id NUMBER;
7285: l_operation_instance c_operation_instance%ROWTYPE;
7286: l_plan_inst_status NUMBER := -1;
7287: l_wooi_update_rec WMS_OP_OPERATION_INSTANCES%ROWTYPE:=NULL;
7288: l_wopi_update_rec WMS_OP_PLAN_INSTANCES%ROWTYPE:=NULL;
7289: l_parent_source_task_id NUMBER;
7290: l_document_rec mtl_material_transactions_temp%ROWTYPE;
7291:
7853: operation_type_id,
7854: operation_sequence,
7855: operation_plan_detail_id,
7856: operation_status
7857: FROM wms_op_operation_instances
7858: WHERE source_task_id = p_source_task_id
7859: AND activity_type_id = p_activity_type_id
7860: ORDER BY 4;
7861:
7871: l_inbound_document c_inbound_document_details%ROWTYPE;
7872: l_wooi_data_rec c_operation_instances%ROWTYPE;
7873: l_min_opertn_sequence NUMBER := -1;
7874: l_op_plan_instance_id NUMBER;
7875: l_wooi_rec wms_op_operation_instances%ROWTYPE;
7876: l_plan_detail_id NUMBER;
7877: l_first_opertn_seq NUMBER;
7878: l_wopi_rec wms_op_plan_instances%ROWTYPE;
7879: l_op_plan_id NUMBER;
8441: CURSOR op_plan_status IS
8442: SELECT wopi.status
8443: FROM wms_op_plan_instances wopi
8444: WHERE wopi.op_plan_instance_id = (SELECT op_plan_instance_id
8445: FROM wms_op_operation_instances wooi
8446: WHERE wooi.source_task_id = p_source_task_id
8447: AND wooi.activity_type_id = p_activity_type_id
8448: AND ROWNUM =1);
8449: