[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Query will not complete and hangs db

The following procedure will not complete.  Can you please look at it and give me and the developer some ideas on how to deal with it.  I have included the table column names and the one index.

Table one:

CMS_PO_ITEM_INV

Columns:   EDPNO  number
                 WHSE   VARCHAR2
                 QTY       NUMBER
                 gROUP_CODE

TABLE TWO:

COLUMNS:   DC    VARCHAR 2
                   EDPNO            NUMBER
                   QTY                VARCHAR2
                   GROUP_CODE  VARCHAR2

note:   THE EDPNO IS CONVERTED AND IS NOT THE SAME DURING THE SQL LOADER OF THE TABLES.

PACKAGE FOLLOWS:

CREATE OR REPLACE PACKAGE BODY CMS_PO_DIST_ENGINE_PKG IS

  --------------------------------------------------------------------------------
  PROCEDURE Main
  --------------------------------------------------------------------------------
  (PO_ID IN NUMBER,
   v_ErrorFlag OUT VARCHAR2) IS

  CURSOR PODetl_cur IS
    SELECT *
      FROM cms_po_details
     WHERE po_header_id = PO_ID
     ORDER BY line_number;

  BEGIN

    SAVEPOINT PO_created;

    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      BEGIN
        v_LogHandle := utl_file.fopen('CMS_PO_ALLOCATION_LOG', 'UPDATE_DIST.log', 'A');
      EXCEPTION
      WHEN OTHERS THEN
        v_LogHandle := utl_file.fopen('CMS_PO_ALLOCATION_LOG', 'UPDATE_DIST.log', 'W');
      END;
      utl_file.put_line(v_LogHandle, ' ');
      utl_file.put_line(v_LogHandle, '*** Starting Distribution Engine ***');
      utl_file.put_line(v_LogHandle, to_char(sysdate, 'mm/dd/yyyy hh:mi:ss'));
      utl_file.put_line(v_LogHandle, 'User: ' || v_user);
      utl_file.put_line(v_LogHandle, 'po_header_id: ' || PO_ID);
    END IF;

    BEGIN
      -- Get CMS_PO_HEADER data...
      SELECT * INTO POHead_rec
        FROM CMS_PO_HEADER
       WHERE po_header_id = PO_ID;

      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle, '   PO Header record found: ' || POHead_rec.MASTER_PO_NO);
      END IF;

    EXCEPTION
      WHEN no_data_found THEN
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '   PO Header record does not exist');
        END IF;
        v_ErrorFlag := 'Y';

      WHEN OTHERS THEN
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '   Error selecting PO Header: ' || SQLCODE || ':' || SQLERRM);
        END IF;
        v_ErrorFlag := 'Y';

    END;

    v_LineCount := 0;

    -- Validate...
    PO_Validate(POHead_rec, v_ErrorFlag);
    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      utl_file.put_line(v_LogHandle, '   Validation complete: ' || v_ErrorFlag);
    END IF;

    IF v_ErrorFlag = 'N' THEN

      -- Delete existing distribution records for this master PO.
      /*DELETE FROM cms_po_distribution
       WHERE po_header_id = PO_ID;*/

      -- Reset all Distribution records for this master PO.
      Distribution_reset(PO_ID, v_ErrorFlag);

      IF v_ErrorFlag = 'N' THEN
        OPEN PODetl_cur;
        LOOP
          -- Get next line from CMS_PO_DETAILS...
          FETCH PODetl_cur INTO PODetl_rec;
          EXIT WHEN PODetl_cur%NOTFOUND;

          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '   Processing: ' || PODetl_rec.line_number || ' ' || PODetl_rec.edpno);
          END IF;

          -- Validate item group code2
          GetGroupCode(PODetl_rec, GroupCode);
          IF GroupCode IS NOT NULL THEN
            IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
              utl_file.put_line(v_LogHandle, '      Group code = ' || GroupCode);
            END IF;

            -- Rule selection...
            Rule_Select(POHead_rec, PODetl_rec, DistRule_rec);
            IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
              utl_file.put_line(v_LogHandle, '      Selected rule # ' || v_RuleNo);
            END IF;

            IF v_RuleNo > 0 THEN
              -- Process...
              process(POHead_rec, PODetl_rec, v_RuleNo, v_ErrorFlag);

            ELSE
              -- Rule not found...
              IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
                utl_file.put_line(v_LogHandle, '      No Rule selected!');
              END IF;
              v_ErrorFlag := 'Y';

            END IF;

          ELSE
            -- Group code not found...
            IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
              utl_file.put_line(v_LogHandle, '      Group code not found!');
            END IF;
            v_ErrorFlag := 'Y';

          END IF;
        END LOOP;
        CLOSE PODetl_cur;

        -- Verify and COMMIT/ROLLBACK...
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '      Verifying: ' || v_ErrorFlag);
        END IF;

        Verify(PO_ID, v_ErrorFlag);
        IF v_ErrorFlag = 'N' THEN
          COMMIT;
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '      COMMIT');
          END IF;
        ELSE
          ROLLBACK TO SAVEPOINT PO_CREATED;
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '      ROLLBACK');
          END IF;
        END IF;
      END IF;

    ELSE
      -- Validation failed (override / origin region)...
      IF v_ErrorFlag = 'R' THEN
        -- Missing region code.
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '      Missing Region Code!');
        END IF;

      END IF;

      IF v_ErrorFlag = 'O' THEN
        -- Distribution override.
        dbms_output.put_line('  Skipping due to distribution override.');
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '      Override flag is set - No redistribution!');
        END IF;

      END IF;

      v_ErrorFlag := 'Y';
    END IF;

    IF utl_file.is_open(v_logHandle) THEN
      utl_file.put_line(v_LogHandle, ' ');
      utl_file.put_line(v_LogHandle, '*** Distribution Engine Complete ***');
      utl_file.fclose(v_logHandle);
    END IF;

  END Main;

  --------------------------------------------------------------------------------
  PROCEDURE PO_Validate  -- Check for distribution override and origin region
                         -- and clear existing distribution records.
  --------------------------------------------------------------------------------
  (POHead_rec IN POHead_rec%TYPE,
   v_ErrorFlag OUT VARCHAR2) IS

  BEGIN
    dbms_output.put_line('   Validate PO...');
    v_ErrorFlag := 'N';

    -- Check origin region in CMS_PO_HEADER for region code.
    BEGIN
      SELECT * INTO POCode_rec
        FROM CMS_PO_CODES
       WHERE code_type = 'R'
         AND code_value = POHead_rec.region_code;

    EXCEPTION
      WHEN no_data_found THEN
        v_ErrorFlag := 'R';

      WHEN OTHERS THEN
        NULL;
    END;

    -- Check for existing distributions and delete them if not overridden.
    BEGIN
      SELECT COUNT(*) INTO v_DistOvr
        FROM CMS_PO_DISTRIBUTION
       WHERE po_header_id = PO_ID
         AND override = 'Y';

      SELECT COUNT(*) INTO v_Distr
        FROM CMS_PO_DISTRIBUTION
       WHERE po_header_id = PO_ID;

      IF v_DistOvr > 0 THEN
        v_ErrorFlag := 'O';

      ELSE
        IF v_Distr > 0 THEN
          DELETE FROM cms_po_distribution
           WHERE po_header_id = PO_ID;
        END IF;
      END IF;
    END;
    dbms_output.put_line('      Validate error: ' || v_ErrorFlag);

  END PO_Validate;

  --------------------------------------------------------------------------------
  PROCEDURE Distribution_Reset  -- Set all existing distributions to the total received qty.
  --------------------------------------------------------------------------------
  (PO_ID IN NUMBER,
   v_ErrorFlag OUT VARCHAR2) IS

  CURSOR Reset_cur IS
    SELECT ds.po_header_id,
           ds.line_no,
           ds.ship_to_whse,
           ds.allocation_qty,
           xr.ecom_po_no,
           xr.ecom_line_no
      FROM cms_po_distribution ds,
           cms_po_xref xr
     WHERE ds.po_header_id = xr.PO_HEADER_ID
       AND ds.line_no = xr.line_number
       AND ds.po_header_id = PO_ID;

  BEGIN
    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      utl_file.put_line(v_LogHandle, '   Starting Dist Reset...');
    END IF;

    v_ErrorFlag := 'N';

    FOR Reset_rec IN Reset_cur LOOP
      EXIT WHEN Reset_cur%NOTFOUND;

      SELECT nvl(totalrecqty,0) INTO v_TRecv
        FROM PODETAILS
       WHERE ponumber = Reset_rec.ecom_po_no
         AND rtrim(linenox) = rtrim(Reset_rec.ecom_line_no);

      BEGIN
        UPDATE CMS_PO_DISTRIBUTION
           SET allocation_qty = v_TRecv
         WHERE po_header_id = Reset_rec.po_header_id
           AND line_no = Reset_rec.line_no
           AND ship_to_whse = Reset_rec.ship_to_whse;

        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '         Reset: ' || Reset_rec.line_no || ' ' || Reset_rec.ship_to_whse);
        END IF;

      EXCEPTION
        WHEN OTHERS THEN
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, 'Error in Distribution_Reset: ' || SQLCODE || ':' || SQLERRM);
          END IF;
          v_ErrorFlag := 'Y';
          EXIT;

      END;
    END LOOP;

  END Distribution_Reset;

  --------------------------------------------------------------------------------
  PROCEDURE GetGroupCode  -- Get Group Code2 value from CMS_PO_ITEM_INV
  --------------------------------------------------------------------------------
  (PODetl_rec IN PODetl_rec%TYPE,
   GroupCode OUT VARCHAR2) IS

  BEGIN
    GroupCode := NULL;

    SELECT max(GROUP_CODE) INTO GroupCode
      FROM CMS_PO_ITEM_INV
     WHERE edpno = PODetl_rec.edpno;

  EXCEPTION
    WHEN OTHERS THEN
      -- Error getting Group Code for item.
      v_ErrorFlag := 'Y';

  END GetGroupCode;

  --------------------------------------------------------------------------------
  PROCEDURE Rule_Select  -- Select distribution rule for line item.
  --------------------------------------------------------------------------------
  (POHead_rec IN POHead_rec%TYPE,
   PODetl_rec IN PODetl_rec%TYPE,
   DistRule_rec OUT DistRule_rec%TYPE) IS

  CURSOR DistRule_cur IS
    SELECT *
      FROM CMS_PO_DIST_RULES
     WHERE active = 'Y'
       AND channel = v_Channel
     ORDER BY rule_rank;

  BEGIN
    v_RuleNo := 0;
    dbms_output.put_line('      Select dist rule...');

    -- Find channel.
    BEGIN
      SELECT pc.code_value INTO v_Channel
        FROM cms_po_codes pc,
             codedesc cd
       WHERE substr(POHead_rec.reason,1,4) = substr(cd.codekey,3,4)
         AND substr(pc.code_value,1,4) = substr(cd.codeid,1,4)
         AND cd.TYPE = 'P6';

    EXCEPTION
      WHEN no_data_found THEN
        v_Channel := 'DIR';
    END;
    dbms_output.put_line('         v_Channel = ' || v_Channel);


    -- Search for rules.
    BEGIN
      -- Get item data...
      SELECT * INTO ItemMast_rec
        FROM ITEMMAST
       WHERE edpno = PODetl_rec.edpno;
      dbms_output.put_line('         Item # = ' || ItemMast_rec.itemno);

      OPEN DistRule_cur;
      LOOP
        FETCH DistRule_cur INTO DistRule_rec;
        EXIT WHEN DistRule_cur%NOTFOUND OR v_RuleNo > 0;

        dbms_output.put_line('         Rule - ' || DistRule_rec.rule_id || ' ' || DistRule_rec.rule_type ||
                             ' ' || DistRule_rec.rule_value || ' ' || DistRule_rec.channel || ' ' ||
                             DistRule_rec.min_split_domestic || ' ' || GroupCode);
        -- Item rule...
        IF DistRule_rec.rule_type = 'IT' AND
           rtrim(ItemMast_rec.itemno) = DistRule_rec.rule_value THEN
           DBMS_OUTPUT.PUT_LINE('***************************************** MATCH!!!!! *******************');
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Style rule...
        IF DistRule_rec.rule_type = 'ST' AND
           substr(ItemMast_rec.itemno,1,length(DistRule_rec.rule_value)) = DistRule_rec.rule_value THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Category rule...
        IF DistRule_rec.rule_type = 'CT' AND
           rtrim(ItemMast_rec.category) = rtrim(DistRule_rec.rule_value) THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Major category rule...
        IF DistRule_rec.rule_type = 'C1' AND
           substr(ItemMast_rec.category,1,2) = substr(DistRule_rec.rule_value,1,2) THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Minor category rule...
        IF DistRule_rec.rule_type = 'C2' AND
           substr(ItemMast_rec.category,3,2) = substr(DistRule_rec.rule_value,1,2) THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Vendor rule...
        IF DistRule_rec.rule_type = 'VN' AND
           POHead_rec.vendor_no = rtrim(DistRule_rec.rule_value) THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Buyer rule...
        IF DistRule_rec.rule_type = 'BY' AND
           rtrim(POHead_rec.buyer_cd) = rtrim(DistRule_rec.rule_value) THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

        -- Default rule...
        IF DistRule_rec.rule_type = 'DE' AND
           rtrim(GroupCode) = rtrim(DistRule_rec.rule_value) THEN
          v_RuleNo := DistRule_rec.rule_id;
          EXIT;
        END IF;

      END LOOP;
      CLOSE DistRule_cur;

      -- If no rule found, default to conveyable rule.
      IF v_RuleNo = 0 THEN
        dbms_output.put_line('      Defaulting to conveyable...');
        SELECT rule_id INTO v_RuleNo
          FROM CMS_PO_DIST_RULES
         WHERE rule_type = 'DE'
           AND channel = 'DIR'
           AND rule_value = 'CV';
      END IF;

      dbms_output.put_line('      Selected rule #: ' || v_RuleNo);

      -- Check the rule to ensure the factors add up to 1.
      SELECT SUM(dist_factor) INTO v_T_Factor
        FROM CMS_PO_DIST_DETAIL
       WHERE rule_id = v_RuleNo;

      IF v_T_Factor <> 1 THEN
        dbms_output.put_line('         Sum of rule factors <> 1!');
        v_ErrorFlag := 'Y';
      END IF;
    END;

  END Rule_Select;

  --------------------------------------------------------------------------------
  PROCEDURE Process
  --------------------------------------------------------------------------------
  (POHead_rec IN POHead_rec%TYPE,
   PODetl_rec IN PODetl_rec%TYPE,
   v_RuleNo   IN NUMBER,
   v_ErrorFlag OUT VARCHAR2) IS

  CURSOR RuleSet_cur IS
    SELECT *
      FROM cms_po_dist_detail
     WHERE rule_id = v_RuleNo
     ORDER BY seq;

  BEGIN
    v_ErrorFlag := 'N';
    v_itemcnt  := 0;
    v_Line     := PODetl_rec.line_number;
    v_HeaderID := POHead_rec.po_header_id;
    v_Override := 'N';
    v_User     := POHead_rec.user_id;
    v_ReqDate  := PODetl_rec.req_date;
    v_Reason   := PODetl_rec.reason;

    -- Check Min split qty.
    IF (substr(POHead_rec.region_code,1,2) = 'US' AND
        PODetl_rec.qty < DistRule_rec.min_split_domestic) OR
       (substr(POHead_rec.region_code,1,2) <> 'US' AND
        PODetl_rec.qty < DistRule_rec.min_split_other) THEN
      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle, '         Performing Min Split logic...');
      END IF;

      -- Lookup 1st Touch DC.
      BEGIN
        SELECT ship_to_whse INTO v_DC
          FROM cms_po_region_codes
         WHERE region_code = POHead_rec.region_code;

        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '         1st Touch DC = ' || v_DC);
        END IF;
        -- Insert 100% of buy into distribution record.
        v_Qty := PODetl_rec.qty;

        Insert_Distribution(v_HeaderID, v_Line, v_DC, v_Qty, v_Override,
                            v_User, v_ReqDate, v_Reason, v_LineCount);
        IF v_LineCount = 0 THEN
          -- Error on insert of distribution record.
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         Error inserting distribution record: ' || SQLCODE || ':' || SQLERRM);
          END IF;
          v_ErrorFlag := 'Y';
        ELSE
          dbms_output.put_line('        Distribution inserted...');
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         Distribution Inserted...');
            utl_file.put_line(v_LogHandle, '         Master PO ID / Line #: ' || v_HeaderID || ' / ' || v_Line);
            utl_file.put_line(v_LogHandle, '                DC / EDP / Qty: ' || v_DC || ' / ' || PODetl_rec.edpno || ' / ' || v_Qty);
          END IF;

        END IF;

        IF v_LineCount < 1 THEN
          -- Error inserting min split Distribution record.
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         Error inserting Min Split distribution record: ' || SQLCODE || ':' || SQLERRM);
          END IF;
          v_ErrorFlag := 'Y';
        END IF;

      EXCEPTION
        WHEN no_data_found THEN
          -- 1st touch DC not found...
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         1st Touch DC not found...');
          END IF;
          v_ErrorFlag := 'Y';
      END;

    ELSE
      -- Regular distribution logic.
      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle, '      Performing Regular distribution logic...');
      END IF;

      -- Get min purchase qty from VENDORITEMS.
      BEGIN
        SELECT to_number(minpurchase) INTO v_MinQty
          FROM vendoritems
         WHERE slotid = (SELECT MIN(slotid)
                           FROM vendoritems
                          WHERE edpno = PODetl_rec.edpno
                            AND rtrim(vendorno) = rtrim(POHead_rec.vendor_no));

        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '         Min Purch Qty = ' || v_MinQty);
        END IF;

      EXCEPTION
        WHEN no_data_found THEN
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         VendorItem not found for: ' || PODetl_rec.edpno || ' ' || POHead_rec.vendor_no);
          END IF;
          v_ErrorFlag := 'Y';
      END;

      -- For each rule set record, calculate the allocation qty and insert distribution.
      BEGIN
        SELECT COUNT(*) INTO v_SetCnt
          FROM CMS_PO_DIST_DETAIL
         WHERE rule_id = v_RuleNo;

      EXCEPTION
        WHEN no_data_found THEN
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         Error getting count of distribution rules: ' || PODetl_rec.edpno);
          END IF;
          v_ErrorFlag := 'Y';
      END;

      v_LoopCnt := 0;

      OPEN RuleSet_cur;
      LOOP
        FETCH RuleSet_cur INTO DistDetl_rec;
        EXIT WHEN RuleSet_cur%NOTFOUND;

        v_LoopCnt := v_LoopCnt + 1;
        v_DC      := DistDetl_rec.ship_to_whse;
        v_ReqDate := PODetl_rec.req_date;
        v_Reason  := PODetl_rec.reason;

        -- Get the received qty for all PO's on this distribution (subtracted from total buy qty to get v_TBuy).
        SELECT nvl(SUM(totalrecqty),0) INTO v_TRecv
          FROM PODETAILS
         WHERE edpno = PODetl_rec.edpno
           AND ponumber IN (SELECT DISTINCT ecom_po_no
                              FROM CMS_PO_XREF
                             WHERE po_header_id = v_HeaderID);

        -- Set the buy qty based on received inventory.
        v_TBuy := PODetl_rec.qty - v_TRecv;

        -- Get the total On-Hand Qty (all DC's in this distribution from CMS_PO_ITEM_INV).
        SELECT nvl(SUM(qty),0) INTO v_TInv
          FROM CMS_PO_ITEM_INV iv,
               codedesc cd
         WHERE rtrim(cd.specs) = iv.whse
           AND iv.edpno = PODetl_rec.edpno
           AND rtrim(iv.whse) IN (SELECT rtrim(ship_to_whse)
                                    FROM cms_po_distribution
                                   WHERE po_header_id = v_HeaderID);

        -- Get the proportion of available inventory from the last WMS feed values for this DC and
        -- apply it to the current available.
        SELECT nvl(SUM(qty),0) INTO v_TFeedQty
          FROM CMS_PO_ITEM_INV
         WHERE edpno = PODetl_rec.edpno
           AND whse IN (SELECT DISTINCT ship_to_whse
                          FROM cms_po_distribution
                         WHERE po_header_id = v_HeaderID
                           AND line_no = PODetl_rec.line_number);

        IF v_TFeedQty = 0 THEN
          v_TFeedQty := 1;
        END IF;

        SELECT nvl(SUM(qty),0) INTO v_DFeedQty
                  FROM CMS_PO_ITEM_INV
                 WHERE edpno = PODetl_rec.edpno
                   AND whse = v_DC;

        v_FeedFactor := v_DFeedQty / v_TFeedQty;

        SELECT (SELECT nvl(SUM(availableinv),0)
                   FROM INVDETAILS
                  WHERE edpno = PODetl_rec.edpno
                    AND substr(warehouseloc,1,2) < '90') * v_FeedFactor INTO v_DC_OH
          FROM dual;

        -- Get the total open PO's to these DC's from Ecometry.

        WITH inv AS(
          SELECT pd.ponumber, pd.linenox, pd.edpno, pd.poqty, pd.totalrecqty,
                 CASE WHEN ph.shiptoaddress IS NULL OR ph.shiptoaddress = '    ' THEN
                   '01'
                 ELSE
                   rtrim(ph.shiptoaddress)
                 END AS whse
            FROM podetails pd,
                 poheader ph
           WHERE ph.ponumber = pd.ponumber
             AND pd.edpno = PODetl_rec.edpno
             AND pd.status IN ('OP', 'PR')
             AND pd.ponumber NOT IN (SELECT DISTINCT ecom_po_no
                                             FROM CMS_PO_XREF
                                            WHERE po_header_id = v_HeaderID))
        SELECT nvl(SUM(poqty - totalrecqty),0) INTO v_TPO
          FROM inv
         WHERE rtrim(whse) IN (SELECT rtrim(ship_to_whse)
                                 FROM CMS_PO_DIST_DETAIL
                                WHERE RULE_ID = v_RuleNo);

        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '          EDP = ' || PODetl_rec.edpno);
          utl_file.put_line(v_LogHandle, '          Header ID = ' || v_HeaderID);
          utl_file.put_line(v_LogHandle, '          v_TPO = ' || v_TPO);
          utl_file.put_line(v_LogHandle, '          v_RuleNo = ' || v_RuleNo);
        END IF;
        -- Get the current available inventory for this item/DC.
        SELECT nvl(SUM(qty),0) INTO v_DCQty
          FROM CMS_PO_ITEM_INV
         WHERE edpno = PODetl_rec.edpno
           AND whse = v_DC;

        -- Get the total open PO's for this item/DC.
        WITH inv AS(
          SELECT pd.ponumber, pd.linenox, pd.edpno, pd.poqty, pd.totalrecqty,
                 CASE WHEN ph.shiptoaddress IS NULL OR ph.shiptoaddress = '    ' THEN
                   '01'
                 ELSE
                   rtrim(ph.shiptoaddress)
                 END AS whse
            FROM podetails pd,
                 poheader ph
           WHERE ph.ponumber = pd.ponumber
             AND pd.edpno = PODetl_rec.edpno
             AND pd.status IN ('OP', 'PR')
             AND pd.ponumber NOT IN (SELECT DISTINCT ecom_po_no
                                       FROM CMS_PO_XREF
                                      WHERE po_header_id = v_HeaderID))
        SELECT nvl(SUM(poqty - totalrecqty),0) INTO v_DCPO
          FROM inv
         WHERE whse = rtrim(v_DC)
           AND ponumber NOT IN (SELECT DISTINCT ecom_po_no
                                  FROM CMS_PO_XREF
                                 WHERE po_header_id = v_HeaderID);

        -- Compute the raw distribution qty.

        IF PODetl_rec.unit_factor IS NULL OR
           PODetl_rec.unit_factor = 0 THEN
          v_UnitFactor := 1;
        ELSE
          v_UnitFactor := PODetl_rec.unit_factor;
        END IF;

        -- This is the "simple" formula used in development. --
--      v_Qty := ((PODetl_rec.qty * PODetl_rec.unit_factor) * DistDetl_rec.dist_factor) / v_UnitFactor;

        v_Qty := (((((v_TBuy + v_TPO) * v_UnitFactor) + v_TInv) * DistDetl_rec.dist_factor) -
                  (v_DC_OH + (v_DCPO * v_UnitFactor)) / v_UnitFactor);

        -- Adjust the calculated qty to ensure the variable don't cause the distribution go negative.
        IF v_Qty > v_TBuy THEN
          v_Qty := v_TBuy - v_ItemCnt;
        END IF;

        IF v_Qty < 0 THEN
          v_Qty := 0;
        END IF;

        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, 'Master PO ID / Line # / DC: ' || v_HeaderID || ' / ' || v_Line || ' / ' || v_DC);
          utl_file.put_line(v_LogHandle, '*********************************************************************');
          utl_file.put_line(v_LogHandle, '                   Buy Qty = ' || PODetl_rec.qty);
          utl_file.put_line(v_LogHandle, '        Total received Qty = ' || v_TRecv);
          utl_file.put_line(v_LogHandle, '              Open Buy Qty = ' || v_TBuy);
          utl_file.put_line(v_LogHandle, '               Unit factor = ' || v_UnitFactor);
          utl_file.put_line(v_LogHandle, '               Feed Factor = ' || v_FeedFactor);
          utl_file.put_line(v_LogHandle, '       Distribution Factor = ' || DistDetl_rec.dist_factor);
          utl_file.put_line(v_LogHandle, '         Total On-Hand Qty = ' || v_TInv);
          utl_file.put_line(v_LogHandle, '         Total Open PO Qty = ' || v_TPO);
          utl_file.put_line(v_LogHandle, '            DC On-Hand Qty = ' || v_DC_OH);
          utl_file.put_line(v_LogHandle, '            DC OPen PO Qty = ' || v_DCPO);
          utl_file.put_line(v_LogHandle, ' ');
          utl_file.put_line(v_LogHandle, '      Raw Distribution Qty = ' || v_Qty);
          utl_file.put_line(v_LogHandle, '      Minimum Purchase Qty = ' || v_MinQty);
        END IF;

        -- Check min purchase qty in VEDORITEMS.
        /*IF v_Qty > 0 AND v_Qty < v_MinQty THEN
          IF v_MinQty > v_TBuy THEN
            v_QTY := v_TBuy;
          ELSE
            v_Qty := v_MinQty;
          END IF;
        END IF;*/

        -- Adjust last qty to balance total buy qty.
        IF v_LoopCnt = v_SetCnt AND
           (v_ItemCnt + v_Qty) <> PODetl_rec.qty THEN
          v_Qty := PODetl_rec.qty - v_ItemCnt;
        END IF;

        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle, '        Final Distribution = ' || v_Qty);
        END IF;

        v_ItemCnt := v_ItemCnt + v_Qty;

        BEGIN
          Insert_Distribution(v_HeaderID, v_Line, v_DC, v_Qty, v_Override,
                              v_User, v_ReqDate, v_Reason, v_LineCount);

          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         Distribution Inserted...');
            utl_file.put_line(v_LogHandle, '*********************************************************************');
            utl_file.put_line(v_LogHandle, 'Qty = ((((BuyQty + T_OpenPO) * Unit_Factor) + T_Inv) * Dist_Factor) -');
            utl_file.put_line(v_LogHandle, '      ((DC_OPenPO * Unit_Factor) + DC_Qty)) / Unit_Factor');
            utl_file.put_line(v_LogHandle, '*********************************************************************');
            utl_file.put_line(v_LogHandle, '*********************************************************************');
            utl_file.put_line(v_LogHandle, ' ');
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
            IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
              utl_file.put_line(v_LogHandle, '         Error Inserting Distribution: ' || SQLCODE || ':' || SQLERRM);
            END IF;
            v_ErrorFlag := 'Y';

        END;
        IF v_LineCount = 0 THEN
          -- Error on insert of distribution record.
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle, '         Zero Distribution records inserted: ' || SQLCODE || ':' || SQLERRM);
          END IF;
          v_ErrorFlag := 'Y';
        END IF;

      END LOOP;
      CLOSE RuleSet_cur;
    END IF;

  END Process;

  --------------------------------------------------------------------------------
  PROCEDURE Insert_Distribution
  --------------------------------------------------------------------------------
  (v_HeaderID IN NUMBER,
   v_Line IN VARCHAR2,
   v_DC IN VARCHAR2,
   v_Qty IN NUMBER,
   v_Override IN VARCHAR2,
   v_User IN VARCHAR2,
   v_ReqDate IN DATE,
   v_Reason IN VARCHAR2,
   v_LineCount OUT NUMBER) IS

  v_po_id  NUMBER;
  v_LogHandle_a utl_file.file_type;

  BEGIN
    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      BEGIN
        v_LogHandle_a := utl_file.fopen('CMS_PO_ALLOCATION_LOG', 'UPDATE_DIST.log', 'A');
      EXCEPTION
        WHEN OTHERS THEN
          v_LogHandle_a := utl_file.fopen('CMS_PO_ALLOCATION_LOG', 'UPDATE_DIST.log', 'W');
      END;
        utl_file.put_line(v_LogHandle_a, ' ');
        utl_file.put_line(v_LogHandle_a, '*** Starting CMS_PO_DIST_ENGINE_PKG.INSERT_DISTRIBUTION ***');
        utl_file.put_line(v_LogHandle_a, to_char(sysdate, 'mm/dd/yyyy hh:mi:ss'));
        utl_file.put_line(v_LogHandle_a, 'User: ' || v_user);
        utl_file.put_line(v_LogHandle_a, 'po_header_id: ' || PO_ID);
    END IF;

    v_Date := SYSDATE;

    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      utl_file.put_line(v_LogHandle_a, '  Entered Insert_Distribution PROCEDURE...');
    END IF;

    BEGIN

      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle_a, '  Attempting Update...');
      END IF;

      SELECT po_header_id INTO v_po_id
        FROM CMS_PO_DISTRIBUTION
       WHERE po_header_id = v_HeaderID
         AND line_no = v_Line
         AND ship_to_whse = rtrim(v_DC);

      UPDATE CMS_PO_DISTRIBUTION
         SET allocation_qty = v_Qty,
             req_date = v_ReqDate,
             reason = v_Reason,
             last_update_date = SYSDATE,
             last_updated_by = v_User
       WHERE po_header_id = v_HeaderID
         AND line_no = v_Line
         AND ship_to_whse = rtrim(v_DC);

      v_LIneCount := v_LIneCount + 1;

    EXCEPTION
      WHEN no_data_found THEN
        BEGIN
          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle_a, '  Attempting Insert...');
          END IF;

          IF v_Qty > 0 THEN
            INSERT INTO cms_po_distribution(po_header_id, line_no, ship_to_whse,
                                            allocation_qty, override, creation_date,
                                            created_by, last_update_date, last_updated_by, req_date, reason)
              VALUES(v_HeaderID, v_Line, rtrim(v_DC), v_Qty, v_Override, v_Date, v_User, v_Date, v_User,
                     v_ReqDate, v_Reason);
          END IF;

          IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
            utl_file.put_line(v_LogHandle_a, '  Dist Insert successful! ' || v_HeaderID || ' ' || v_Line || ' ' || v_DC);
          END IF;
          v_LineCount := v_LineCount + 1;

        EXCEPTION
          WHEN OTHERS THEN
            -- Error inserting Distribution record.
            dbms_output.put_line('   Error inserting Distribution record.' || SQLCODE || ' ' || SQLERRM);
            IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
              utl_file.put_line(v_LogHandle_a, '   Error inserting Distribution record.' || SQLCODE || ' ' || SQLERRM);
            END IF;
            v_ErrorFlag := 'Y';
        END;
      WHEN OTHERS THEN
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle_a, '  Update of Dist failed!');
        END IF;
        v_ErrorFlag := 'Y';
    END;

    IF utl_file.is_open(v_LogHandle_a) THEN
      utl_file.put_line(v_LogHandle_a, ' ');
      utl_file.put_line(v_LogHandle_a, '*** Distribution Insert complete ***');
      utl_file.fclose(v_logHandle_a);
    END IF;

  END Insert_Distribution;

  --------------------------------------------------------------------------------
  PROCEDURE Verify
  --------------------------------------------------------------------------------
  (PO_ID IN NUMBER,
   v_ErrorFlag OUT VARCHAR2
  ) IS
  BEGIN
    v_ErrorFlag := 'Y';

    SELECT nvl(SUM(qty),0) INTO v_DetlSum
      FROM CMS_PO_DETAILS
     WHERE po_header_id = PO_ID;

    SELECT nvl(SUM(allocation_qty),0) INTO v_DistSum
      FROM CMS_PO_DISTRIBUTION
     WHERE po_header_id = PO_ID;

    IF v_DetlSum = v_DistSum THEN
      v_ErrorFlag := 'N';
    ELSE
      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle, '   PODetail Sum / PODist Sum: ' || v_DetlSum || ' / ' || v_DistSum);
      END IF;

    END IF;

  END Verify;

  --------------------------------------------------------------------------------
  PROCEDURE WMS_Feed
  --------------------------------------------------------------------------------
  (v_ErrorFlag OUT VARCHAR2)  IS

    CURSOR ITMFEED_CUR IS
      WITH itmfeed AS (SELECT to_number(edpno) AS EDPNO
                             ,CASE WHEN dc IS NULL THEN
                                '01'
                              ELSE
                                dc
                              END  AS DC
                             ,nvl(to_number(qty),0) AS QTY
                             ,group_code AS GROUPCODE
                         FROM CMS_PO_WMS_FEED
                        WHERE LENGTH(TRIM(TRANSLATE(edpno, ' +-.0123456789', ' '))) IS NULL)
      SELECT it.edpno AS EDPNO
            ,it.DC AS DC
            ,SUM(it.qty) AS Qty
            ,MAX(it.groupcode) AS Groupcode
            ,substr(cd.codekey,3,2) AS WH
        FROM itmfeed it
             LEFT OUTER JOIN codedesc cd ON
               substr(it.DC,1,2) = substr(cd.specs,1,2) AND
               cd.TYPE = 'P2'
       GROUP BY it.edpno, it.DC, cd.codekey;

    iteminv_rec   cms_po_item_inv%ROWTYPE;

  BEGIN
    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      BEGIN
        v_LogHandle_a := utl_file.fopen('CMS_PO_ALLOCATION_LOG', 'WMS_FEED.log', 'A');
      EXCEPTION
      WHEN OTHERS THEN
        v_LogHandle_a := utl_file.fopen('CMS_PO_ALLOCATION_LOG', 'WMS_FEED.log', 'W');
      END;
      utl_file.put_line(v_LogHandle_a, ' ');
      utl_file.put_line(v_LogHandle_a, '*** Starting WMS Feed ***');
      utl_file.put_line(v_LogHandle_a, to_char(sysdate, 'mm/dd/yyyy hh:mi:ss'));
    END IF;

    v_ErrorFlag := 'N';

    -- Insert / update CMS_PO_ITEM_INV based on feed from WMS.
    FOR ITEMFEED_REC IN ITMFEED_CUR LOOP
      BEGIN
        SELECT * INTO iteminv_rec
          FROM cms_po_item_inv
         WHERE ROWID = (SELECT MIN(ROWID)
                          FROM cms_po_item_inv
                         WHERE edpno = itemfeed_rec.edpno
                           AND whse = itemfeed_rec.wh);

        -- Update the item and whse record with the updated values.
        IF iteminv_rec.qty <> itemfeed_rec.qty OR
           iteminv_rec.group_code <> itemfeed_rec.groupcode THEN
          BEGIN
            UPDATE cms_po_item_inv
               SET qty = itemfeed_rec.qty, group_code = itemfeed_rec.groupcode
             WHERE iteminv_rec.edpno = itemfeed_rec.edpno
               AND iteminv_rec.WHSE = itemfeed_rec.wh;
          EXCEPTION
            WHEN OTHERS THEN
              IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
                utl_file.put_line(v_LogHandle_a, 'Error updating record: ' || iteminv_rec.edpno || ' / ' ||
                                  iteminv_rec.whse || ' ' || SQLCODE || ':' || SQLERRM);
              END IF;
              v_ErrorFlag := 'Y';
          END;
        END IF;

        -- Update the group code on all item records if different.
        BEGIN
          UPDATE CMS_PO_ITEM_INV
             SET group_code = itemfeed_rec.groupcode
           WHERE iteminv_rec.edpno = itemfeed_rec.edpno
             AND iteminv_rec.group_code <> itemfeed_rec.groupcode;
        EXCEPTION
          WHEN OTHERS THEN
            IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
              utl_file.put_line(v_LogHandle_a, 'Error updating record: ' || iteminv_rec.edpno || ' / ' ||
                                iteminv_rec.whse || ' ' || SQLCODE || ':' || SQLERRM);
            END IF;
            v_ErrorFlag := 'Y';
        END;

      EXCEPTION
        WHEN no_data_found THEN
          BEGIN
            INSERT INTO cms_po_item_inv(edpno, whse, qty, group_code)
              VALUES(itemfeed_rec.edpno, itemfeed_rec.wh, itemfeed_rec.qty, itemfeed_rec.groupcode);
          EXCEPTION
            WHEN OTHERS THEN
              IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
                utl_file.put_line(v_LogHandle_a, 'Error inserting record: ' || iteminv_rec.edpno || ' / ' ||
                                  iteminv_rec.whse || ' ' || SQLCODE || ':' || SQLERRM);
              END IF;
              v_ErrorFlag := 'Y';
          END;
      END;
    END LOOP;

    -- Zero the quantities for items/DC's not in the WMS feed (assume out of stock).
    BEGIN
      UPDATE cms_po_item_inv
         SET qty = 0
       WHERE ROWID NOT IN (SELECT it.ROWID
                             FROM cms_po_item_inv it
                                 ,CMS_PO_WMS_FEED wms
                                 ,codedesc cd
                            WHERE it.edpno = to_number(wms.edpno)
                              AND substr(wms.dc,1,2) = substr(cd.specs,1,2)
                              AND cd.TYPE = 'P2');

    EXCEPTION
      WHEN OTHERS THEN
        IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
          utl_file.put_line(v_LogHandle_a, 'Error encountered in zero update...' || SQLCODE || ':' || SQLERRM);
        END IF;
        v_ErrorFlag := 'Y';
    END;

    IF v_ErrorFlag = 'N' THEN
      COMMIT;
      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle_a, 'Commit...');
      END IF;
    ELSE
      ROLLBACK;
      IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
        utl_file.put_line(v_LogHandle_a, 'Rollback...');
      END IF;
    END IF;

    IF cms_po_allocation_pkg.PO_CHECK_LOGGING_LEVEL(v_user) THEN
      utl_file.put_line(v_LogHandle_a, 'WMS feed is complete - Error = ' || v_ErrorFlag);
    END IF;
  END WMS_Feed;

end CMS_PO_DIST_ENGINE_PKG;
/



0
mjimison1956
Asked:
mjimison1956
1 Solution
 
MohanKNairCommented:
One suggestion for debugging is

1) Add the following line after every DML statement and select query and run the procedure
dbms_application_info.set_client_info('Message text')

2) From another session query the V$SESSION view. The user should have the privilege to query this view
select SID, client_info from v$session where client_info is not null and username='user1';

From the text message displayed for client_info it is possible to identify the location where DML or select statement is taking more time or waiting for some locks to be released.

Also query v$lock and v$locked_object views.
0
 
mjimison1956Author Commented:
Here is the block of code that is taking forever.

WITH dup_dtl AS
     (
        SELECT SUBSTR (a.fms_data, 13, 10) AS pkt, a.fms_data,
               a.slotid AS slot, b.slotid AS hdr_slot
          FROM fmsoutpt a, fmsoutpt b
         WHERE SUBSTR (a.fms_data, 13, 10) = SUBSTR (b.fms_data, 13, 10)
           AND b.rectype = 'PH'
           AND a.process_sw = 'N'
           AND a.rectype IN ('PD', 'PS')
           AND a.process_date = TO_CHAR (SYSDATE, 'yyyymmdd'))
SELECT slot
  FROM dup_dtl
 WHERE slot > hdr_slot



any help in tuning this?
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now