Update HZ_Organization_units Table

CREATE OR REPLACE PROCEDURE XX_UPDATE_BRAND_NAME
AS
   L_PERSON_REC              HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   L_ORGANIZATION_REC        HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
   L_VERSION_NUMBER          NUMBER;
   L_PARTY_ID                NUMBER;
   L_PARTY_TYPE              VARCHAR2(100);
   L_DUNS_NAME               VARCHAR2 (100);
   L_ORIG_SYSTEM_REFERENCE   VARCHAR2 (100);
   X_PARTY_NUMBER            VARCHAR2 (100);
   X_PARTY_ID                NUMBER;
   X_PROFILE_ID              NUMBER;
   X_RETURN_STATUS           VARCHAR2 (1000);
   X_MSG_COUNT               NUMBER;
   X_MSG_DATA                VARCHAR2 (1000);
   L_ERROR_MESSAGE           VARCHAR2 (1000);

   CURSOR P_RECORDS
   IS
      SELECT *
        FROM XX_BRAND_NAME_STAG where comments is null;
       
BEGIN

   FOR P1 IN P_RECORDS
    LOOP
        IF P1.COMMENTS IS NULL THEN
 
        SELECT PARTY_ID, OBJECT_VERSION_NUMBER,PARTY_TYPE
            INTO L_PARTY_ID, L_VERSION_NUMBER,L_PARTY_TYPE
            FROM HZ_PARTIES
            WHERE PARTY_ID = P1.PARTY_ID;

      IF L_PARTY_TYPE = 'ORGANIZATION'
      THEN
         L_ORGANIZATION_REC.ORGANIZATION_NAME := P1.PARTY_NAME;
        -- L_ORGANIZATION_REC.CREATED_BY_MODULE :=  P1.CREATED_BY_MODULE;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_ID :=  P1.PARTY_ID;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_NUMBER := P1.PARTY_NUMBER;
       --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM_REFERENCE :=  P1.ORIG_SYSTEM_REFERENCE;
         --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM           := P_ORIG_SYSTEM;
         L_ORGANIZATION_REC.DUNS_NUMBER_C := P1.BRAND_NAME;

       
            UPDATE APPS.HZ_ORGANIZATION_PROFILES G
               SET DUNS_NUMBER_C =  P1.BRAND_NAME
             WHERE ATTRIBUTE_CATEGORY(+) = 'Organization Information'
               AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE
                   )
               AND PARTY_ID = L_PARTY_ID;
       

         HZ_PARTY_V2PUB.UPDATE_ORGANIZATION
                           (P_INIT_MSG_LIST                    => FND_API.G_TRUE,
                            P_ORGANIZATION_REC                 => L_ORGANIZATION_REC,
                            P_PARTY_OBJECT_VERSION_NUMBER      => L_VERSION_NUMBER,
                            X_PROFILE_ID                       => X_PROFILE_ID,
                            X_RETURN_STATUS                    => X_RETURN_STATUS,
                            X_MSG_COUNT                        => X_MSG_COUNT,
                            X_MSG_DATA                         => X_MSG_DATA
                           );
      -- P_DUNS_NAME =>'URBANOO' );
     
     
     
     END IF;
   UPDATE XX_BRAND_NAME_STAG SET COMMENTS='Y' WHERE PARTY_ID =P1.PARTY_ID;
    COMMIT;
     ELSE
UPDATE XX_BRAND_NAME_STAG SET COMMENTS='N' WHERE PARTY_ID =P1.PARTY_ID;
COMMIT;
   END IF;
   END LOOP;

   FOR I IN 1 .. X_MSG_COUNT
   LOOP
      L_ERROR_MESSAGE :=
            I
         || '. '
         || SUBSTR (FND_MSG_PUB.GET (P_ENCODED => FND_API.G_FALSE), 1, 255);
   --SINA_ERROR('UPDATE_PARTY', P_PARTY_TYPE || P_PARTY_NAME || ' ' || L_ERROR_MESSAGE);
   END LOOP;
 
END XX_UPDATE_BRAND_NAME;