API Script to Update Customer Organization

API Script to Update Customer Organization

DECLARE

l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;

l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;

l_profile_id NUMBER;

x_return_status VARCHAR2(10) := NULL;

x_msg_count NUMBER := NULL;

x_msg_data LONG;

l_party_obj_version NUMBER ;  

vMsgDummy                 VARCHAR2 (5000);

vErrMsg                   LONG; 

ln_resp_id NUMBER;

ln_resp_appl_id NUMBER;

lc_exists VARCHAR2(100);

BEGIN

    begin

select responsibility_id, application_id

   into ln_resp_id, ln_resp_appl_id

   from fnd_responsibility_vl

  where responsibility_name = 'Application Developer';

exception

when others then

ln_resp_id := null;

ln_resp_appl_id := null;

end;

fnd_global.apps_initialize(fnd_global.user_id,ln_resp_id,ln_resp_appl_id);

FOR i  IN (select distinct party_id,object_version_number,created_by_module from xxfin.XXFIN_CUST_UPD x

where status = 'U'

   )

LOOP

    begin

l_party_rec.party_id := i.party_id;--Party id of the record to be updated

l_organization_rec.duns_number_c := FND_API.G_MISS_CHAR;

  l_organization_rec.party_rec      := l_party_rec;

l_party_obj_version := i.object_version_number;

    x_msg_data := null;

    

HZ_PARTY_V2PUB.update_organization

(p_init_msg_list     => FND_API.G_FALSE,

p_organization_rec     => l_organization_rec,

p_party_object_version_number  => l_party_obj_version,

x_profile_id      => l_profile_id,

x_return_status     => x_return_status,

x_msg_count      => x_msg_count,

x_msg_data       => x_msg_data

);

IF x_return_status <> 'S'

  THEN

--ROLLBACK;

        dbms_output.PUT_LINE('In error loop');

        

FOR j IN 1 .. x_msg_count

LOOP

      -- FND_MSG_PUB.GET (j, FND_API.G_FALSE, x_msg_data, vMsgDummy);

--vErrMsg := vErrMsg||('Msg' || TO_CHAR (j) || ': ' || x_msg_data);

     

FOR j IN 1 .. fnd_msg_pub.count_msg 

                            LOOP 

                                fnd_msg_pub.get (p_msg_index => j, 

                                                   p_encoded => 'F', 

                                                   p_data    => x_msg_data,             

                                             p_msg_index_out => x_msg_count 

                                    ); 

                            END LOOP; 

END LOOP; 

   dbms_output.PUT_LINE('API Returned Error Message: '||x_msg_data);

          vErrMsg := x_msg_data;

--    dbms_output.PUT_LINE('API Returned Error count='||TO_CHAR(x_msg_count));

   ELSE

COMMIT;

END IF;

--DBMS_OUTPUT.put_line('Status of the api call : '||x_return_status);

dbms_output.PUT_LINE('API return_status='|| SUBSTR (x_return_status, 1, 255));     

    

UPDATE xxfin.XXFIN_CUST_UPD

   SET status = x_return_status,

   error_message = decode(x_return_status,'S',null,substr(vErrMsg,1,4000))

  WHERE party_id = i.party_id

    AND status = 'U'

--AND (vendor_id = i.vendor_id or cust_account_id = i.cust_account_id)

--and rowid = i.rowid

;

dbms_output.put_line('upd cnt '||sql%rowcount);

    exception

        when others then

            vErrMsg :='Error at Api is '||SQLERRM;

              UPDATE xxfin.XXFIN_CUST_UPD

                 SET status = 'E',

                   error_message = substr(vErrMsg,1,4000)

                WHERE party_id = i.party_id

                  AND status = 'U'

                ;

    end;

END LOOP;

  commit;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error '||SQLERRM);

--ROLLBACK;

END;

/

exit;