DBA Data[Home] [Help]

APPS.CZ_IMP_PR_XFR 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,
                        inXFR_GROUP       IN    VARCHAR2)
IS
BEGIN
		DECLARE CURSOR C_XFR_PRICE_GROUP
			IS
			  SELECT PRICE_GROUP_ID,DESC_TEXT,NAME,CURRENCY,RUN_ID,DISPOSITION,DELETED_FLAG,
                           USER_STR01,USER_STR02,USER_STR03,USER_STR04,
                           USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,CREATION_DATE,LAST_UPDATE_DATE,
                           CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER,ORIG_SYS_REF
                          FROM CZ_IMP_PRICE_GROUP WHERE CZ_IMP_PRICE_GROUP.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
Line: 25

			NINSERTCOUNT			PLS_INTEGER:=0;
Line: 26

			NUPDATECOUNT			PLS_INTEGER:=0;
Line: 28

                        NOUPDATE_DESC_TEXT              NUMBER;
Line: 29

			NOUPDATE_NAME			NUMBER;
Line: 30

                        NOUPDATE_CURRENCY               NUMBER;
Line: 31

			NOUPDATE_DELETED_FLAG            NUMBER;
Line: 32

			NOUPDATE_USER_STR01              NUMBER;
Line: 33

			NOUPDATE_USER_STR02              NUMBER;
Line: 34

			NOUPDATE_USER_STR03              NUMBER;
Line: 35

			NOUPDATE_USER_STR04              NUMBER;
Line: 36

			NOUPDATE_USER_NUM01              NUMBER;
Line: 37

			NOUPDATE_USER_NUM02              NUMBER;
Line: 38

			NOUPDATE_USER_NUM03              NUMBER;
Line: 39

			NOUPDATE_USER_NUM04              NUMBER;
Line: 40

			NOUPDATE_CREATION_DATE                 NUMBER;
Line: 41

			NOUPDATE_LAST_UPDATE_DATE                NUMBER;
Line: 42

			NOUPDATE_CREATED_BY          	 NUMBER;
Line: 43

			NOUPDATE_LAST_UPDATED_BY             NUMBER;
Line: 44

			NOUPDATE_SECURITY_MASK           NUMBER;
Line: 45

			NOUPDATE_CHECKOUT_USER           NUMBER;
Line: 46

			NOUPDATE_ORIG_SYS_REF            NUMBER;
Line: 49

		-- GET UPDATE FLAG FOR EACH COLUMN
			NOUPDATE_DESC_TEXT    		 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','DESC_TEXT',inXFR_GROUP);
Line: 51

			NOUPDATE_NAME			 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','NAME',inXFR_GROUP);
Line: 52

			NOUPDATE_CURRENCY		 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CURRENCY',inXFR_GROUP);
Line: 53

			NOUPDATE_DELETED_FLAG            := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','DELETED_FLAG',inXFR_GROUP);
Line: 54

			NOUPDATE_USER_STR01              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR01',inXFR_GROUP);
Line: 55

			NOUPDATE_USER_STR02              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR02',inXFR_GROUP);
Line: 56

			NOUPDATE_USER_STR03              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR03',inXFR_GROUP);
Line: 57

			NOUPDATE_USER_STR04              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR04',inXFR_GROUP);
Line: 58

			NOUPDATE_USER_NUM01              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM01',inXFR_GROUP);
Line: 59

			NOUPDATE_USER_NUM02              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM02',inXFR_GROUP);
Line: 60

			NOUPDATE_USER_NUM03              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM03',inXFR_GROUP);
Line: 61

			NOUPDATE_USER_NUM04              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM04',inXFR_GROUP);
Line: 62

			NOUPDATE_CREATION_DATE                 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CREATION_DATE',inXFR_GROUP);
Line: 63

			NOUPDATE_LAST_UPDATE_DATE                := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','LAST_UPDATE_DATE',inXFR_GROUP);
Line: 64

                        NOUPDATE_CREATED_BY              := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CREATED_BY',inXFR_GROUP);
Line: 65

			NOUPDATE_LAST_UPDATED_BY             := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','LAST_UPDATED_BY',inXFR_GROUP);
Line: 66

			NOUPDATE_SECURITY_MASK           := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','SECURITY_MASK',inXFR_GROUP);
Line: 67

			NOUPDATE_CHECKOUT_USER           := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CHECKOUT_USER',inXFR_GROUP);
Line: 68

			NOUPDATE_ORIG_SYS_REF            := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','ORIG_SYS_REF',inXFR_GROUP);
Line: 85

						INSERT INTO CZ_PRICE_GROUPS (PRICE_GROUP_ID,DESC_TEXT,NAME,CURRENCY,
									 DELETED_FLAG,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
									 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
                                                                         CREATION_DATE,LAST_UPDATE_DATE,
									 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER,
                                                       ORIG_SYS_REF)
						VALUES
					                            (P_XFR_PRICEGROUP.PRICE_GROUP_ID,P_XFR_PRICEGROUP.DESC_TEXT,P_XFR_PRICEGROUP.NAME,P_XFR_PRICEGROUP.CURRENCY,
								     P_XFR_PRICEGROUP.DELETED_FLAG,P_XFR_PRICEGROUP.USER_STR01,P_XFR_PRICEGROUP.USER_STR02,
                                                                     P_XFR_PRICEGROUP.USER_STR03,P_XFR_PRICEGROUP.USER_STR04,
								     P_XFR_PRICEGROUP.USER_NUM01,P_XFR_PRICEGROUP.USER_NUM02,
                                                                     P_XFR_PRICEGROUP.USER_NUM03,P_XFR_PRICEGROUP.USER_NUM04,
                                                                     SYSDATE,SYSDATE,
								     1,1,NULL,P_XFR_PRICEGROUP.CHECKOUT_USER,
                                                                     P_XFR_PRICEGROUP.ORIG_SYS_REF);
Line: 100

						NINSERTCOUNT:=NINSERTCOUNT+1;
Line: 102

                                                    UPDATE CZ_IMP_price_group
                                                       SET REC_STATUS='OK'
                                                     WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
Line: 110

                                                          UPDATE CZ_IMP_price_group
                                                             SET REC_STATUS='ERR'
                                                           WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
Line: 118

						UPDATE CZ_PRICE_GROUPS
						SET
						PRICE_GROUP_ID=P_XFR_PRICEGROUP.PRICE_GROUP_ID,
						DESC_TEXT=			DECODE(NOUPDATE_DESC_TEXT,0,P_XFR_PRICEGROUP.DESC_TEXT,DESC_TEXT),
 						NAME=   			DECODE(NOUPDATE_NAME,0, P_XFR_PRICEGROUP.NAME ,NAME),
 						CURRENCY=   			DECODE(NOUPDATE_CURRENCY,0, P_XFR_PRICEGROUP.CURRENCY ,CURRENCY),
						USER_NUM01=			DECODE(NOUPDATE_USER_NUM01,0,P_XFR_PRICEGROUP.USER_NUM01,USER_NUM01),
						USER_NUM02=			DECODE(NOUPDATE_USER_NUM02,0,P_XFR_PRICEGROUP.USER_NUM02,USER_NUM02),
						USER_NUM03=			DECODE(NOUPDATE_USER_NUM03,0,P_XFR_PRICEGROUP.USER_NUM03,USER_NUM03),
						USER_NUM04=			DECODE(NOUPDATE_USER_NUM04,0,P_XFR_PRICEGROUP.USER_NUM04,USER_NUM04),
						USER_STR01=			DECODE(NOUPDATE_USER_STR01,0,P_XFR_PRICEGROUP.USER_STR01,USER_STR01),
						USER_STR02=			DECODE(NOUPDATE_USER_STR02,0,P_XFR_PRICEGROUP.USER_STR02,USER_STR02),
						USER_STR03=			DECODE(NOUPDATE_USER_STR03,0,P_XFR_PRICEGROUP.USER_STR03,USER_STR03),
						USER_STR04=			DECODE(NOUPDATE_USER_STR04,0,P_XFR_PRICEGROUP.USER_STR04,USER_STR04),
						CREATION_DATE=			DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
						LAST_UPDATE_DATE=			DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
						DELETED_FLAG=			DECODE(NOUPDATE_DELETED_FLAG,0,P_XFR_PRICEGROUP.DELETED_FLAG ,DELETED_FLAG),
						CREATED_BY=			DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
						LAST_UPDATED_BY=			DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
						SECURITY_MASK=			DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
						CHECKOUT_USER=			DECODE(NOUPDATE_CHECKOUT_USER,0,P_XFR_PRICEGROUP.CHECKOUT_USER,CHECKOUT_USER),
						ORIG_SYS_REF=			DECODE(NOUPDATE_ORIG_SYS_REF,0,P_XFR_PRICEGROUP.ORIG_SYS_REF,ORIG_SYS_REF)
						WHERE PRICE_GROUP_ID=P_XFR_PRICEGROUP.PRICE_GROUP_ID;
Line: 144

							NUPDATECOUNT :=NUPDATECOUNT+1;
Line: 146

                                                          UPDATE CZ_IMP_price_group
                                                             SET REC_STATUS='OK'
                                                           WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
Line: 155

                                                          UPDATE CZ_IMP_price_group
                                                             SET REC_STATUS='ERR'
                                                           WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
Line: 165

			INSERTS:=NINSERTCOUNT;
Line: 166

			UPDATES:=NUPDATECOUNT;
Line: 177

				INSERTS		  OUT NOCOPY PLS_INTEGER,
				UPDATES		  OUT NOCOPY PLS_INTEGER,
				FAILED		  OUT NOCOPY PLS_INTEGER,
                        inXFR_GROUP       IN    VARCHAR2)
IS
BEGIN
		DECLARE CURSOR C_XFR_PRICE
			IS
			  SELECT  *
			  FROM CZ_IMP_PRICE
			  WHERE CZ_IMP_PRICE.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
Line: 193

			NINSERTCOUNT			PLS_INTEGER:=0;
Line: 194

			NUPDATECOUNT			PLS_INTEGER:=0;
Line: 196

			NOUPDATE_DELETED_FLAG            NUMBER;
Line: 197

			NOUPDATE_ITEM_PRICE		 NUMBER;
Line: 198

			NOUPDATE_CREATION_DATE                 NUMBER;
Line: 199

			NOUPDATE_LAST_UPDATE_DATE                NUMBER;
Line: 200

			NOUPDATE_CREATED_BY          	 NUMBER;
Line: 201

			NOUPDATE_LAST_UPDATED_BY             NUMBER;
Line: 202

			NOUPDATE_SECURITY_MASK           NUMBER;
Line: 203

			NOUPDATE_CHECKOUT_USER           NUMBER;
Line: 207

		-- GET UPDATE FLAG FOR EACH COLUMN
			NOUPDATE_DELETED_FLAG            := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','DELETED_FLAG',inXFR_GROUP);
Line: 209

			NOUPDATE_ITEM_PRICE		 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','ITEM_PRICE',inXFR_GROUP);
Line: 210

			NOUPDATE_CREATION_DATE                 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CREATION_DATE',inXFR_GROUP);
Line: 211

			NOUPDATE_LAST_UPDATE_DATE                := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','LAST_UPDATE_DATE',inXFR_GROUP);
Line: 212

			NOUPDATE_CREATED_BY          	 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CREATED_BY',inXFR_GROUP);
Line: 213

			NOUPDATE_LAST_UPDATED_BY             := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','LAST_UPDATED_BY',inXFR_GROUP);
Line: 214

			NOUPDATE_SECURITY_MASK           := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','SECURITY_MASK',inXFR_GROUP);
Line: 215

			NOUPDATE_CHECKOUT_USER           := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CHECKOUT_USER',inXFR_GROUP);
Line: 232

						INSERT INTO CZ_PRICES ( ITEM_ID,PRICE_GROUP_ID,ITEM_PRICE,
									 DELETED_FLAG,CREATION_DATE,LAST_UPDATE_DATE,
									 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER)
						VALUES
					                            (P_XFR_PRICE.ITEM_ID,P_XFR_PRICE.PRICE_GROUP_ID,
								     P_XFR_PRICE.ITEM_PRICE,
								     P_XFR_PRICE.DELETED_FLAG,
                                                                     SYSDATE,SYSDATE,1,1,NULL,P_XFR_PRICE.CHECKOUT_USER);
Line: 240

						NINSERTCOUNT:=NINSERTCOUNT+1;
Line: 242

                                                    UPDATE CZ_IMP_price
                                                       SET REC_STATUS='OK'
                                                     WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
                                                       AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
Line: 251

                                                          UPDATE CZ_IMP_price
                                                             SET REC_STATUS='ERR'
                                                           WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
                                                             AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
Line: 260

						UPDATE CZ_PRICES
						SET
						ITEM_PRICE=			DECODE(NOUPDATE_ITEM_PRICE,0,P_XFR_PRICE.ITEM_PRICE,ITEM_PRICE),
						CREATION_DATE=			DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
						LAST_UPDATE_DATE=			DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
						DELETED_FLAG=			DECODE(NOUPDATE_DELETED_FLAG,0,P_XFR_PRICE.DELETED_FLAG ,DELETED_FLAG),
						CREATED_BY=			DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
						LAST_UPDATED_BY=			DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
						SECURITY_MASK=			DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
						CHECKOUT_USER=			DECODE(NOUPDATE_CHECKOUT_USER,0,P_XFR_PRICE.CHECKOUT_USER,CHECKOUT_USER)
                                                WHERE
						PRICE_GROUP_ID=			P_XFR_PRICE.PRICE_GROUP_ID AND
						ITEM_ID=			P_XFR_PRICE.ITEM_ID;
Line: 277

							NUPDATECOUNT :=NUPDATECOUNT+1;
Line: 279

                                                          UPDATE CZ_IMP_price
                                                             SET REC_STATUS='OK'
                                                           WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
                                                             AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
Line: 289

                                                          UPDATE CZ_IMP_price
                                                             SET REC_STATUS='ERR'
                                                           WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
                                                             AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
Line: 300

			INSERTS:=NINSERTCOUNT;
Line: 301

			UPDATES:=NUPDATECOUNT;