APPS.CZ_IMP_PR_KRS SQL Statements

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

Line: 7

					INSERTS		  OUT NOCOPY PLS_INTEGER,
					UPDATES		  OUT NOCOPY PLS_INTEGER,
                                        FAILED          OUT NOCOPY     PLS_INTEGER,
                                        DUPS            OUT NOCOPY     PLS_INTEGER,
                              inXFR_GROUP       IN    VARCHAR2
					) IS
BEGIN
	DECLARE
 		/* cursor's data found indicators */
                nOnlFSKItemId           CZ_IMP_PRICE.ITEM_ID%TYPE;

Line: 36

		nInsertCount			PLS_INTEGER:=0;					/*Inserts */

Line: 37

		nUpdateCount			PLS_INTEGER:=0;					/*Updates */

Line: 49

			SELECT DECODE(x_usesurr_itemmaster,0,FSK_ITEMMASTER_1_1,1,FSK_ITEMMASTER_1_EXT),
				 DECODE(x_usesurr_pricegroup,0,FSK_PRICEGROUP_2_1,1,FSK_PRICEGROUP_2_EXT), ROWID
                                FROM CZ_IMP_PRICE
                                WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
				ORDER BY 1,2,ROWID;

Line: 77

					SELECT ITEM_ID
					FROM CZ_ITEM_MASTERS
					WHERE ORIG_SYS_REF=sFSKITEMMASTER;

Line: 89

					SELECT PRICE_GROUP_ID
					FROM  CZ_PRICE_GROUPS
					WHERE ORIG_SYS_REF=sFSKPRICEGROUP;

Line: 99

			/* Check if this is an insert or update */
			DECLARE
				CURSOR c_onl_price  IS
					SELECT 'X' FROM CZ_PRICES
					WHERE ITEM_ID=nOnlFSKItemId
					AND PRICE_GROUP_ID=nOnlFSKPriceGroupId;

Line: 115

				 	/* Mark record as Modify and insert the item_id */
					IF (NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=1 AND sFSKITEMMASTER IS NULL) THEN
						sRecStatus:='N29';

Line: 136

				/* Insert or update */
				BEGIN
					IF(
					   sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
					   sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2) THEN
						/* This is a duplicate record */
						sRecStatus:='DUPL';

Line: 150

								/* Update */
								sDisposition:='M';

Line: 152

								nUpdateCount:=nUpdateCount+1;

Line: 154

								/*Insert */
								sDisposition:='I';

Line: 156

								nInsertCount:=nInsertCount+1;

Line: 163

                                UPDATE CZ_IMP_PRICE
				set ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,nOnlFSKItemId),
				PRICE_GROUP_ID=DECODE(sDISPOSITION,'R',PRICE_GROUP_ID,nOnlFSKPriceGroupId),
				DISPOSITION=sDisposition, REC_STATUS=sRecStatus
				WHERE ROWID = thisRowId;

Line: 180

		INSERTS:=nInsertCount;

Line: 181

		UPDATES:=nUpdateCount;

Line: 194

					INSERTS		  OUT NOCOPY PLS_INTEGER,
					UPDATES		  OUT NOCOPY PLS_INTEGER,
                                        FAILED          OUT NOCOPY     PLS_INTEGER,
                                        DUPS            OUT NOCOPY     PLS_INTEGER,
                              inXFR_GROUP       IN    VARCHAR2
					) IS
BEGIN
	DECLARE
 		/* cursor's data found indicators */
                sOrigSysRef             CZ_IMP_PRICE_GROUP.NAME%TYPE;

Line: 216

		nInsertCount		PLS_INTEGER:=0;						/*Inserts */

Line: 217

		nUpdateCount		PLS_INTEGER:=0;						/*Updates */

Line: 227

                        SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_PRICE_GROUP
			WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
			ORDER BY 1,ROWID;

Line: 233

     SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
     WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';

Line: 259

			/* Check if this is an insert or update */
			DECLARE
				CURSOR c_onl_pricegroup_prcgrpid  IS
					SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
					WHERE ORIG_SYS_REF=sOrigSysRef;

Line: 290

						/* Update so save the record */
						sDisposition:='M';

Line: 292

						nUpdateCount:=nUpdateCount+1;

Line: 294

					/*Insert */
						sDisposition:='I';

Line: 296

						nInsertCount:=nInsertCount+1;

Line: 300

              SELECT CZ_PRICE_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;

Line: 306

                                UPDATE CZ_IMP_PRICE_GROUP
				SET price_group_id = DECODE(sDISPOSITION,'R',PRICE_GROUP_ID,'I',nNextValue+nAllocateCounter, nPriceGroupId),
				DISPOSITION=sDisposition, REC_STATUS=sRecStatus
				WHERE ROWID = thisRowId;

Line: 320

		INSERTS:=nInsertCount;

Line: 321

		UPDATES:=nUpdateCount;