DBA Data[Home] [Help]

APPS.WMS_CONTROL_BOARD SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 61

      SELECT meaning
	INTO l_temp_status_code
	FROM mfg_lookups
	WHERE lookup_type = 'WMS_TASK_STATUS'
	AND   lookup_code = i;
Line: 74

   l_CursorStmt := ' SELECT wdt.status, count(*) task_count FROM ' || p_at_from || ' WHERE ' || p_at_where ||
   				   	 ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status  UNION ALL  ' ||
				   ' SELECT 1, count(*) task_count FROM ' || p_pt_from || ' WHERE ' || p_pt_where || ' HAVING count(rownum) > 0  ' ||
				   ' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ct_from || ' WHERE ' || p_ct_where || ' HAVING count(rownum) > 0 ';
Line: 82

                    ' SELECT wdt.status, count(*) task_count FROM ' || p_acy_from ||
	  			   	  ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL  ' ||
				   	  ' SELECT 1, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
				   	  ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 ' ||
				   	  ' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ccy_from ||
					  ' WHERE ' || p_ccy_where ||
				   	  ' HAVING count(rownum) > 0 ';
Line: 91

     l_CursorStmt := ' SELECT wdt.status, count(*) task_count FROM ' || p_acy_from ||
                     ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL  ' ||
                     ' SELECT 1, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
                     ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 '	||
                     ' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ccy_from ||
                  ' WHERE ' || p_ccy_where ||
                     ' HAVING count(rownum) > 0 ';
Line: 226

      SELECT meaning
	INTO l_temp_task_type
	FROM mfg_lookups
	WHERE lookup_type = 'WMS_TASK_TYPES'
	AND   lookup_code = l_loop_index;
Line: 237

   l_CursorStmt := 'SELECT NVL(wdt.task_type,mmtt.wms_task_type) task_type, count(*) task_count FROM ' ||
      				   p_at_from || ' WHERE ' || p_at_where ||
					   ' AND status IN (2,3,4,5) GROUP BY NVL(wdt.task_type,mmtt.wms_task_type) UNION ALL ' ||
     			   'SELECT mmtt.wms_task_type task_type, count(*) task_count FROM ' ||
				   		   p_pt_from || ' WHERE ' || p_pt_where || ' GROUP BY mmtt.wms_task_type UNION ALL ' ||
				   'SELECT wdth.task_type task_type, count(*) task_count FROM ' ||
				   	   p_ct_From || ' WHERE ' || p_ct_where || ' GROUP BY wdth.task_type ';
Line: 248

                       ' SELECT 3 task_type, count(*) task_count FROM ' || p_acy_from ||
           			    ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) HAVING count(rownum) > 0 UNION ALL ' ||
     			          ' SELECT 3 task_type, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
				          ' WHERE ' || p_pcy_where || --' and count(rownum) > 0  UNION ALL ' ||
				          ' UNION ALL SELECT 3 task_type, count(*) task_count FROM ' || p_ccy_From ||
				          ' WHERE ' || p_ccy_where || ' HAVING count(rownum) > 0 ';
Line: 256

      l_CursorStmt :=  ' SELECT 3 task_type, count(*) task_count FROM ' || p_acy_from ||
           			    ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) HAVING count(rownum) > 0 UNION ALL ' ||
     			          ' SELECT 3 task_type, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
				          ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 UNION ALL ' ||
				          ' UNION ALL SELECT 3 task_type, count(*) task_count FROM ' || p_ccy_From ||
				          ' WHERE ' || p_ccy_where || ' HAVING count(rownum) > 0 ';
Line: 428

	CURSOR C_mcce IS SELECT  --kkoothan
		cycle_count_entry_id
	,	1 -- status
	,	task_priority
	FROM MTL_CYCLE_COUNT_ENTRIES
	WHERE cycle_count_entry_id = p_transaction_temp_id
	FOR UPDATE OF entry_status_code, task_priority NOWAIT;
Line: 436

	CURSOR C_mmtt IS SELECT
		transaction_temp_id
	,	wms_task_status
	,	task_priority
	FROM MTL_MATERIAL_TRANSACTIONS_TEMP
	WHERE transaction_temp_id = p_transaction_temp_id
	FOR UPDATE OF wms_task_status, task_priority NOWAIT;
Line: 444

	CURSOR C_wdt IS SELECT
		transaction_temp_id
	,	task_id
	,	status
	,	priority
	,	person_id
	,	person_resource_id
	FROM WMS_DISPATCHED_TASKS
	WHERE 	transaction_temp_id = p_transaction_temp_id
	AND		task_id	= p_task_id
	FOR UPDATE OF status, priority, person_id, person_resource_id NOWAIT;
Line: 470

	 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Line: 477

	    fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
Line: 487

	       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Line: 494

		  fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
Line: 518

	  fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Line: 525

	     fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
Line: 559

     CURSOR C_mcce IS SELECT  --kkoothan
       cycle_count_entry_id
       ,	1 -- status
       ,	task_priority
	FROM MTL_CYCLE_COUNT_ENTRIES
	WHERE cycle_count_entry_id = p_transaction_temp_id
	FOR UPDATE OF entry_status_code, task_priority NOWAIT;
Line: 583

	 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Line: 590

	    fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
Line: 614

	      fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
Line: 624

		 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
Line: 638

PROCEDURE update_mcce(
	 p_cycle_count_entry_id	        IN NUMBER
	,p_priority		        IN NUMBER
	,p_updated_by			IN NUMBER
	,p_user_task_type		IN NUMBER
       ,p_last_update_date              IN      DATE    /* Bug 2372652 */
         ) IS

    l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Line: 649

	UPDATE MTL_CYCLE_COUNT_ENTRIES
        SET
		task_priority = p_priority
	,	last_updated_by	= p_updated_by
	,	last_update_date= p_last_update_date                     /* Bug 2372652 */
	,	standard_operation_id = p_user_task_type
	WHERE cycle_count_entry_id = p_cycle_count_entry_id;
Line: 661

END update_mcce;
Line: 667

PROCEDURE update_mmtt(
	 p_transaction_temp_id		IN NUMBER
	,p_priority			IN NUMBER
	,p_from_status			IN NUMBER
	,p_to_status			IN NUMBER
	,p_updated_by			IN NUMBER
	,p_user_task_type		IN NUMBER
	,p_task_type			IN NUMBER
         ) IS

    l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Line: 679

	--dbms_output.put_line('in update_mmtt '|| p_transaction_temp_id || ':' || p_priority || ':' ||p_from_status || ':' ||p_to_status);
Line: 681

	UPDATE mtl_material_transactions_temp
	SET
		task_priority = p_priority
	,	wms_task_status = decode(p_to_status, NULL, p_from_status, p_to_status)
	,	last_updated_by	= p_updated_by
	,	last_update_date= SYSDATE
	,	standard_operation_id = p_user_task_type
	,	wms_task_type = p_task_type
	WHERE transaction_temp_id = p_transaction_temp_id;
Line: 691

	--dbms_output.put_line('did update_mmtt ');
Line: 697

END update_mmtt;
Line: 701

PROCEDURE update_wdt(
	p_transaction_temp_id 	IN NUMBER
	,p_task_id		IN NUMBER
	,p_priority		IN NUMBER
	,p_from_status		IN NUMBER
	,p_to_status		IN NUMBER
	,p_person_id		IN NUMBER
	,p_person_resource_id	IN NUMBER
	,p_updated_by		IN NUMBER
	,p_user_task_type	IN NUMBER
	,p_task_type		IN NUMBER
   ) IS
    l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Line: 715

	--dbms_output.put_line('in update wdt');
Line: 716

	UPDATE wms_dispatched_tasks
	SET
		person_resource_id = p_person_resource_id
	,	person_id	   = p_person_id
	,	priority	   = p_priority
	,	status		   = p_to_status
	,	last_updated_by	   = p_updated_by
	,	last_update_date   = SYSDATE
	,	user_task_type	   = p_user_task_type
	,	task_type	   = p_task_type
	WHERE transaction_temp_id = p_transaction_temp_id
	AND	  task_id	  = nvl(p_task_id, task_id);
Line: 729

END update_wdt;
Line: 735

PROCEDURE insert_to_wdt(
	p_transaction_temp_id 	IN NUMBER
	,p_status		IN NUMBER
	,p_person_id		IN NUMBER
	,p_person_resource_id	IN NUMBER
	,p_updated_by		IN NUMBER
        ,p_transaction_source_type_id IN NUMBER --kkoothan
	,x_task_id	 OUT NOCOPY /* file.sql.39 change */ NUMBER
        ,x_priority             IN NUMBER

) IS

	l_org_id		NUMBER;
Line: 758

	SELECT wms_dispatched_tasks_s.NEXTVAL
	INTO l_next_task_id
	FROM dual ;
Line: 765

      SELECT 	organization_id, standard_operation_id, 3 --task type
	  INTO	l_org_id, l_user_task_type, l_wms_task_type
	  FROM	mtl_cycle_count_entries
	  WHERE	cycle_count_entry_id = p_transaction_temp_id;
Line: 770

	  SELECT organization_id, standard_operation_id, wms_task_type, operation_plan_id, move_order_line_id
 	  INTO	l_org_id, l_user_task_type, l_wms_task_type, l_operation_plan_id, l_move_order_line_id
          FROM	mtl_material_transactions_temp
	  WHERE	transaction_temp_id = p_transaction_temp_id;
Line: 776

	--dbms_output.put_line('Before Insert into WMSDT');
Line: 779

	INSERT INTO WMS_DISPATCHED_TASKS
	(	TASK_ID
	,	TRANSACTION_TEMP_ID
	,	ORGANIZATION_ID
	,	USER_TASK_TYPE
	,	PERSON_ID
	,	EFFECTIVE_START_DATE
	,	EFFECTIVE_END_DATE
	,	PERSON_RESOURCE_ID
	,	STATUS
	,	DISPATCHED_TIME
	,	LAST_UPDATE_DATE
	,	LAST_UPDATED_BY
	,	CREATION_DATE
	,	CREATED_BY
	,	task_type
	,       priority
	,       operation_plan_id
	,       move_order_line_id	)

	VALUES( l_next_task_id
	,	p_transaction_temp_id
	,	l_org_id
	,	Nvl(l_user_task_type,2)
	,	p_person_id
	,	sysdate
	,	sysdate
	,	p_person_resource_id
	,	p_status
	,	sysdate
	,	sysdate
	,	p_updated_by
	,	sysdate
	,	p_updated_by
	,	l_wms_task_type
        ,       x_priority
        ,       l_operation_plan_id
        ,       l_move_order_line_id);
Line: 818

END insert_to_wdt;
Line: 822

PROCEDURE delete_from_wdt(
	p_transaction_temp_id 	IN NUMBER , p_task_id IN NUMBER) IS
    l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Line: 826

	DELETE FROM wms_dispatched_tasks
	WHERE transaction_temp_id = p_transaction_temp_id
	AND task_id = nvl(p_task_id, task_id);
Line: 830

END delete_from_wdt;
Line: 848

	,p_updated_by				IN	NUMBER
	,p_task_id				IN	NUMBER
	,p_transaction_temp_id			IN	NUMBER
	,p_organization_id			IN	NUMBER
	,p_person_resource_id			IN	NUMBER
	,p_person_id				IN	NUMBER
	,p_priority				IN	NUMBER
	,p_from_status				IN	NUMBER
	,p_to_status				IN	NUMBER
	,p_user_task_type			IN 	NUMBER
	,p_task_type				IN	NUMBER
	,p_transaction_source_type_id           IN	NUMBER  -- kkoothan
        ,p_last_update_date                     IN      DATE    /* Bug 2372652 */

	) IS
        -- Bug# 1728558, added p_user_task_type parameter in task_manipulator,
	-- update_mmtt, update_wdt

	c_api_name CONSTANT VARCHAR2(20) := 'task_manipulator';
Line: 881

	/*select mlk1.meaning, mlk2.meaning
	into	from_status_code, to_status_code
	from	mfg_lookups mlk1, mfg_lookups mlk2
	where   mlk1.lookup_type = 'WMS_TASK_TYPES'
	and		mlk2.lookup_type = 'WMS_TASK_TYPES'
	and 	mlk1.lookup_code = from_status
	and 	mlk2.lookup_code = to_status;*/
Line: 906

			-- Update mmtt/mcce
                  IF p_transaction_source_type_id = 9  THEN -- kkoothan
                   -- cycle count
               	        update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				     ,p_last_update_date                     /* Bug 2372652 */
				      );
Line: 916

                	update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 927

			-- update mmtt/mcce and wdt
			--dbms_output.put_line('calling update_mmtt ');
Line: 931

                  	            update_mcce(p_transaction_temp_id
		  		    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 938

                            	   update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 947

			update_wdt(p_transaction_temp_id
				   , p_task_id
				   , p_priority
				   , from_status
				   , to_status
				   , p_person_id
				   , p_person_resource_id
				   , p_updated_by
				   , p_user_task_type
				   , p_task_type);
Line: 964

               	                   update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 971

                	            update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 979

				insert_to_wdt(p_transaction_temp_id
					      , to_status
					      , p_person_id
					      , p_person_resource_id
					      , p_updated_by
                                              , p_transaction_source_type_id -- kkoothan
					      , x_task_id
                                              , p_priority);
Line: 992

                 	            update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 999

                	            update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 1007

				delete_from_wdt(p_transaction_temp_id
						, p_task_id);
Line: 1013

				update_wdt(p_transaction_temp_id
					   , p_task_id
					   , p_priority
					   , from_status
					   , to_status
					   , p_person_id
					   , p_person_resource_id
					   , p_updated_by
					   , p_user_task_type
					   , p_task_type);
Line: 1029

                 	            update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 1036

                	            update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 1044

				delete_from_wdt(p_transaction_temp_id
					, p_task_id);
Line: 1052

               	                    update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 1059

                	            update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 1067

				update_wdt(p_transaction_temp_id
					   , p_task_id
					   , p_priority
					   , from_status
					   , to_status
					   , p_person_id
					   , p_person_resource_id
					   , p_updated_by
					   , p_user_task_type
					   , p_task_type);
Line: 1082

                 	            update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 1089

                	            update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 1097

				delete_from_wdt(p_transaction_temp_id, p_task_id);
Line: 1102

               	                    update_mcce(p_transaction_temp_id
				    ,p_priority
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_last_update_date                     /* Bug 2372652 */
				    );
Line: 1109

                	            update_mmtt(p_transaction_temp_id
				    ,p_priority
				    ,from_status
				    ,from_status
				    ,p_updated_by
				    ,p_user_task_type
				    ,p_task_type);
Line: 1117

				update_wdt(p_transaction_temp_id
					   , p_task_id
					   , p_priority
					   , from_status
					   , to_status
					   , p_person_id
					   , p_person_resource_id
					   , p_updated_by
					   , p_user_task_type
					   , p_task_type);