eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PV_TAP_BES_PKG SQL Statements

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

Line: 24


*    This  package has  been  modified  to delete  the reference of
*    hz_param_pkg.param_tab_t variable which is no longer supported
*    by TCA in R12 release.
*    This issue reported by TCA in bug# 4528865. By removing the
*    reference we are going to ignore whether the change were in any
*    Territory related transaction qualifier or not. We will insert
*    that  organization record in PV_TAP_BATCH_CHG_PARTNERS table
*    for Channel team assignment.
*
*******************************************************************/

/***********************COMMENTS - STARTED ***************************
------------------------
-- The Nullify routines
------------------------
-- PROCEDURE Nullify_location_rec
-- PROCEDURE Nullify_contact_point_rec
-- PROCEDURE Nullify_organization_rec
-- PROCEDURE Nullify_party_site_rec
-------------------------

PROCEDURE Nullify_location_rec
( p_location_rec   IN OUT NOCOPY hz_location_v2pub.location_rec_type )
IS
BEGIN
    p_location_rec.location_id              := null;

Line: 1323


PROCEDURE CTeam_Org_Update (
  p_organization_rec     IN OUT NOCOPY  HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
  p_old_organization_rec IN 	HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
  x_return_status        OUT NOCOPY  VARCHAR2,
  x_msg_count            OUT NOCOPY NUMBER,
  x_msg_data             OUT NOCOPY VARCHAR2
)
*********************************************************************************/
PROCEDURE CTeam_Org_Update (
  p_party_id		 IN          NUMBER,
  x_return_status        OUT NOCOPY  VARCHAR2,
  x_msg_count            OUT NOCOPY  NUMBER,
  x_msg_data             OUT NOCOPY  VARCHAR2
) IS

  l_partner_id           NUMBER;

Line: 1351


    SELECT partner_id
    FROM   pv_partner_profiles
    WHERE  partner_party_id = cv_partner_party_id
    AND	   status = 'A';

Line: 1358


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 1415


               PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
                  p_api_version_number    => 1.0
                  ,p_init_msg_list        => FND_API.G_FALSE
                  ,p_commit               => FND_API.G_FALSE
                  ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
                  ,x_return_status        => l_return_status
                  ,x_msg_count            => l_msg_count
                  ,x_msg_data             => l_msg_data
                  ,p_batch_chg_prtnrs_rec => l_batch_chg_prtnrs_rec);

Line: 1430


                      FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');

Line: 1445


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1458


         hz_utility_v2pub.debug('Update_Channel_Team (-)');

Line: 1462


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1474


         hz_utility_v2pub.debug('Update_Channel_Team (-)');

Line: 1478


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1491


         hz_utility_v2pub.debug('Update_Channel_Team (-)');

Line: 1493


END CTeam_Org_Update;

Line: 1523


PROCEDURE CTeam_PartySite_Update (
  p_party_site_rec       IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
  p_old_party_site_rec   IN 	 HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
  x_return_status        IN OUT NOCOPY VARCHAR2,
  x_msg_count            OUT NOCOPY NUMBER,
  x_msg_data             OUT NOCOPY VARCHAR2
  )
*********************************************************************************/
  PROCEDURE CTeam_PartySite_Update (
  p_party_site_id        IN      NUMBER,
  x_return_status        IN OUT NOCOPY VARCHAR2,
  x_msg_count            OUT NOCOPY NUMBER,
  x_msg_data             OUT NOCOPY VARCHAR2
  ) IS

  l_partner_id           NUMBER;

Line: 1550


    SELECT partner_id
    FROM   hz_party_sites hzps,
           pv_partner_profiles ppp
    WHERE  hzps.party_site_id = cv_party_site_id
    AND    hzps.status = 'A'
    AND    ppp.partner_party_id = hzps.party_id
    AND	   ppp.status = 'A';

Line: 1560


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 1567


  SAVEPOINT CTeam_PartySite_Update_pub;

Line: 1612


             PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
                p_api_version_number    => 1.0
                ,p_init_msg_list        => FND_API.G_FALSE
                ,p_commit               => FND_API.G_FALSE
                ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
                ,x_return_status        => l_return_status
                ,x_msg_count            => l_msg_count
                ,x_msg_data             => l_msg_data
                ,p_batch_chg_prtnrs_rec => l_batch_chg_prtnrs_rec);

Line: 1627


                    FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');

Line: 1641


      ROLLBACK TO CTeam_PartySite_Update_pub;

Line: 1654


         hz_utility_v2pub.debug('CTeam_PartySite_Update (-)');

Line: 1658


      ROLLBACK TO CTeam_PartySite_Update_pub;

Line: 1670


         hz_utility_v2pub.debug('CTeam_PartySite_Update (-)');

Line: 1674


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1687


         hz_utility_v2pub.debug('CTeam_PartySite_Update (-)');

Line: 1689


END CTeam_PartySite_Update;

Line: 1723


PROCEDURE CTeam_Location_Update (
  p_location_rec         IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
  p_old_location_rec     IN      HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
  x_return_status        IN OUT NOCOPY VARCHAR2,
  x_msg_count            OUT NOCOPY NUMBER,
  x_msg_data             OUT NOCOPY VARCHAR2
*********************************************************************************/
PROCEDURE CTeam_Location_Update (
  p_location_id          IN  NUMBER,
  x_return_status        IN  OUT NOCOPY VARCHAR2,
  x_msg_count            OUT NOCOPY NUMBER,
  x_msg_data             OUT NOCOPY VARCHAR2
) IS

  l_partner_id           NUMBER;

Line: 1749


     SELECT ppp.partner_id
     FROM   hz_party_sites hps,
            pv_partner_profiles ppp
 -- Fixed the isse by pointing the LOCATION_ID to partner's party.
 --    WHERE  ppp.partner_id = hps.party_id
     WHERE  ppp.partner_party_id = hps.party_id
     AND    ppp.status = 'A'
     AND    hps.location_id = cv_location_id
     AND    hps.identifying_address_flag = 'Y'
     AND    hps.status = 'A';

Line: 1762


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 1820


                  PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
                     p_api_version_number    => 1.0
                     ,p_init_msg_list        => FND_API.G_FALSE
                     ,p_commit               => FND_API.G_FALSE
                     ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
                     ,x_return_status        => l_return_status
                     ,x_msg_count            => l_msg_count
                     ,x_msg_data             => l_msg_data
                     ,p_batch_chg_prtnrs_rec => l_batch_chg_prtnrs_rec);

Line: 1835


                         FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');

Line: 1849


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1862


         hz_utility_v2pub.debug('CTeam_Location_Update (-)');

Line: 1866


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1878


         hz_utility_v2pub.debug('CTeam_Location_Update (-)');

Line: 1882


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1895


         hz_utility_v2pub.debug('CTeam_Location_Update (-)');

Line: 1897


END CTeam_Location_Update;

Line: 1925


PROCEDURE CTeam_ContPoint_Update (
   p_contact_points_rec     IN OUT NOCOPY HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE,
   p_old_contact_points_rec IN  HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE,
   p_edi_rec                IN OUT NOCOPY HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE,
   p_old_edi_rec            IN   HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE,
   p_email_rec              IN OUT NOCOPY HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE,
   p_old_email_rec          IN   HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE,
   p_phone_rec              IN OUT NOCOPY HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE,
   p_old_phone_rec          IN  HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE,
   p_telex_rec              IN OUT NOCOPY HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE,
   p_old_telex_rec          IN  HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE,
   p_web_rec                IN OUT NOCOPY HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE,
   p_old_web_rec            IN  HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE,
   x_return_status          IN OUT NOCOPY     VARCHAR2,
   x_msg_count              OUT NOCOPY     NUMBER,
   x_msg_data               OUT NOCOPY     VARCHAR2
*********************************************************************************/

   PROCEDURE CTeam_ContPoint_Update (
   p_contact_point_id       IN NUMBER,
   x_return_status          IN OUT NOCOPY     VARCHAR2,
   x_msg_count              OUT NOCOPY     NUMBER,
   x_msg_data               OUT NOCOPY     VARCHAR2
) IS

  l_partner_id           NUMBER;

Line: 1963


        SELECT /*+ index(p) */ partner_id
        FROM    HZ_CONTACT_POINTS CP,
                HZ_PARTIES PARTY,
                pv_partner_profiles p
        WHERE  CP.CONTACT_POINT_ID = cv_contact_Point_id
        AND    CP.owner_table_id = p.partner_party_id
        AND    CP.owner_table_name(+) = 'HZ_PARTIES'
        AND    CP.status(+) = 'A'
        AND    CP.primary_flag(+) = 'Y'
        AND    CP.contact_point_type(+) = 'PHONE'
        AND    CP.owner_table_id(+) = PARTY.party_id
        AND    PARTY.party_type = 'ORGANIZATION'
        AND    PARTY.status = 'A'
        AND    p.status = 'A';

Line: 1998


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 2051


		  PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners(
                     p_api_version_number    => 1.0
                     ,p_init_msg_list        => FND_API.G_FALSE
                     ,p_commit               => FND_API.G_FALSE
                     ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
                     ,x_return_status        => l_return_status
                     ,x_msg_count            => l_msg_count
                     ,x_msg_data             => l_msg_data
                     ,p_batch_chg_prtnrs_rec => l_batch_chg_prtnrs_rec);

Line: 2066


                         FND_MESSAGE.SET_TOKEN('API_NAME', 'PV_BATCH_CHG_PRTNR_PVT.Update_Batch_Chg_Partners');

Line: 2082


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2095


         hz_utility_v2pub.debug('CTeam_ContPoint_Update (-)');

Line: 2099


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2111


         hz_utility_v2pub.debug('CTeam_ContPoint_Update (-)');

Line: 2115


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2128


         hz_utility_v2pub.debug('CTeam_ContPoint_Update (-)');

Line: 2130


END CTeam_ContPoint_Update;

Line: 2156


 FUNCTION organization_update_post
 ( p_subscription_guid      in raw,
   p_event                  in out NOCOPY wf_event_t)
 RETURN VARCHAR2
 IS
   -- Declaration of local variables
   l_party_id		    NUMBER;

Line: 2179


  CTeam_Org_Update(
      p_organization_rec      => p_organization_rec,
      p_old_organization_rec  => p_old_organization_rec,
      x_return_status         => x_return_status,
      x_msg_count             => x_msg_count,
      x_msg_data              => x_msg_data );

Line: 2188


       CTeam_Org_Update(
	 p_party_id              => l_party_id,
         x_return_status         => x_return_status,
         x_msg_count             => x_msg_count,
         x_msg_data              => x_msg_data );

Line: 2203


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'Organization_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2207


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'Organization_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2210


END Organization_Update_Post;

Line: 2237


 FUNCTION partysite_update_post
 ( p_subscription_guid      in raw,
   p_event                  in out NOCOPY wf_event_t)
 RETURN VARCHAR2
 IS

   -- Declaration of local variables
   l_party_site_id          NUMBER;

Line: 2259


  CTeam_PartySite_Update (
      p_party_site_rec      => p_party_site_rec,
      p_old_party_site_rec  => p_old_party_site_rec,
      x_return_status         => x_return_status,
      x_msg_count             => x_msg_count,
      x_msg_data              => x_msg_data );

Line: 2268


       CTeam_PartySite_Update (
          p_party_site_id         => l_party_site_id,
          x_return_status         => x_return_status,
          x_msg_count             => x_msg_count,
          x_msg_data              => x_msg_data );

Line: 2283


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'partysite_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2287


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'partysite_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2290


END partysite_Update_Post;

Line: 2316


 FUNCTION location_update_post
 ( p_subscription_guid      in raw,
   p_event                  in out NOCOPY wf_event_t)
 RETURN VARCHAR2
 IS


   l_location_id            NUMBER;

Line: 2339


   CTeam_Location_Update (
      p_location_rec        => p_location_rec,
      p_old_location_rec    => p_old_location_rec,
      x_return_status       => x_return_status,
      x_msg_count           => x_msg_count,
      x_msg_data            => x_msg_data );

Line: 2346


   CTeam_Location_Update (
      p_location_id         => l_location_id,
      x_return_status       => x_return_status,
      x_msg_count           => x_msg_count,
      x_msg_data            => x_msg_data );

Line: 2361


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'location_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2365


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'location_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2368


END location_Update_Post;

Line: 2393


 FUNCTION contactpoint_update_post
 ( p_subscription_guid      in raw,
   p_event                  in out NOCOPY wf_event_t)
 RETURN VARCHAR2
 IS

   l_contact_point_id	    NUMBER;

Line: 2414


  CTeam_ContPoint_Update (
     p_contact_point_id       => l_contact_point_id,
     x_return_status          => x_return_status,
     x_msg_count              => x_msg_count,
     x_msg_data               => x_msg_data  );

Line: 2428


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'contactpoint_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2432


     WF_CORE.CONTEXT('PV_TAP_BES_PKG', 'contactpoint_Update_Post', p_event.getEventName(), p_subscription_guid);

Line: 2435


END contactpoint_Update_Post;