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: 1497


 	 --      API name  : CResource_Org_Update
 	 --      Type      : Private
 	 --      Function  : This procedure is used to update the Resource details and Group details when
 	 --                  any of the Organization is updated
 	 --      Pre-reqs  :
 	 --
 	 --      Paramaeters     :
 	 --      IN              :
 	 --                 p_organization_rec     IN OUT  HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
 	 --                 p_old_organization_rec IN    HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
 	 --      OUT
 	 --                 x_return_status        OUT  VARCHAR2,
 	 --                 x_msg_count            OUT NOCOPY NUMBER,
 	 --                 x_msg_data             OUT NOCOPY VARCHAR2
 	 --
 	 --      Version :
 	 --                 Initial version         1.0
 	 --
 	 --      Notes:
 	 --
 	 --
 	 -- End of Comments
 	 PROCEDURE CResource_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: 1539


 	     SELECT ppp.partner_id,
 	            jrr.resource_id,
 	            jrr.category,
 	            jrr.resource_number,
 	            jrr.object_version_number,
 	            jrr.source_name,
 	            hzp.party_name,
                    hzpr.party_name party_name_r
             FROM   pv_partner_profiles ppp,
 	            jtf_rs_resource_extns jrr,
 	      	    hz_parties hzp,
                    hz_parties hzpr,
                    hz_party_relationship_v hzprv
             WHERE  ppp.partner_party_id = cv_partner_party_id
 	     AND    ppp.partner_id = hzp.party_id
 	     AND    ppp.status = 'A'
 	     AND    jrr.source_id = ppp.partner_id
             AND    hzpr.party_id = hzprv.subject_id
             AND    hzprv.party_id = hzp.party_id;

Line: 1560


 	     SELECT jrg.group_id,
 	            jrg.group_name,
 	            jrg.group_number,
 	            jrg.object_version_number
 	     from jtf_rs_groups_vl jrg,
 	          jtf_rs_group_members jrm
 	     where jrg.group_name = cv_group_name
 	     and jrm.resource_id = cv_resource_id
 	     and jrm.group_id = jrg.group_id;

Line: 1572


 	     l_module VARCHAR2(50) := 'pv.plsql.PV_TAP_BES_PKG.UpdateResource';

Line: 1592


 	    JTF_RS_RESOURCE_PUB.Update_Resource(
 	        p_api_version        => 1.0
 	       ,p_init_msg_list      => FND_API.g_true
 	       ,p_resource_id        => l_cust_partner_rec.resource_id
 	       ,p_resource_number    => l_cust_partner_rec.resource_number
 	       ,p_resource_name      => l_cust_partner_rec.PARTY_NAME        -- resource_name
 	       ,p_source_name        => l_cust_partner_rec.PARTY_NAME          -- source_name
 	     --  ,p_source_org_id            => l_admin_complete_rec.source_org_id        -- source_org_id
 	     --  ,p_source_first_name  => l_admin_complete_rec.source_first_name
 	     --  ,p_source_last_name   => l_admin_complete_rec.source_last_name
 	    --   ,p_source_middle_name => l_admin_complete_rec.source_middle_name
 	       ,p_object_version_num  => l_cust_partner_rec.object_version_number
 	       ,x_return_status      => x_return_status
 	       ,x_msg_count          => x_msg_count
 	       ,x_msg_data           => x_msg_data

 	       );

Line: 1633


 	         jtf_rs_groups_pub.update_resource_group
 	           (P_API_VERSION         => 1.0,
 	            P_GROUP_ID            => l_group_rec.group_id,
 	            P_GROUP_NUMBER        => l_group_rec.group_number,
 	            P_GROUP_NAME          => SUBSTR(l_cust_partner_rec.PARTY_NAME_R,1,44)||'('||p_party_id||')',
 	            P_GROUP_DESC          => SUBSTR(l_cust_partner_rec.PARTY_NAME,1,80),
 	            P_OBJECT_VERSION_NUM  => l_group_rec.object_version_number,
 	            X_RETURN_STATUS       => x_return_status,
 	            X_MSG_COUNT           => x_msg_count,
 	            X_MSG_DATA            => x_msg_data
 	         );

Line: 1662


 	 End CResource_Org_Update;

Line: 1694


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: 1721


    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: 1731


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 1738


  SAVEPOINT CTeam_PartySite_Update_pub;

Line: 1783


             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: 1798


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

Line: 1812


      ROLLBACK TO CTeam_PartySite_Update_pub;

Line: 1825


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

Line: 1829


      ROLLBACK TO CTeam_PartySite_Update_pub;

Line: 1841


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

Line: 1845


      ROLLBACK TO Update_Channel_Team_pub;

Line: 1858


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

Line: 1860


END CTeam_PartySite_Update;

Line: 1894


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: 1920


     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: 1933


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 1991


                  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: 2006


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

Line: 2020


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2033


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

Line: 2037


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2049


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

Line: 2053


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2066


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

Line: 2068


END CTeam_Location_Update;

Line: 2096


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: 2134


        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: 2169


    SELECT processed_flag, object_version_number
    FROM   pv_tap_batch_chg_partners
    WHERE  partner_id = cv_partner_id;

Line: 2222


		  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: 2237


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

Line: 2253


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2266


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

Line: 2270


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2282


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

Line: 2286


      ROLLBACK TO Update_Channel_Team_pub;

Line: 2299


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

Line: 2301


END CTeam_ContPoint_Update;

Line: 2327


 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: 2350


  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: 2359


       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: 2365


       CResource_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: 2380


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

Line: 2384


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

Line: 2387


END Organization_Update_Post;

Line: 2414


 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: 2436


  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: 2445


       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: 2460


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

Line: 2464


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

Line: 2467


END partysite_Update_Post;

Line: 2493


 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: 2516


   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: 2523


   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: 2538


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

Line: 2542


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

Line: 2545


END location_Update_Post;

Line: 2570


 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: 2591


  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: 2605


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

Line: 2609


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

Line: 2612


END contactpoint_Update_Post;