Home » Applications » Oracle Fusion Apps & E-Business Suite » Customer conversion
icon5.gif  Customer conversion [message #439882] Tue, 19 January 2010 18:46 Go to next message
oraclefaqs1
Messages: 23
Registered: October 2008
Location: SC
Junior Member

Hi all,

this is my insert statement into RA_CUSTOMER_PROFILES_INT_ALL, but for some reason the VALID payment terms and Credit_check, chedit_hold values are not populating at the header level
for l_cur_cust_reference in cur_cust_reference
      loop
          --   dbms_output.put_line( 'Insert customer: '||l_cur_cust_reference.orig_system_customer_ref||'a3');
        -----------------------------------------------------
        -- Insert/Update profile record for Customer Header       --
        -- Credit Hold, Term Name, Collector name values   --
        -- from the data file will be inserted at customer --
        -- header level only --
        -----------------------------------------------------

        for l_cur_hdr_profile in cur_hdr_profile(l_cur_cust_reference.orig_system_customer_ref)
          loop
                 --   dbms_output.put_line( 'Insert customer: '||l_cur_hdr_profile.orig_system_customer_ref||'a4');
            begin
              insert into apps.ra_customer_profiles_int_all(
                orig_system_customer_ref,
                insert_update_flag,
            --    original_system_address_ref
                customer_profile_class_name,
                CREDIT_CHECKING,                
                credit_hold,
                standard_term_name,
            --    collector_name,
                overall_credit_limit,
                trx_credit_limit,
                currency_code,
                last_updated_by,
                last_update_date,
                created_by,
                creation_date
                )
              values(
                l_cur_hdr_profile.orig_system_customer_ref,
                l_cur_hdr_profile.insert_update_flag,
             --   l_cur_hdr_profile.orig_system_address_ref
                'DEFAULT',
                'Y',
                'N',
                l_cur_hdr_profile.standard_term_name,
             --   l_cur_hdr_profile.collector_name,
                to_number(Nvl(l_cur_hdr_profile.overall_credit_limit,0)),
                to_char(Nvl(l_cur_hdr_profile.overall_credit_limit/2,0), '99999999.99'),
                'USD',
                -1,--fnd_profile.value('USER_ID'),
                sysdate,
                -1,--fnd_profile.value('USER_ID'),
                sysdate
                );
              exception
                when others then
                  p_status := 'E';
                  fnd_file.put_line(fnd_file.log,'Error while inserting into ra_customer_profiles_int_all 1:'||l_cur_cust_reference.orig_system_customer_ref||substr(sqlerrm,1,100));
                  raise ex_insert_exception;
               --   dbms_output.put_line( 'Insert customer: '||l_cur_hdr_profile.orig_system_customer_ref||'a5');
            end;
        end loop; --  end of cur_hdr_profile --

        ----------------------------------------------
        -- Insert/Update profile record for Customer Site  --
        ----------------------------------------------
        for l_cur_site_profile in cur_site_profile(l_cur_cust_reference.orig_system_customer_ref)
          loop
        --   dbms_output.put_line( l_cur_site_profile.orig_system_customer_ref||'a6');
            if l_cur_site_profile.orig_system_address_ref is not null and
               l_cur_site_profile.site_use_code = 'BILL_TO' then
          --   dbms_output.put_line( l_cur_site_profile.orig_system_customer_ref||'a7');
             begin
                insert into apps.ra_customer_profiles_int_all(
                  insert_update_flag,
                  orig_system_customer_ref,
                  orig_system_address_ref,
              --    customer_profile_class_name,
              --    collector_name,
                  overall_credit_limit,
                  trx_credit_limit,
              --    currency_code,
                  CREDIT_CHECKING,      
                  credit_hold,
                  last_updated_by,
                  last_update_date,
                  created_by,
                  creation_date,
                  standard_term_name
                  )
                values(
                  l_cur_site_profile.insert_update_flag,
                  l_cur_site_profile.orig_system_customer_ref,
                  l_cur_site_profile.orig_system_address_ref,
              --    'DEFAULT',
              --    l_cur_site_profile.collector_name,
                  NULL,
                  NULL,
              --    'USD',
                  'N',
                  'N',  -- This is a mandatory column, data need to be passed to this field --
                  -1,--fnd_profile.value('USER_ID'),
                  sysdate,
                  -1,--fnd_profile.value('USER_ID'),
                  sysdate,
                  NULL
                 );
              exception
                when others then
                  p_status := 'E';
                  fnd_file.put_line(fnd_file.log,'Error while inserting into ra_customer_profiles_int_all 2:'||l_cur_cust_reference.orig_system_customer_ref||substr(sqlerrm,1,100));
                  raise ex_insert_exception;
               --   dbms_output.put_line( 'Insert customer: '||l_cur_site_profile.orig_system_customer_ref||'a8');
              end;
            end if;
          end loop; -- end of loop l_cur_site_profile;
     end loop; --  end of loop l_cur_cust_reference --

can someone please help me.

Thanks,

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 20 January 2010 00:45] by Moderator

Report message to a moderator

Re: Customer conversion [message #439899 is a reply to message #439882] Wed, 20 January 2010 00:49 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know Oracle Apps., but - if I were you - I'd remove WHEN OTHERS exception handling section and let Oracle tell directly what has happened (if anything). If there was no error, then you'll have to debug your code, line by line (you know how it goes, right? Very slowly and carefully).

If your tool includes debugging option, turn it on. If not, use DBMS_OUTPUT.PUT_LINE in order to trace code execution.
Re: Customer conversion [message #439907 is a reply to message #439882] Wed, 20 January 2010 01:22 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What is the cursor code of
cur_cust_reference
cur_hdr_profile
cur_site_profile

As Littlefoot suggested, uncomment the dbms_output calls and check whether the header cursor is getting into or not.

By
Vamsi

[Updated on: Wed, 20 January 2010 01:23]

Report message to a moderator

icon5.gif  Re: Customer conversion [message #440013 is a reply to message #439907] Wed, 20 January 2010 10:34 Go to previous message
oraclefaqs1
Messages: 23
Registered: October 2008
Location: SC
Junior Member

Vamshi/Littlefoot,

Actually I am updating the customers here (insert_update_flag = 'U'). And I found that standard Customer Import cannot update the standard term name.

Can anyone suggest how can I INACTIVE a profile at site level.
I am trying to do that using hz_customer_profile_v2pub.update_customer_profile API by passing the status as 'I' but I get the following error

This record in table hz_customer_profiles cannot be locked as it has been updated by another user.

Thanks.

[Updated on: Wed, 20 January 2010 10:34]

Report message to a moderator

Previous Topic: DATABASE Connectivity is lost, but Application is Up
Next Topic: App's Payables
Goto Forum:
  


Current Time: Sat Dec 03 08:16:53 CST 2016

Total time taken to generate the page: 0.08250 seconds