oracle 逐层更新,Oracle EBS AR 更新客户组织层

declare

--Local variables here

i integer;

g_modulevarchar2(30) := ‘TCA_V2_API‘;

lrec_org hz_party_v2pub.organization_rec_type;

lrec_party hz_party_v2pub.party_rec_type;

l_return_statusvarchar2(1);

l_msg_countnumber;

l_msg_datavarchar2(2000);

l_party_idnumber;

l_party_number hz_parties.party_number%type;

l_profile_idnumber;

v_error_msgvarchar2(2000);

v_version_numbernumber;cursor cur_DT is

SELECTloc.location_id,

loc.object_version_number,

addr.PARTY_SITE_ID,

hc.ACCOUNT_NUMBER,

hc.CUST_ACCOUNT_ID,

party_site.PARTY_SITE_NAME,

hcp.CUST_ACCOUNT_PROFILE_ID,

hcp.CREDIT_HOLD,

hc.CREATION_DATE,

hc.CREATED_BY,

hc.ORIG_SYSTEM_REFERENCE,

hc.PARTY_ID,

hc.STATUS STATUS1,

hz.STATUS STATUS2FROMhz_cust_acct_sites_all addr,

hz_party_sites party_site,

hz_locations loc,

HZ_CUSTOMER_PROFILES hcp,

HZ_CUST_ACCOUNTS hc,

hz_parties hzWHERE 1 = 1

AND addr.party_site_id =party_site.party_site_idAND loc.location_id =party_site.location_idand hc.PARTY_ID =party_site.PARTY_IDand hc.CUST_ACCOUNT_ID =hcp.CUST_ACCOUNT_IDand hz.PARTY_ID =party_site.PARTY_IDand hz.PARTY_NUMBER = ‘10984‘;begin

--Test statements here

fnd_global.apps_initialize(0, 50738, 20003);

mo_global.init(‘AR‘);FOR rec INCUR_DT LOOPbegin

selecth.OBJECT_VERSION_NUMBERintov_version_numberfromhz_parties hwhere h.PARTY_ID =rec.PARTY_ID;end;

lrec_party.party_id :=rec.party_id;

lrec_party.status := ‘I‘;

lrec_org.party_rec :=lrec_party;

hz_party_v2pub.update_organization(p_init_msg_list=>fnd_api.G_TRUE

,p_organization_rec=>lrec_org

,p_party_object_version_number=>v_version_number

,x_profile_id=>l_profile_id

,x_return_status=>l_return_status

,x_msg_count=>l_msg_count

,x_msg_data=>l_msg_data);IF l_return_status = fnd_api.g_ret_sts_error THENl_msg_count :=fnd_msg_pub.count_msg;

l_msg_data :=substr(fnd_msg_pub.get(fnd_msg_pub.g_first,

fnd_api.g_false),1,512);

dbms_output.put_line(l_msg_data);

fnd_msg_pub.delete_msg();

RAISE fnd_api.g_exc_error;

ELSIF l_return_status= fnd_api.g_ret_sts_unexp_error THENl_msg_count :=fnd_msg_pub.count_msg;

l_msg_data :=substr(fnd_msg_pub.get(fnd_msg_pub.g_first,

fnd_api.g_false),1,512);

dbms_output.put_line(l_msg_data);

fnd_msg_pub.delete_msg();

RAISE fnd_api.g_exc_unexpected_error;END IF;IF l_return_status = fnd_api.g_ret_sts_success THENdbms_output.put_line(‘------------------Update Success-------------------‘);END IF;endloop;end;