APPS.DDR_BASE_UTIL_PKG SQL Statements

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

Line: 10

INSERT INTO DDR_E_RTL_INV_ITEM
    (REC_ID, LOAD_ID, ERR_REASON,
    GLBL_ITEM_ID, GLBL_ITEM_ID_TYP,RTL_BSNS_UNIT_CD,
    RTL_SKU_ITEM_NBR,INV_LOC_CD,UOM, ON_HAND_QTY,RECVD_QTY,
    IN_TRANSIT_QTY,BCK_ORDR_QTY,QLTY_HOLD_QTY,
    ON_HAND_NET_COST_AMT, RECVD_NET_COST_AMT,
    IN_TRANSIT_NET_COST_AMT, BCKORDR_NET_COST_AMT,
    QLTY_HOLD_NET_COST_AMT, ON_HAND_RTL_AMT, RECVD_RTL_AMT,
    IN_TRANSIT_RTL_AMT,BCKORDR_RTL_AMT,
    QLTY_HOLD_RTL_AMT,SRC_SYS_IDNT, SRC_SYS_DT,
    SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT
     )
    SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
    GLBL_ITEM_ID,GLBL_ITEM_ID_TYP,  RTL_BSNS_UNIT_CD,
    RTL_SKU_ITEM_NBR, INV_LOC_CD,UOM,
    ON_HAND_QTY, RECVD_QTY,
    IN_TRANSIT_QTY, BCK_ORDR_QTY,
    QLTY_HOLD_QTY, ON_HAND_NET_COST_AMT,
    RECVD_NET_COST_AMT, IN_TRANSIT_NET_COST_AMT,
    BCKORDR_NET_COST_AMT, QLTY_HOLD_NET_COST_AMT,
    ON_HAND_RTL_AMT, RECVD_RTL_AMT,
    IN_TRANSIT_RTL_AMT, BCKORDR_RTL_AMT,
    QLTY_HOLD_RTL_AMT, SRC_SYS_IDNT,
    SRC_SYS_DT,'I','N',TRANS_DT
      FROM DDR_I_RTL_INV_ITEM
       WHERE
           (RTL_BSNS_UNIT_CD,
          TRANS_DT,
          GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          INV_LOC_CD)
          IN (
             SELECT
            RTL_BSNS_UNIT_CD,
          TRANS_DT,
          GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          INV_LOC_CD
          FROM DDR_I_RTL_INV_ITEM
          GROUP BY
              RTL_BSNS_UNIT_CD,
          TRANS_DT,
          GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          INV_LOC_CD
             HAVING COUNT(*) > 1
         );

Line: 66

DELETE FROM DDR_I_RTL_INV_ITEM
    WHERE
      (RTL_BSNS_UNIT_CD,
   TRANS_DT,
   GLBL_ITEM_ID,
   RTL_SKU_ITEM_NBR,
   GLBL_ITEM_ID_TYP,
   INV_LOC_CD)
    IN (
     SELECT
       RTL_BSNS_UNIT_CD,
    TRANS_DT,
    GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR,
    GLBL_ITEM_ID_TYP,
    INV_LOC_CD
    FROM DDR_E_RTL_INV_ITEM
    GROUP BY
        RTL_BSNS_UNIT_CD,
      TRANS_DT,
      GLBL_ITEM_ID,
      RTL_SKU_ITEM_NBR,
      GLBL_ITEM_ID_TYP,
      INV_LOC_CD
         HAVING COUNT(*) > 1
    );

Line: 102

INSERT INTO DDR_E_PRMTN_PLN
    (REC_ID, LOAD_ID, ERR_REASON,RTL_BSNS_UNIT_CD,
     PRMTN_TYP,
  PRMTN_FROM_DT,
  PRMTN_TO_DT,
  GLBL_ITEM_ID,
  RTL_SKU_ITEM_NBR,
  GLBL_ITEM_ID_TYP,
  PRMTN_PRICE_AMT,
  SRC_SYS_IDNT,
  SRC_SYS_DT,
  SRC_IDNT_FLAG,
  ACTION_FLAG,
  TRANS_DT
     )
    SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
    RTL_BSNS_UNIT_CD,
    PRMTN_TYP,
    PRMTN_FROM_DT,
    PRMTN_TO_DT,
    GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR,
    GLBL_ITEM_ID_TYP,
    PRMTN_PRICE_AMT,
    SRC_SYS_IDNT,
    SRC_SYS_DT,
    'I','N',TRANS_DT
      FROM DDR_I_PRMTN_PLN
       WHERE
           (GLBL_ITEM_ID,
        RTL_BSNS_UNIT_CD,
        PRMTN_TYP,
        PRMTN_FROM_DT,
        PRMTN_TO_DT,
        RTL_SKU_ITEM_NBR,
        GLBL_ITEM_ID_TYP,
        TRANS_DT)
          IN (
             SELECT
            GLBL_ITEM_ID,
          RTL_BSNS_UNIT_CD,
          PRMTN_TYP,
          PRMTN_FROM_DT,
          PRMTN_TO_DT,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT
          FROM DDR_I_PRMTN_PLN
          GROUP BY
              GLBL_ITEM_ID,
          RTL_BSNS_UNIT_CD,
          PRMTN_TYP,
          PRMTN_FROM_DT,
          PRMTN_TO_DT,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT
             HAVING COUNT(*) > 1
         );

Line: 166

DELETE FROM DDR_I_PRMTN_PLN
    WHERE
      (GLBL_ITEM_ID,
   RTL_BSNS_UNIT_CD,
   PRMTN_TYP,
   PRMTN_FROM_DT,
   PRMTN_TO_DT,
   RTL_SKU_ITEM_NBR,
   GLBL_ITEM_ID_TYP,
   TRANS_DT)
    IN (
     SELECT
       GLBL_ITEM_ID,
    RTL_BSNS_UNIT_CD,
    PRMTN_TYP,
    PRMTN_FROM_DT,
    PRMTN_TO_DT,
    RTL_SKU_ITEM_NBR,
    GLBL_ITEM_ID_TYP,
    TRANS_DT
    FROM DDR_E_PRMTN_PLN
    GROUP BY
        GLBL_ITEM_ID,
      RTL_BSNS_UNIT_CD,
      PRMTN_TYP,
      PRMTN_FROM_DT,
      PRMTN_TO_DT,
      RTL_SKU_ITEM_NBR,
      GLBL_ITEM_ID_TYP,
      TRANS_DT
         HAVING COUNT(*) > 1
    );

Line: 211

INSERT INTO DDR_E_RTL_ORDR_ITEM
    (REC_ID, LOAD_ID, ERR_REASON,
    RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, UOM,
    ORDR_QTY,ORDR_AMT,SRC_SYS_IDNT,
    SRC_SYS_DT, TRANS_DT, SRC_IDNT_FLAG,
    ACTION_FLAG
     )
    SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
    RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
       RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, UOM,
      ORDR_QTY,ORDR_AMT,SRC_SYS_IDNT,
      SRC_SYS_DT, TRANS_DT,'I','N'
      FROM DDR_I_RTL_ORDR_ITEM
       WHERE
           (RTL_BSNS_UNIT_CD,
          GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT)
          IN (
             SELECT
            RTL_BSNS_UNIT_CD,
          GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT
          FROM DDR_I_RTL_ORDR_ITEM
          GROUP BY
              RTL_BSNS_UNIT_CD,
          GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT
             HAVING COUNT(*) > 1
         );

Line: 253

DELETE FROM DDR_I_RTL_ORDR_ITEM
    WHERE
      (RTL_BSNS_UNIT_CD,
   GLBL_ITEM_ID,
   RTL_SKU_ITEM_NBR,
   GLBL_ITEM_ID_TYP,
   TRANS_DT)
    IN (
     SELECT
       RTL_BSNS_UNIT_CD,
    GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR,
    GLBL_ITEM_ID_TYP,
    TRANS_DT
    FROM DDR_E_RTL_ORDR_ITEM
    GROUP BY
        RTL_BSNS_UNIT_CD,
      GLBL_ITEM_ID,
      RTL_SKU_ITEM_NBR,
      GLBL_ITEM_ID_TYP,
      TRANS_DT
         HAVING COUNT(*) > 1
    );

Line: 289

INSERT INTO DDR_E_RTL_SHIP_ITEM
    (REC_ID, LOAD_ID, ERR_REASON,
    RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
    UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
    SRC_SYS_DT, SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT
     )
    SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
    RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
       RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
       UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
       SRC_SYS_DT,'I','N', TRANS_DT
      FROM DDR_I_RTL_SHIP_ITEM
       WHERE
           (GLBL_ITEM_ID,
          RTL_BSNS_UNIT_CD,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT)
          IN (
             SELECT
            GLBL_ITEM_ID,
          RTL_BSNS_UNIT_CD,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT
          FROM DDR_I_RTL_SHIP_ITEM
          GROUP BY
              GLBL_ITEM_ID,
          RTL_BSNS_UNIT_CD,
          RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP,
          TRANS_DT
             HAVING COUNT(*) > 1
         );

Line: 330

DELETE FROM DDR_I_RTL_SHIP_ITEM
    WHERE
      (GLBL_ITEM_ID,
   RTL_BSNS_UNIT_CD,
   RTL_SKU_ITEM_NBR,
   GLBL_ITEM_ID_TYP,
   TRANS_DT)
    IN (
     SELECT
       GLBL_ITEM_ID,
    RTL_BSNS_UNIT_CD,
    RTL_SKU_ITEM_NBR,
    GLBL_ITEM_ID_TYP,
    TRANS_DT
    FROM DDR_E_RTL_SHIP_ITEM
    GROUP BY
        GLBL_ITEM_ID,
      RTL_BSNS_UNIT_CD,
      RTL_SKU_ITEM_NBR,
      GLBL_ITEM_ID_TYP,
      TRANS_DT
         HAVING COUNT(*) > 1
    );

Line: 369

INSERT INTO DDR_E_RTL_SL_RTN_ITEM
    (REC_ID, LOAD_ID, ERR_REASON,
     GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
  GLBL_ITEM_ID_TYP, UOM, SLS_QTY,
  SLS_AMT, SLS_COST_AMT,RTRN_QTY,
  RTRN_AMT,RTRN_COST_AMT,SRC_SYS_IDNT,
  SRC_SYS_DT, PERIOD_TYP_FLAG,
  LOC_IDNT_CD, LOC_IDNT_FLAG,
  ORG_LVL_CD,SRC_IDNT_FLAG, ACTION_FLAG,
  TRANS_DT)
     SELECT REC_ID, DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
     GLBL_ITEM_ID,RTL_SKU_ITEM_NBR,
     GLBL_ITEM_ID_TYP, UOM, SLS_QTY,
     SLS_AMOUNT,SLS_COST_AMOUNT,
     RTRN_QTY, RTRN_AMOUNT,
     RTRN_COST_AMOUNT, SRC_SYS_IDNT,
     SRC_SYS_DT, PERIOD_TYP_FLAG,
     LOC_IDNT_CD, LOC_IDNT_FLAG,
     ORG_LVL_CD,'I','N',TRANS_DT
      FROM DDR_I_RTL_SL_RTN_ITEM
       WHERE
           (LOC_IDNT_CD, GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
             TRANS_DT, PERIOD_TYP_FLAG,
          LOC_IDNT_FLAG, ORG_LVL_CD)
          IN (
             SELECT
          LOC_IDNT_CD, GLBL_ITEM_ID,
          RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
             TRANS_DT, PERIOD_TYP_FLAG,
          LOC_IDNT_FLAG, ORG_LVL_CD
          FROM DDR_I_RTL_SL_RTN_ITEM
          GROUP BY
             LOC_IDNT_CD, GLBL_ITEM_ID,
           RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
           TRANS_DT, PERIOD_TYP_FLAG,
           LOC_IDNT_FLAG, ORG_LVL_CD
             HAVING COUNT(*) > 1
         );

Line: 414

DELETE FROM DDR_I_RTL_SL_RTN_ITEM
    WHERE
       (LOC_IDNT_CD, GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
       TRANS_DT, PERIOD_TYP_FLAG,
    LOC_IDNT_FLAG, ORG_LVL_CD)
    IN (
     SELECT
       LOC_IDNT_CD, GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
       TRANS_DT, PERIOD_TYP_FLAG,
    LOC_IDNT_FLAG, ORG_LVL_CD
    FROM DDR_E_RTL_SL_RTN_ITEM
    GROUP BY
         LOC_IDNT_CD, GLBL_ITEM_ID,
       RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
         TRANS_DT, PERIOD_TYP_FLAG,
       LOC_IDNT_FLAG, ORG_LVL_CD
         HAVING COUNT(*) > 1
    );

Line: 448

INSERT INTO DDR_E_SLS_FRCST_ITEM
    (REC_ID, LOAD_ID, ERR_REASON,
    FRCST_SLS_UOM, FRCST_NBR,
    FRCST_TYP, GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
    FRCST_SLS_QTY, FRCST_SLS_AMT,
    SRC_SYS_IDNT, SRC_SYS_DT,
    PERIOD_TYP_FLAG, LOC_IDNT_CD,
    LOC_IDNT_FLAG, ORG_LVL_CD,
    SRC_IDNT_FLAG, ACTION_FLAG,
    TRANS_DT
     )
    SELECT REC_ID,DDR_LOAD_SEQ.NEXTVAL,'Duplicate Record',
    FRCST_SLS_UOM, FRCST_NBR,
    FRCST_TYP, GLBL_ITEM_ID,
    RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
    FRCST_SLS_QTY, FRCST_SLS_AMT,
    SRC_SYS_IDNT, SRC_SYS_DT,
    PERIOD_TYP_FLAG, LOC_IDNT_CD,
    LOC_IDNT_FLAG, ORG_LVL_CD,
    'I','N', TRANS_DT
      FROM DDR_I_SLS_FRCST_ITEM
       WHERE
           (TRANS_DT, FRCST_NBR,
          FRCST_TYP, LOC_IDNT_CD,
          GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
          LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT)
          IN (
             SELECT
            TRANS_DT, FRCST_NBR,
          FRCST_TYP, LOC_IDNT_CD,
          GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
          LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
          FROM DDR_I_SLS_FRCST_ITEM
          GROUP BY
              TRANS_DT, FRCST_NBR,
          FRCST_TYP, LOC_IDNT_CD,
          GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
          GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
          LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
             HAVING COUNT(*) > 1
         );

Line: 498

DELETE FROM DDR_I_SLS_FRCST_ITEM
    WHERE
      (TRANS_DT, FRCST_NBR,
   FRCST_TYP, LOC_IDNT_CD,
   GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
   GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
   LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT)
    IN (
     SELECT
       TRANS_DT, FRCST_NBR, FRCST_TYP, LOC_IDNT_CD,
    GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
    GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
    LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
    FROM DDR_E_SLS_FRCST_ITEM
    GROUP BY
        TRANS_DT, FRCST_NBR, FRCST_TYP,
      LOC_IDNT_CD, GLBL_ITEM_ID,
      RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
      LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT
         HAVING COUNT(*) > 1
    );

Line: 528

select LKUP_NAME into RT  from DDR_R_LKUP_MST
where LKUP_TYP_CD='SYS_PARAM'
and LKUP_CD='PERFORM_DUP_CHECK';

Line: 538

select LKUP_NAME into RT  from DDR_R_LKUP_MST
where LKUP_TYP_CD='SYS_PARAM'
and LKUP_CD='DISCOVERY_MODE';

Line: 549

tab_name_ := 'select count(1) from '||map_nm || ', ' || map_stg || ' where '|| map_nm||'.load_id = '|| map_stg || '.load_id and rownum <2';

Line: 557

select LKUP_NAME into RT  from DDR_R_LKUP_MST
where LKUP_TYP_CD='SYS_PARAM'
and LKUP_CD='RUN_TYPE_FACT';

Line: 567

select LKUP_NAME into RT  from DDR_R_LKUP_MST
where LKUP_TYP_CD='SYS_PARAM'
and LKUP_CD='STAGE_TO_TARGET_VALIDATION';

Line: 579

tab_name_ := 'select count(1) from '||map_nm|| ' where rownum <2';

Line: 589

tab_name_ := 'select count(1) from '||map_nm|| ' where rownum <2';