We help IT Professionals succeed at work.

Oracle error in Stored procedure

suhinrasheed
suhinrasheed asked
on
595 Views
Last Modified: 2013-12-19
PROMPT ======== CREATE PROCEDURE ION_PLAN.Corp_Incremental_Load===========

CREATE OR REPLACE PROCEDURE Corp_Incremental_Load(p_download char default 'Y')
AS
   LHCODE             BUSINESS_AREA.HCODE%TYPE;
   HTABLE             HIERARCHY.THIERARCHYTABLE;
   SUM_OF_DIVISIONS   CONSTANT VARCHAR2(10):='G_0183';
   TOP_NODE CONSTANT  VARCHAR2(10):='TOP_NODE';
   ILLEGAL_CCS        CONSTANT VARCHAR2(10):='G_9999';
   OTHER_ITEMS1       CONSTANT VARCHAR2(10):='G_5967';
   OTHER_ITEMS2       CONSTANT VARCHAR2(10):='G_3252';
   LAST_DOWNLOAD      DATE;
   l_lvl              NUMBER;
   l_cnt              NUMBER;

BEGIN
      BEN_IF.ENV_PKG.P_INIT@BENPROD.WORLD;
      PKG_LOG.p_log_message('PLAN Corporate structure update');
      select nvl(min(download_time),sysdate-7) into last_download from BENPROD_BA where rownum=1;
      if p_download ='Y' or (trunc(last_download)<trunc(sysdate) and p_download='C') then

         PKG_LOG.p_log_message('PLAN CS download from BEN');

            EXECUTE IMMEDIATE 'TRUNCATE TABLE BENPROD_BA';
            EXECUTE IMMEDIATE 'TRUNCATE TABLE BENPROD_CC';

            --get list of business areas under 'Sum of Divisions G_0183'
          INSERT INTO BENPROD_BA(business_area_Code,parent_business_area_code)
            SELECT BUSINESS_AREA_CODE,DECODE(BUSINESS_AREA_CODE,SUM_OF_DIVISIONS,'TOP_NODE',OTHER_ITEMS1,'TOP_NODE',OTHER_ITEMS2,'TOP_NODE',PARENT_BUSINESS_AREA)
            FROM
            BEN_IF.V_PLAN_BA_STRUCTURE_NODES@BENPROD.WORLD
            CONNECT BY PRIOR business_area_code=parent_business_area
            START WITH business_area_code in (SUM_OF_DIVISIONS,OTHER_ITEMS1,OTHER_ITEMS2);

            --add in virtual ba for external BA clients under G_0183
            INSERT INTO BENPROD_BA(BUSINESS_AREA_CODE,PARENT_BUSINESS_AREA_CODE)
            VALUES('G_5338','TOP_NODE');

            --get any missing legal entities
            INSERT INTO LEGAL_ENTITY (
                        LEGAL_ENTITY_ID, DESCRIPTION, REGION_CODE)
            SELECT LEGAL_ENTITY_ID, DESCRIPTION, REGION_CODE FROM BEN_IF.LEGAL_ENTITY@benprod.WORLD a
            WHERE NOT EXISTS (SELECT 1 FROM LEGAL_ENTITY WHERE legal_entity_id=a.legal_entity_id);

            --get a list of all cost centre details in the plan hierarchy to staging table*/
            INSERT /*+ append*/INTO BENPROD_CC(   COST_CENTRE_CODE, BUSINESS_AREA_CODE, SAP_BA_CODE,
               CCY_CODE, LEGAL_ENTITY_ID, REGION_CODE,
                     COST_CENTRE_NAME, VALID_FROM_DATE, VALID_TO_DATE,
                     BLOCKED_PRIMARY, BLOCKED_SECONDARY, BLOCKED_PLAN_PRIMARY,
                     BLOCKED_PLAN_SECONDARY, SUBSEQUENT_CC, IN_SAP)
            SELECT
                     COST_CENTRE_CODE, A.BUSINESS_AREA_CODE, SAP_BA_CODE,
                     CCY_CODE, LEGAL_ENTITY_ID, REGION_CODE,
                     COST_CENTRE_NAME, VALID_FROM_DATE, VALID_TO_DATE,
                     BLOCKED_PRIMARY, BLOCKED_SECONDARY, BLOCKED_PLAN_PRIMARY,
                     BLOCKED_PLAN_SECONDARY, SUBSEQUENT_CC, IN_SAP
          FROM BEN_IF.V_PLAN_COST_CENTRES@BENPROD.WORLD A,BENPROD_BA B
            WHERE A.BUSINESS_aREA_CODE=B.BUSINESS_AREA_CODE;

            --get a list non sap cost centres and their business areas from a separate table in Ben interfaces
            INSERT INTO BENPROD_CC(COST_CENTRE_CODE,COST_CENTRE_NAME,BUSINESS_AREA_CODE,CCY_CODE, LEGAL_ENTITY_ID, REGION_CODE,IN_SAP)
            SELECT
                    MEMBER,MEMBER,'G_'||PARENT,PRINCIPAL_CCY, B.LEGAL_ENTITY_ID, B.REGION_CODE,'N'
          FROM
                   BEN_IF.sap_cost_centre_plan_gto@BENPROD.WORLD A,
                   LEGAL_ENTITY B,
                   REGION C
            WHERE decode(SUBSTR(A.MEMBER,1,4),'0001','0100',SUBSTR(A.MEMBER,1,4))=B.LEGAL_ENTITY_ID -- deduce the legal entity from the first for digits if none supplied.
            AND C.REGION_CODE=B.REGION_CODE
            AND EXISTS
            (SELECT 1 FROM BENPROD_BA WHERE BUSINESS_AREA_CODE='G_'||PARENT)
            AND NOT EXISTS (SELECT 1 FROM BENPROD_CC WHERE cost_Centre_code=a.MEMBER);

            /*INSERT INTO BENPROD_CC(COST_CENTRE_CODE,COST_CENTRE_NAME,BUSINESS_AREA_CODE,CCY_CODE, LEGAL_ENTITY_ID, REGION_CODE,IN_SAP)
            SELECT
                    MEMBER,MEMBER,'G_'||PARENT,PRINCIPAL_CCY, B.LEGAL_ENTITY_ID, B.REGION_CODE,'N'
          FROM
                   BEN_IF.EXT7@BENPROD.WORLD A,
                   LEGAL_ENTITY B,
                   REGION C
            WHERE '0100'=B.LEGAL_ENTITY_ID
            AND SUBSTR(A.MEMBER,1,4)='0001' --get just german cost centres.
                                                          --the legal entity is '0100' whereas first 4 digits of cc is '0001'
            AND C.REGION_CODE=B.REGION_CODE
            AND EXISTS
            (SELECT 1 FROM BENPROD_BA WHERE BUSINESS_AREA_CODE='G_'||PARENT)
            AND NOT EXISTS (SELECT 1 FROM BENPROD_CC WHERE cost_Centre_code=a.MEMBER);*/

      -- get cost centres that already exist in cost centre table and are external
      --just so BENPROD_CC contains a complete list of all cost centres that should be in cost_centre table.
      -- (not going to add them again).
      INSERT INTO BENPROD_CC (
         COST_CENTRE_CODE, BUSINESS_AREA_CODE,
         CCY_CODE, LEGAL_ENTITY_ID, REGION_CODE,
         COST_CENTRE_NAME,BLOCKED_PRIMARY, BLOCKED_SECONDARY)
      SELECT
         COST_CENTRE_CODE, BUSINESS_AREA_CODE,
         C.PRINCIPAL_CCY, B.LEGAL_ENTITY_ID, B.REGION_CODE,
         COST_CENTRE_NAME, BLOCKED_ALLOCS, BLOCKED_COSTS
          FROM
                   BEN_IF.sap_cost_centre_plan_gto@BENPROD.WORLD A,
                   COST_CENTRE b,
                   REGION C
          WHERE a.MEMBER=b.cost_centre_code
            AND C.REGION_CODE=B.REGION_CODE
            AND EXISTS
            (SELECT 1 FROM BENPROD_BA WHERE BUSINESS_AREA_CODE='G_'||PARENT)
            AND NOT EXISTS
            (SELECT 1 FROM BENPROD_CC WHERE cost_centre_code=b.cost_centre_code);
      commit;
      end if;
    PKG_LOG.p_log_message('Using CS from BEN dated '||last_download);
      -- add all new business areas. set the parents to null just temporarily.
      --(avoids FK errors)
      INSERT INTO BUSINESS_AREA (
         BUSINESS_AREA_CODE, PARENT_BUSINESS_AREA, BUSINESS_AREA_NAME)
         SELECT
                      A.BUSINESS_AREA_CODE, NULL PARENT_BUSINESS_AREA, BUSINESS_AREA_NAME
         FROM BEN_IF.V_PLAN_BA_STRUCTURE_NODES@BENPROD.WORLD A,BENPROD_BA B
         WHERE A.BUSINESS_AREA_CODE=B.BUSINESS_AREA_CODE
         AND NOT EXISTS (SELECT 1 FROM BUSINESS_AREA WHERE BUSINESS_AREA_CODE=B.BUSINESS_AREA_CODE);

    PKG_LOG.p_log_message('PLAN Created '||SQL%ROWCOUNT||' business areas');

      -- create node for cost centres that are going to be dropped from the structure
      INSERT INTO BUSINESS_AREA(BUSINESS_AREA_CODE,PARENT_BUSINESS_AREA,BUSINESS_AREA_NAME,LEAF,HCODE)
      SELECT ILLEGAL_CCS ,'TOP_NODE','Illegal cost centres','Y','00010008' FROM DUAL
      WHERE NOT EXISTS(SELECT 1 FROM BUSINESS_AREA WHERE BUSINESS_AREA_CODE=ILLEGAL_CCS );

      INSERT INTO BUSINESS_AREA(BUSINESS_AREA_CODE,PARENT_BUSINESS_AREA,BUSINESS_AREA_NAME,LEAF,HCODE)
      select TOP_NODE,NULL,'Top node',NULL,'0001' from dual
      WHERE NOT EXISTS (sELECT NULL FROM BUSINESS_AREA WHERE BUSINESS_AREA_CODE=TOP_NODE);

      --correct parents of recently added business areas
      UPDATE BUSINESS_AREA A
      SET A.PARENT_BUSINESS_AREA=(SELECT NVL(B.PARENT_BUSINESS_AREA_CODE,ILLEGAL_CCS )
      FROM BENPROD_BA B
      WHERE B.BUSINESS_AREA_CODE=A.BUSINESS_AREA_CODE)
      WHERE A.PARENT_BUSINESS_AREA IS NULL AND A.BUSINESS_AREA_CODE NOT IN (SUM_OF_DIVISIONS, OTHER_ITEMS1, OTHER_ITEMS2);
    PKG_LOG.p_log_message('Updated  '||SQL%ROWCOUNT||' parents of new business areas');


      --correct all incorrect parents
      UPDATE BUSINESS_AREA A
      SET PARENT_BUSINESS_AREA=(SELECT B.PARENT_BUSINESS_AREA_CODE FROM BENPROD_BA B
      WHERE A.BUSINESS_AREA_CODE=B.BUSINESS_AREA_CODE)
      WHERE EXISTS (SELECT NULL FROM BENPROD_BA C WHERE
      A.BUSINESS_AREA_CODE=C.BUSINESS_AREA_CODE
      AND A.PARENT_BUSINESS_AREA<>C.PARENT_BUSINESS_AREA_CODE);


      PKG_LOG.p_log_message('Corrected  '||SQL%ROWCOUNT||' existing business area parents');
      UPDATE BUSINESS_AREA A SET A.PARENT_BUSINESS_AREA='TOP_NODE'
      WHERE BUSINESS_AREA_CODE IN (SUM_OF_DIVISIONS, OTHER_ITEMS1,OTHER_ITEMS2);

      --remap all the existing cost centres to the correct ba nodes.
      UPDATE COST_CENTRE A SET (BUSINESS_AREA_CODE,LEGAL_ENTITY_ID)=
               (SELECT BUSINESS_AREA_CODE,LEGAL_ENTITY_ID FROM BENPROD_CC WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE)
    WHERE EXISTS (SELECT 1 FROM BENPROD_CC C WHERE C.COST_CENTRE_CODE=A.COST_CENTRE_CODE
      AND (A.BUSINESS_AREA_CODE<>C.BUSINESS_AREA_CODE OR A.LEGAL_ENTITY_ID<>C.LEGAL_ENTITY_ID));

      PKG_LOG.p_log_message('Moved '||SQL%ROWCOUNT||' cost centres');

      --correct denormalized ba field in usage_value table so it reflects the new cc mapping
      UPDATE USAGE_VALUE  A SET (BUSINESS_AREA_CODE,LEGAL_ENTITY_ID)=
               (SELECT BUSINESS_AREA_CODE,LEGAL_ENTITY_ID FROM COST_CENTRE WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE)
    WHERE EXISTS (SELECT 1 FROM COST_CENTRE C WHERE C.COST_CENTRE_CODE=A.COST_CENTRE_CODE
      AND (A.BUSINESS_AREA_CODE<>C.BUSINESS_AREA_CODE OR A.LEGAL_ENTITY_ID<>C.LEGAL_ENTITY_ID));

-- add in new cost centres
    INSERT INTO COST_CENTRE (
         COST_CENTRE_CODE, COST_CENTRE_NAME, BUSINESS_AREA_CODE,
         REGION_CODE, LEGAL_ENTITY_ID, LEAD_COST_CENTRE_CODE,
         TO_DELETE, BLOCKED_COSTS, BLOCKED_ALLOCS)
         SELECT
            SUBSTR(COST_CENTRE_CODE,1,15), COST_CENTRE_NAME, A.BUSINESS_AREA_CODE,
            REGION_CODE, LEGAL_ENTITY_ID, NULL,
            NULL, BLOCKED_PLAN_PRIMARY, BLOCKED_PLAN_SECONDARY
            FROM BENPROD_CC a
            WHERE NOT EXISTS (SELECT 1 FROM COST_CENTRE WHERE cost_centre_code=SUBSTR(A.COST_CENTRE_CODE,1,15));

      PKG_LOG.p_log_message('Added '||SQL%ROWCOUNT||' cost centres');

      --make sure top node is null;
      --UPDATE BUSINESS_AREA SET PARENT_BUSINESS_AREA='TOP_NODE' WHERE BUSINESS_AREA_CODE in (SUM_OF_DIVISIONS);


      DELETE FROM COST_CENTRE A WHERE NOT EXISTS
      (SELECT 1 FROM BENPROD_CC WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE)
      AND
      NOT EXISTS(SELECT 1 FROM USAGE_VALUE WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE)
      AND
      NOT EXISTS(SELECT 1 FROM PROJECT_COST WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE)
      AND
      NOT EXISTS(SELECT 1 FROM CHARGE_ELEMENT WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE)
      AND
      NOT EXISTS(SELECT 1 FROM VALID_PROJECT_CCS WHERE COST_CENTRE_CODE=A.COST_CENTRE_CODE);

      update cost_centre set to_delete=null where to_delete='Y';

      update cost_centre a set to_delete='Y',business_area_code=ILLEGAL_CCS where not exists
      (select cost_centre_code from benprod_cc b where a.cost_centre_code=b.cost_centre_code);

       --remove old business areas
      DELETE FROM BUSINESS_AREA A
    WHERE NOT EXISTS(SELECT NULL FROM BENPROD_BA B WHERE A.BUSINESS_AREA_CODE=B.BUSINESS_AREA_CODE)
      AND BUSINESS_AREA_CODE NOT IN(SUM_OF_DIVISIONS,TOP_NODE,ILLEGAL_CCS);
      --remap NS cost centres
      update cost_centre a
      set business_Area_Code='G_'||substr(cost_Centre_Code,7,4)
      where cost_centre_Code like 'NS%' and business_area_code<>'G_'||substr(cost_Centre_Code,7,4)
      and exists
      (select null from business_area b where b.business_area_Code='G_'||substr(cost_Centre_Code,7,4));

      --and not exists (select null from cost_centre f where f.business_area_code=a.business_area_code);
      pkg_log.p_log_message('PLAN Removed '||SQL%ROWCOUNT||' old business areas');
      HTABLE.OWNER:='ION_PLAN_OWNER';
      HTABLE.TABLE_NAME:='BUSINESS_AREA';
      HTABLE.PRIMARY_COLUMN(1):='BUSINESS_AREA_CODE';
      HTABLE.PARENT_COLUMN(1):='PARENT_BUSINESS_AREA';
      HIERARCHY.SET_HCODE_AND_OPTIONAL_LEAF(HTABLE);

   COMMIT;
      Populate_Ba_Hierarchy;
      COMMIT;

        SELECT MAX(LVL) INTO l_lvl FROM BUSINESS_AREA;
        IF l_lvl IN (11,12,13)
        THEN
           PKG_LOG.p_log_message('Business area Hierarchy has required levels');
        ELSE
        DBMS_OUTPUT.PUT_LINE.('Business area Hierarchy does not have the  required levels');
        PKG_LOG.p_log_message('Business area Hierarchy does not have the  required levels');
        END IF;
       
        SELECT count(*) FROM COST_CENTRE CC, REGION R
        INTO l_cnt
        WHERE CC.REGION_CODE = R.REGION_CODE
        AND R.LEAF= 'N';  
 
        IF l_cnt>0
        THEN
           DBMS_OUTPUT.PUT_LINE('Cost centres falied to rollup to a leaf region');
           PKG_LOG.p_log_message('ost centres falied to rollup to a leaf region');
        ELSE
           PKG_LOG.p_log_message('PLAN corporate structure update finished');

END;
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
198/30   PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         <an identifier> <a double-quoted delimited-identifier> delete
         exists prior <a single-quoted SQL string>
         The symbol "<an identifier>" was substituted for "(" to continue.


202/9    PLS-00103: Encountered the symbol "INTO" when expecting one of
         the following:
         , ; for group having intersect minus order start union where
         connect

LINE/COL ERROR
-------- -----------------------------------------------------------------
         The symbol ", was inserted before "INTO" to continue.

211/4    PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         if

Can somebody help me on this
Comment
Watch Question

CERTIFIED EXPERT

Commented:
look here:

        DBMS_OUTPUT.PUT_LINE.('Business area Hierarchy does not have the  required levels');

should be

        DBMS_OUTPUT.PUT_LINE('Business area Hierarchy does not have the  required levels');
CERTIFIED EXPERT

Commented:
       SELECT count(*) FROM COST_CENTRE CC, REGION R
        INTO l_cnt
        WHERE CC.REGION_CODE = R.REGION_CODE
        AND R.LEAF= 'N';  

change to

        SELECT count(*)
        INTO l_cnt
        FROM COST_CENTRE CC, REGION R
        WHERE CC.REGION_CODE = R.REGION_CODE
        AND R.LEAF= 'N';  
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.