Re: Store Procedure Help

Thanks for the help and that is correct on the MAX() function is not needed. That query was originally given to me in the spec and today I noticed it was unneeded. I had tried the fetch in one form or another yesterday, I honestly can not remember if it was a exact copy in syntax as was shown, regardless it would end up erroring out. The method that did end up working for me is this:

CREATE OR REPLACE procedure SP_SLB_LOCORGSAP AS

• Cursor for Organizations CURSOR ORG IS SELECT CD_ORG, NM_LST_UPDT, DT_LST_UPDT, CD_SEG FROM ORGANIZATIONS WHERE CD_ORG NOT IN (SELECT CD_ORG FROM ORGANIZATIONS_DTL);
• Cursor for Locations CURSOR LOC IS SELECT CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG FROM LOCATIONS WHERE CD_LOC NOT IN (SELECT CD_LOC FROM LOCATIONS_DTL);
• For Loopcount in Organizations LOOPORG_ID NUMBER;
• For Loopcount in Locations LOOPLOC_ID NUMBER;

	   BEGIN
	   		SELECT CD_SYSKEY_NEXT INTO LOOPLOC_ID FROM TMS_SYSKEY WHERE
NM_TABLE = 'LOCATIONS_DTL';
	   		FOR i IN LOC LOOP
				-- Insert in LOCATIONS_DTL
				INSERT INTO LOCATIONS_DTL


				(ID_LOC, CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG)

				VALUES


				(LOOPLOC_ID, i.CD_LOC, i.NM_LST_UPDT, i.DT_LST_UPDT, i.CD_SEG);

				-- Increment loop count
				LOOPLOC_ID := LOOPLOC_ID + 1;
			COMMIT;
			END LOOP;
			-- UPDATE new Max(CD_SYSKEY_NEXT)
			UPDATE TMS_SYSKEY SET CD_SYSKEY_NEXT = LOOPLOC_ID WHERE NM_TABLE =
'LOCATIONS_DTL';
			COMMIT;
		
			-- Organizations Code
			SELECT CD_SYSKEY_NEXT INTO LOOPORG_ID FROM TMS_SYSKEY WHERE
NM_TABLE = 'ORGANIZATIONS_DTL';
	   	 	FOR i IN ORG LOOP
		 		-- Insert in ORGANIZATIONS_DTL
				INSERT INTO ORGANIZATIONS_DTL


				(ID_ORG, CD_ORG, CD_ORG_TYPE, NM_LST_UPDT, DT_LST_UPDT,

CD_ORG_STATUS, CD_SEG)
				VALUES


				(LOOPORG_ID, i.CD_ORG, 8, i.NM_LST_UPDT, i.DT_LST_UPDT, 'A', 230);

				-- Increment loop count
				LOOPORG_ID := LOOPORG_ID + 1;
			COMMIT;
		 	END LOOP;
		 	-- UPDATE new Max(CD_SYSKEY_NEXT)
			UPDATE TMS_SYSKEY SET CD_SYSKEY_NEXT = LOOPORG_ID WHERE NM_TABLE =
'ORGANIZATIONS_DTL';
			COMMIT;