Home » Applications » Oracle Fusion Apps & E-Business Suite » Cycle Counting
Cycle Counting [message #273165] Tue, 09 October 2007 05:24 Go to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Hi all,
Anyone know if there is an interface for loading Cycle Count Items. I've come across the package mtl_cycle_count_items_pkg.insert_row but it does not seem to be working and there is no error message returned. When I look in the system I get the following message: You must define and save your cycle count before entering cycle count items
Anyone have any idea of how I could set the cycle count items up programmaticly? I can enter them in manually without receiving this error though.
Thks
Re: Cycle Counting [message #273330 is a reply to message #273165] Wed, 10 October 2007 00:26 Go to previous message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Ran a trace on the form and it also seemed to be using the mtl_cycle_count_items_pkg.insert_row package. This is the final code in case any one wants to use it as a reference:
PROCEDURE pls_cc_item_insert
	(
		--errbuf     OUT VARCHAR2,
		--retcode    OUT VARCHAR2,
		p_user_id NUMBER,
		--p_username VARCHAR2,
		p_login    NUMBER,
		p_file_id VARCHAR2
	) IS
		CURSOR cc_item IS
			SELECT id$,
						 file_id,
						 item_number,
						 cycle_count_header,
						 abc_class,
						 item_last_schedule_date,
						 approval_tolerance_positive,
						 approval_tolerance_negative,
						 schedule_order,
						 control_group_flag,
						 creation_date,
						 created_by,
						 created_by_name
			FROM   pls_cycle_count_items_t cci
			WHERE  cci.file_id = p_file_id;
		v_row_id VARCHAR2(240);
		v_count  NUMBER := 0;
		cc_exception EXCEPTION;
		v_check NUMBER;
		v_org   NUMBER;
	BEGIN
		BEGIN
			SELECT COUNT(*) INTO v_count FROM pls_cycle_count_items_t WHERE file_id = p_file_id;
		EXCEPTION
			WHEN OTHERS THEN
				v_count := 0;
		END;
	
		IF (v_count > 0) THEN
		
			-- Update records with user data and static information for all records
			BEGIN
				UPDATE pls_cycle_count_items_t
				SET    creation_date = SYSDATE, created_by = p_user_id --, created_by_name = p_username
				WHERE  file_id = p_file_id;
				COMMIT;
				dbms_output.put_line('*** INFO: Temporary table updated with user info');
			END;
		
			FOR r_cc_item IN cc_item LOOP
				BEGIN
				
					--Check that the Cycle count Exists
					BEGIN
						SELECT 1, cch.organization_id
						INTO   v_check, v_org
						FROM   mtl_cycle_count_headers cch
						WHERE  cch.cycle_count_header_id = r_cc_item.cycle_count_header;
					EXCEPTION
						WHEN no_data_found THEN
							v_check := -10;
							RAISE cc_exception;
					END;
				
					--Check that the Item exists
					BEGIN
						SELECT 1
						INTO   v_check
						FROM   mtl_system_items_b msi
						WHERE  msi.inventory_item_id = r_cc_item.item_number
						AND    msi.organization_id = v_org;
					EXCEPTION
						WHEN no_data_found THEN
							v_check := -20;
							RAISE cc_exception;
						WHEN OTHERS THEN
							v_check := -30;
							RAISE cc_exception;
					END;
				
					--Check that the item is not already in the Cycle count
					BEGIN
						SELECT -40
						INTO   v_check
						FROM   mtl_cycle_count_items cci
						WHERE  cci.cycle_count_header_id = r_cc_item.cycle_count_header
						AND    cci.inventory_item_id = r_cc_item.item_number;
						RAISE cc_exception;
					EXCEPTION
						WHEN no_data_found THEN
							v_check := 1;
					END;
				
					dbms_output.put_line('*** INFO: Loading the following item:' || r_cc_item.item_number);
				
					mtl_cycle_count_items_pkg.insert_row(x_rowid => v_row_id, --IN OUT VARCHAR2,
          x_cycle_count_header_id => r_cc_item.cycle_count_header, --NUMBER,
          x_inventory_item_id => r_cc_item.item_number, --NUMBER,
          x_last_update_date => SYSDATE, --DATE,
          x_last_updated_by => r_cc_item.created_by, --NUMBER,
          x_creation_date => SYSDATE, --DATE,
          x_created_by => r_cc_item.created_by, --NUMBER,
          x_last_update_login => p_login, --NUMBER,
          x_abc_class_id => r_cc_item.abc_class, --NUMBER,
          x_item_last_schedule_date => r_cc_item.item_last_schedule_date, --DATE,
          x_schedule_order => r_cc_item.schedule_order, --NUMBER,
          x_approval_tolerance_positive => r_cc_item.approval_tolerance_positive, --NUMBER,
          x_approval_tolerance_negative => r_cc_item.approval_tolerance_negative, --NUMBER,
          x_control_group_flag => r_cc_item.control_group_flag); --NUMBER
          dbms_output.put_line('/t '||r_cc_item.item_number||' loaded with rowid '||v_row_id);
				EXCEPTION
					WHEN cc_exception THEN
						IF (v_check = -10) THEN
							dbms_output.put_line('*** ERROR: Problem found in the Cycle Count Header');
						ELSIF (v_check = -20) THEN
							dbms_output.put_line('*** ERROR: Problem found in the Item, check organization assigment');
						ELSIF (v_check = -30) THEN
							dbms_output.put_line('*** ERROR: Problem found in the Item');
						ELSIF (v_check = -40) THEN
							dbms_output.put_line('*** ERROR: Duplicate entry, exists in the Cycle Count');
						END IF;
					WHEN OTHERS THEN
						dbms_output.put_line('*** ERROR: Problem occured when inserting: ' || SQLERRM);
					
				END;
			END LOOP;
		ELSE
			dbms_output.put_line('*** INFO: No records found for ' || p_file_id);
		END IF;
	
	END;
Previous Topic: form cant be queried after forms Personalization
Next Topic: Sales and Marketing Online 360 Degree View Does Not Show Currency on Invoices
Goto Forum:
  


Current Time: Wed Dec 07 20:42:30 CST 2016

Total time taken to generate the page: 0.11511 seconds