APPS.OE_CUSTOMER_INFO_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 58

	IF OE_GLOBALS.G_UPDATE_ON_ID THEN
		oe_debug_pub.add('OE_GLOBALS.G_UPDATE_ON_ID : TRUE');

Line: 61

		oe_debug_pub.add('OE_GLOBALS.G_UPDATE_ON_ID : FALSE');

Line: 352

3.If customer_id is sent then see if value is aslo sent and try to update the value on the id ig OE_GLOBALS.G_UPDATE_ON_ID is true
4.If customer_id could not be resolved till step 3,create the customer
5. Check for minimum required fields
6.Validate customer related fields
7. call TCA API to create the customer
*/

IF l_sold_to_cust_found
THEN

	IF l_debug_level > 0 THEN
		oe_debug_pub.add('l_sold_to_customer_rec.customer_id :'||l_sold_to_customer_rec.customer_id);

Line: 374

			oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 386

	           AND OE_GLOBALS.G_UPDATE_ON_ID
		THEN

			Update_Customer (p_customer_rec => l_sold_to_customer_rec
					 , x_return_status =>x_return_status
		                         );

Line: 473

3.If customer_id is sent then see if value is aslo sent and try to update the value on the id ig OE_GLOBALS.G_UPDATE_ON_ID is true
4.If customer_id could not be resolved till step 3,create the customer
5. Check for minimum required fields
6.Validate customer related fields
7. call TCA API to create the customer
*/


IF l_ship_to_cust_found
THEN

IF l_debug_level > 0 THEN
	oe_debug_pub.add('l_ship_to_customer_rec.customer_id :'||l_ship_to_customer_rec.customer_id);

Line: 495

		oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 509

	   AND OE_GLOBALS.G_UPDATE_ON_ID
	   THEN
			Update_Customer (p_customer_rec => l_ship_to_customer_rec
					 , x_return_status =>x_return_status
		                         );

Line: 516

					oe_debug_pub.add('Update_Customer failed  ,returning');

Line: 601

		oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 613

	   AND OE_GLOBALS.G_UPDATE_ON_ID
      THEN

		Update_Customer (p_customer_rec => l_bill_to_customer_rec
					 , x_return_status =>x_return_status
		                         );

Line: 622

				oe_debug_pub.add('Update_Customer failed  ,returning');

Line: 711

		oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 724

	   AND OE_GLOBALS.G_UPDATE_ON_ID
	THEN

			Update_Customer (p_customer_rec => l_deliver_to_customer_rec
					 , x_return_status =>x_return_status
		                         );

Line: 732

					oe_debug_pub.add('Update_Customer failed  ,returning');

Line: 2174

SELECT party_site_id,location_id
FROM hz_party_sites
WHERE party_id = p_party_id;

Line: 2277

SELECT  cust_acct_site_id
INTO x_cust_acct_site_id
FROM hz_cust_acct_sites
WHERE    party_site_id = p_party_site_id
AND cust_account_id = p_cust_account_id
AND ROWNUM =1;

Line: 2312

   SELECT loc.location_id
    INTO l_location_id
    FROM hz_cust_acct_sites      acct_site,
         hz_party_sites          party_site,
         hz_locations            loc
  WHERE acct_site.party_site_id     =  party_site.party_site_id
    AND loc.location_id             =  party_site.location_id
    AND acct_site.cust_acct_site_id =  p_cust_acct_site_id ;

Line: 2419

	      	        oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 2434

		--Update Address logic to go here
		IF IS_BOTH_ID_VAL_PASSED( p_address_rec => l_addr_rec )
	           AND OE_GLOBALS.G_UPDATE_ON_ID
             	THEN
			Update_Location (p_address_rec => l_addr_rec
	    			       , p_site_use_id   => l_addr_rec.site_use_id
		  		       , p_site_usage_code => p_address_usage
				       , x_return_status =>x_return_status
				       , x_msg_count => x_msg_count
				       , x_msg_data  => x_msg_data
		   		      );

Line: 2448

			oe_debug_pub.add('Update_Location :x_return_status'||x_return_status);

Line: 2449

			oe_debug_pub.add('Update_Location :x_msg_count'||x_msg_count);

Line: 2450

			oe_debug_pub.add('Update_Location :x_msg_data'||x_msg_data);

Line: 2488

		 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 3105

        select party_id
	into   l_party_id
	from hz_cust_accounts
	where cust_account_id = p_cust_acct_id;

Line: 3117

/* This procedure is used to update a address location.This will be called from create_address procedure if
bot ID and Value are passed for address related fields and OE_GLOBALS.G_UPDATE_ON_ID is TRUE
*/

Procedure Update_Location (  p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
                           , p_site_use_id IN NUMBER
			   , p_site_usage_code IN VARCHAR2
			   , x_return_status OUT NOCOPY VARCHAR2
			   , x_msg_count OUT NOCOPY NUMBER
			   , x_msg_data  OUT NOCOPY VARCHAR2
			   ) IS
l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;

Line: 3152

	 HZ_LOCATION_V2PUB.UPDATE_LOCATION (
		p_init_msg_list  => FND_API.G_TRUE,
		p_location_rec  => l_location_rec,
		p_object_version_number => x_ver_number,
		x_return_status   =>x_return_status,
		x_msg_count =>x_msg_count,
		x_msg_data  =>x_msg_data
		);

Line: 3173

            ,   'Update_Location'
            );

Line: 3177

END Update_Location;

Line: 3180

which will be passed to the TCA APIS in case of updates*/

FUNCTION Get_obj_version_number( p_location_id IN NUMBER DEFAULT NULL
				,p_cust_account_id IN NUMBER DEFAULT NULL
				,p_party_id        IN NUMBER DEFAULT NULL ) RETURN NUMBER
IS
l_version_num NUMBER;

Line: 3190

		select object_version_number
		into l_version_num
		from hz_locations
		where location_id = p_location_id;

Line: 3197

		SELECT OBJECT_VERSION_NUMBER
		into l_version_num
		FROM HZ_CUST_ACCOUNTS
		WHERE CUST_ACCOUNT_ID = p_cust_account_id;

Line: 3204

		SELECT OBJECT_VERSION_NUMBER
		into l_version_num
		FROM HZ_PARTIES
		WHERE party_id = p_party_id;

Line: 3225

    SELECT loc.location_id
    INTO l_location_id
    FROM hz_cust_site_uses   site_uses,
         hz_cust_acct_sites  acct_site,
         hz_party_sites          party_site,
         hz_locations            loc
    WHERE site_uses.cust_acct_site_id =  acct_site.cust_acct_site_id
	AND acct_site.party_site_id     =  party_site.party_site_id
	AND loc.location_id             =  party_site.location_id
	AND site_uses.site_use_code     =  p_site_usage_code
	AND site_uses.site_use_id       =  p_site_use_id;

Line: 3256

for customer fields and OE_GLOBALS.G_UPDATE_ON_ID is TRUE. This API can update Customer_name,
Account_Description,Account_number,Email contact point,Phone contact point*/

Procedure Update_Customer ( p_customer_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
	  		   , x_return_status OUT NOCOPY VARCHAR2 ) IS
x_version_number  NUMBER;

Line: 3280

		oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Customer ');

Line: 3300

		hz_party_v2pub.update_organization (
				p_init_msg_list  =>  FND_API.G_TRUE,
			        p_organization_rec => l_organization_rec,
				p_party_object_version_number  => x_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 );

Line: 3319

		     hz_party_v2pub.update_person (
			    p_init_msg_list   =>  FND_API.G_TRUE,
			    p_person_rec    => l_person_rec,
			    p_party_object_version_number  => x_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 );

Line: 3391

	       HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT (
		p_init_msg_list  => FND_API.G_TRUE,
		p_cust_account_rec  => l_cust_acct_rec,
		p_object_version_number => x_version_number,
		x_return_status  => x_return_status,
		x_msg_count      =>x_msg_count,
		x_msg_data       =>x_msg_data
		);

Line: 3408

		oe_debug_pub.add('Update_Customer :x_return_status:'||x_return_status);

Line: 3409

		oe_debug_pub.add('Update_Customer:x_msg_count:'||x_msg_count);

Line: 3410

		oe_debug_pub.add('Update_Customer :x_msg_data:'||x_msg_data);

Line: 3411

		oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Customer ');

Line: 3417

END Update_Customer;

Line: 3469

		     oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);

Line: 3482

		--Update Contact logic to go here
		IF IS_BOTH_ID_VAL_PASSED( p_contact_rec => l_contact_rec )
	           AND OE_GLOBALS.G_UPDATE_ON_ID
             	THEN
			Update_Contact (p_contact_rec => l_contact_rec
			   , x_return_status =>x_return_status
			   , x_msg_count => x_msg_count
			   , x_msg_data  => x_msg_data
			   );

Line: 3494

			oe_debug_pub.add('Update_Location :x_return_status'||x_return_status);

Line: 3495

			oe_debug_pub.add('Update_Location :x_msg_count'||x_msg_count);

Line: 3496

			oe_debug_pub.add('Update_Location :x_msg_data'||x_msg_data);

Line: 3740

        SELECT 'Y' INTO l_exists
        FROM HZ_CUST_ACCT_RELATE
        WHERE CUST_ACCOUNT_ID = p_customer_id
        AND RELATED_CUST_ACCOUNT_ID = p_rel_customer_id
	AND STATUS='A';

Line: 3776

		select 'Y'
		into l_exists
		from HZ_PARTIES
		Where party_id = p_party_id;

Line: 3802

    Select p_contact_record.person_last_name
           || DECODE(p_contact_record.person_first_name,  NULL, NULL, ', '
	   || p_contact_record.PERSON_FIRST_NAME)
	   || DECODE(p_contact_record.Person_Name_Suffix, NULL, NULL, ', '
	   ||p_contact_record.Person_Name_Suffix)
    Into l_contact_name
    From Dual;

Line: 3831

    SELECT site_uses.site_use_id
    INTO   x_site_use_id
    FROM hz_cust_acct_sites  acct_site,
         hz_cust_site_uses   site_uses,
         hz_party_sites          party_site,
         hz_locations            loc
    WHERE site_uses.cust_acct_site_id =  acct_site.cust_acct_site_id
	AND acct_site.party_site_id     =  party_site.party_site_id
	AND loc.location_id             =  party_site.location_id
	AND site_uses.site_use_code     =  p_site_usage
	AND acct_site.CUST_ACCT_SITE_ID = p_cust_acct_site_id
	AND  site_uses.STATUS = 'A';

Line: 3855

	select party_type
	into l_party_type
	from hz_parties
	where party_id = p_party_id;

Line: 3867

/* Updates the contact information like person name,title,email,phone  .Called from create_contact procedure
if both ID and value are passed in contact record and OE_GLOBALS.G_UPDATE_ON_ID is TRUE*/

PROCEDURE Update_Contact  ( p_contact_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
			   , x_return_status  OUT NOCOPY VARCHAR2
			   , x_msg_count      OUT NOCOPY NUMBER
			   , x_msg_data       OUT NOCOPY VARCHAR2
			   )
IS
l_party_id NUMBER;

Line: 3886

		oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Contact ');

Line: 3890

 SELECT party.party_id,REL_PARTY.party_id
 into l_party_id , l_rel_party_id
 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
 HZ_PARTIES PARTY,
 HZ_CUST_ACCOUNTS ACCT,
 HZ_RELATIONSHIPS REL,
 HZ_ORG_CONTACTS ORG_CONT,
 HZ_PARTIES REL_PARTY
 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
 AND REL.SUBJECT_ID = PARTY.PARTY_ID
 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
 AND REL.OBJECT_ID = ACCT.PARTY_ID
 AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID
 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_rec.contact_id;

Line: 3926

		hz_party_v2pub.update_person (
			    p_init_msg_list   =>  FND_API.G_TRUE,
			    p_person_rec    => l_person_rec,
			    p_party_object_version_number  => x_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 );

Line: 3992

		oe_debug_pub.add('Update_Contact :x_return_status:'||x_return_status);

Line: 3993

		oe_debug_pub.add('Update_Contact :x_msg_count:'||x_msg_count);

Line: 3994

		oe_debug_pub.add('Update_Contact :x_msg_data:'||x_msg_data);

Line: 3995

		oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Contact ');

Line: 4006

            ,   'Update_Contact'
            );

Line: 4011

  		oe_debug_pub.add('Update_Contact Other Errors :'||SQLERRM);

Line: 4015

END Update_Contact;