SQLize | PHPize | SQLtest

Create PROCEDURE CREATE_SORT_REQ1 IS L_MAT_HIER_CD VARCHAR2(18); L_CUST_HIER_CD VARCHAR2(10); L_SLS_ORG_CD VARCHAR2(4); L_SELLING_ORG_LVL_10_CD VARCHAR2(10); L_DISTR_CHNL_CD VARCHAR2(2); TYPE_SORT_RQST_REC_TBL SORT.SORT_GLOBAL.INDW_SORT_RQST_REC_TBL; CURSOR FCT_CUR IS SELECT DISTINCT EXTL_GMI_CUST_XREF_KEY, EXTL_GMI_PRD_XREF_KEY FROM INTG.CF_TRD_PRF_FCT ORDER BY EXTL_GMI_CUST_XREF_KEY, EXTL_GMI_PRD_XREF_KEY; FCT_CUR_REC FCT_CUR%ROWTYPE; CNT_SLS NUMBER; CNT_PRD NUMBER; CNT_CUST NUMBER; BEGIN DELETE FROM INTG.CF_TRD_PRF_SORT_REQ; COMMIT; OPEN FCT_CUR; LOOP FETCH FCT_CUR INTO FCT_CUR_REC; EXIT WHEN FCT_CUR%NOTFOUND; SELECT COUNT(A.INTRNL_XREF_MATCH_VAL) INTO CNT_PRD FROM INTG.VCF_EXTL_GMI_PRD_XREF A WHERE A.VAL_MATCH_ID = FCT_CUR_REC.EXTL_GMI_PRD_XREF_KEY /** ADDED BY BHARAT (2015.08.05) PURPOSE: FILTER OUT UNKNOWN PRODUCT ROWS FROM VCF_EXTL_GMI_PRD_XREF SELECT **/ AND A.INTRNL_XREF_MATCH_VAL != '000000000000000000' ; IF CNT_PRD = 0 THEN L_MAT_HIER_CD := NULL; ELSE SELECT NULLIF(A.INTRNL_XREF_MATCH_VAL,NULL) INTO L_MAT_HIER_CD FROM INTG.VCF_EXTL_GMI_PRD_XREF A WHERE A.VAL_MATCH_ID = FCT_CUR_REC.EXTL_GMI_PRD_XREF_KEY /** ADDED BY BHARAT (2015.08.05) PURPOSE: FILTER OUT UNKNOWN PRODUCT ROWS FROM VCF_EXTL_GMI_PRD_XREF SELECT **/ AND A.INTRNL_XREF_MATCH_VAL != '000000000000000000' ; END IF; SELECT COUNT(A.INTRNL_XREF_MATCH_VAL) INTO CNT_CUST FROM INTG.VCF_EXTL_GMI_CUST_XREF A WHERE A.VAL_MATCH_ID = FCT_CUR_REC.EXTL_GMI_CUST_XREF_KEY /** ADDED BY BHARAT (2015.08.05) PURPOSE: FILTER OUT UNKNOWN CUSTOMER ROWS FROM VCF_EXTL_GMI_CUST_XREF SELECT **/ AND A.INTRNL_XREF_MATCH_VAL != '0000000000' ; IF CNT_CUST = 0 THEN L_CUST_HIER_CD := NULL; ELSE SELECT NULLIF(A.INTRNL_XREF_MATCH_VAL,NULL) INTO L_CUST_HIER_CD FROM INTG.VCF_EXTL_GMI_CUST_XREF A WHERE A.VAL_MATCH_ID = FCT_CUR_REC.EXTL_GMI_CUST_XREF_KEY /** ADDED BY BHARAT (2015.08.05) PURPOSE: FILTER OUT UNKNOWN CUSTOMER ROWS FROM VCF_EXTL_GMI_CUST_XREF SELECT **/ AND A.INTRNL_XREF_MATCH_VAL != '0000000000' ; END IF; IF (L_MAT_HIER_CD IS NULL OR L_CUST_HIER_CD IS NULL) THEN CONTINUE; END IF; /** COMMENTED BY BHARAT (2015.08.05) PURPOSE: UNKNOWN RELATED CHANGES CHANGING THE FILTERING CONDITION TO FILTER OUT VALUES LIKE 18 0'S FOR L_MAT_HIER_CD OR 10'S FOR L_CUST_HIER_CD EARLIER WE SEARCHED FOR -1 OR NULL POST RECENT XREF RELATED CHANGES THERE WILL NOT BE A SITUATION WHERE L_MAT_HIER_CD OR L_CUST_HIER_CD ARE FOUND TO BE NULL OR -1 **/ -- IF (L_MAT_HIER_CD <> -1) OR (L_MAT_HIER_CD IS NOT NULL) THEN -- OLD CONDITION IF ((L_MAT_HIER_CD <> '000000000000000000' ) AND (L_CUST_HIER_CD <> '0000000000')) THEN SELECT COUNT(*) INTO CNT_SLS FROM INTG.PRD_MATL_DIST_CHNL_N A WHERE A.MATL_NBR = L_MAT_HIER_CD AND A.DISTR_CHN_LVL_DEL_FG <> 'Y' AND A.SLS_ORG_CD IN ('YCUS','GMUS') AND A.DISTR_CHNL_CD = 'FS'; IF CNT_SLS = 0 THEN L_SLS_ORG_CD := NULL; ELSE SELECT NULLIF(SLS_ORG_CD,NULL) INTO L_SLS_ORG_CD FROM INTG.PRD_MATL_DIST_CHNL_N A WHERE A.MATL_NBR = L_MAT_HIER_CD AND A.DISTR_CHN_LVL_DEL_FG <> 'Y' AND A.SLS_ORG_CD IN ('YCUS','GMUS') AND A.DISTR_CHNL_CD = 'FS'; END IF; ELSE L_SLS_ORG_CD := NULL; END IF; IF L_SLS_ORG_CD IS NULL THEN L_DISTR_CHNL_CD := NULL; ELSE L_DISTR_CHNL_CD := 'FS'; END IF; IF L_SLS_ORG_CD IS NULL THEN L_SELLING_ORG_LVL_10_CD := '0000803000'; ELSE L_SELLING_ORG_LVL_10_CD := NULL; END IF; INSERT INTO INTG.CF_TRD_PRF_SORT_REQ (APPL_CD, CUST_HIER_CD, CUST_HIER_LVL_CD, MAT_HIER_CD, MAT_HIER_LVL_CD, SLS_ORG_CD, DISTR_CHNL_CD, SELLING_ORG_LVL_10_CD, DIV_CD) VALUES ('CNF', L_CUST_HIER_CD, 'CUST', L_MAT_HIER_CD, 'BASE', L_SLS_ORG_CD, L_DISTR_CHNL_CD, L_SELLING_ORG_LVL_10_CD, NULL); COMMIT; END LOOP; CLOSE FCT_CUR; SELECT DISTINCT * BULK COLLECT INTO TYPE_SORT_RQST_REC_TBL FROM INTG.CF_TRD_PRF_SORT_REQ; SORT.SORT_GLOBAL.SP_INSERT_SORT_RQST('CNF',TYPE_SORT_RQST_REC_TBL,TRUE); Exception when others then dbms_output.put_line(sqlcode || sqlerrm); END CREATE_SORT_REQ1;