APPS.JTF_RS_SRP_TERRITORIES_PVT SQL Statements

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

Line: 7

    create and update resource salesrep territories, from other modules.
    Its main procedures are as following:
    Create Resource Salesrep Territories
    Update Resource Salesrep Territories
    Calls to these procedures will invoke calls to table handlers (jtf_rs_srp_territories_pkg)
    which do the actual inserts, updates and deletes into tables.
    ******************************************************************************************/

   /* Package variables. */

   G_PKG_NAME         VARCHAR2(30) := 'JTF_RS_SRP_TERRITORIES_PVT';

Line: 29

    P_WH_UPDATE_DATE      	IN   JTF_RS_SRP_TERRITORIES.WH_UPDATE_DATE%TYPE,
    P_START_DATE_ACTIVE    	IN   JTF_RS_SRP_TERRITORIES.START_DATE_ACTIVE%TYPE,
    P_END_DATE_ACTIVE      	IN   JTF_RS_SRP_TERRITORIES.END_DATE_ACTIVE%TYPE,
    P_ATTRIBUTE2            	IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE2%TYPE,
    P_ATTRIBUTE3                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE3%TYPE,
    P_ATTRIBUTE4                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE4%TYPE,
    P_ATTRIBUTE5                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE5%TYPE,
    P_ATTRIBUTE6                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE6%TYPE,
    P_ATTRIBUTE7                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE7%TYPE,
    P_ATTRIBUTE8                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE8%TYPE,
    P_ATTRIBUTE9                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE9%TYPE,
    P_ATTRIBUTE1                IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE1%TYPE,
    P_ATTRIBUTE10               IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE10%TYPE,
    P_ATTRIBUTE11               IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE11%TYPE,
    P_ATTRIBUTE12               IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE12%TYPE,
    P_ATTRIBUTE13               IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE13%TYPE,
    P_ATTRIBUTE14               IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE14%TYPE,
    P_ATTRIBUTE15               IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE15%TYPE,
    P_ATTRIBUTE_CATEGORY        IN   JTF_RS_SRP_TERRITORIES.ATTRIBUTE_CATEGORY%TYPE,
    X_RETURN_STATUS        	OUT NOCOPY VARCHAR2,
    X_MSG_COUNT            	OUT NOCOPY NUMBER,
    X_MSG_DATA             	OUT NOCOPY VARCHAR2,
    X_SALESREP_TERRITORY_ID     OUT NOCOPY JTF_RS_SRP_TERRITORIES.SALESREP_TERRITORY_ID%TYPE
   )IS

   l_api_version         	CONSTANT NUMBER := 1.0;

Line: 61

   l_wh_update_date		jtf_rs_srp_territories.wh_update_date%type	:= p_wh_update_date;

Line: 91

         SELECT 'Y'
         FROM jtf_rs_srp_territories
         WHERE ROWID = l_rowid;

Line: 96

      SELECT start_date_active,
           end_date_active
      FROM jtf_rs_salesreps
      WHERE SALESREP_ID = l_salesrep_id;

Line: 102

      SELECT start_date_active,
           end_date_active
      FROM ra_territories
      WHERE TERRITORY_ID = l_territory_id;

Line: 135

          P_WH_UPDATE_DATE	=> l_wh_update_date,
          P_START_DATE_ACTIVE	=> l_start_date_active,
          P_END_DATE_ACTIVE	=> l_end_date_active,
          X_RETURN_STATUS	=> x_return_status,
          X_MSG_COUNT		=> x_msg_count,
          X_MSG_DATA		=> x_msg_data
       );

Line: 163

          P_WH_UPDATE_DATE      => l_wh_update_date,
          P_START_DATE_ACTIVE   => l_start_date_active,
          P_END_DATE_ACTIVE     => l_end_date_active,
          X_RETURN_STATUS       => x_return_status,
          X_MSG_COUNT           => x_msg_count,
          X_MSG_DATA            => x_msg_data
       );

Line: 191

          P_WH_UPDATE_DATE      => l_wh_update_date,
          P_START_DATE_ACTIVE   => l_start_date_active,
          P_END_DATE_ACTIVE     => l_end_date_active,
          X_RETURN_STATUS       => x_return_status,
          X_MSG_COUNT           => x_msg_count,
          X_MSG_DATA            => x_msg_data
       );

Line: 210

      SELECT count(*)
      INTO l_check_count
      FROM jtf_rs_srp_territories
      WHERE SALESREP_ID = l_salesrep_id
         AND TERRITORY_ID = l_territory_id;

Line: 321

      SELECT jtf_rs_srp_territories_s.nextval
      INTO l_salesrep_territory_id
      FROM dual;

Line: 326

      jtf_rs_srp_territories_pkg.insert_row(
         X_ROWID 			=> l_rowid,
      	 X_SALESREP_TERRITORY_ID	=> l_salesrep_territory_id,
 	 X_SALESREP_ID			=> l_salesrep_id,
 	 X_TERRITORY_ID			=> l_territory_id,
 	 X_STATUS			=> l_status,
 	 X_START_DATE_ACTIVE		=> l_start_date_active,
 	 X_END_DATE_ACTIVE		=> l_end_date_active,
 	 X_WH_UPDATE_DATE		=> l_wh_update_date,
 	 X_ATTRIBUTE_CATEGORY		=> l_attribute_category,
 	 X_ATTRIBUTE2			=> l_attribute2,
 	 X_ATTRIBUTE3			=> l_attribute3,
 	 X_ATTRIBUTE4			=> l_attribute4,
 	 X_ATTRIBUTE5			=> l_attribute5,
 	 X_ATTRIBUTE6			=> l_attribute6,
 	 X_ATTRIBUTE7			=> l_attribute7,
 	 X_ATTRIBUTE8			=> l_attribute8,
 	 X_ATTRIBUTE9			=> l_attribute9,
 	 X_ATTRIBUTE10			=> l_attribute10,
 	 X_ATTRIBUTE11			=> l_attribute11,
 	 X_ATTRIBUTE12			=> l_attribute12,
 	 X_ATTRIBUTE13			=> l_attribute13,
 	 X_ATTRIBUTE14			=> l_attribute14,
 	 X_ATTRIBUTE15			=> l_attribute15,
 	 X_ATTRIBUTE1			=> l_attribute1,
 	 X_CREATION_DATE		=> sysdate,
 	 X_CREATED_BY			=> jtf_resource_utl.created_by,
 	 X_LAST_UPDATE_DATE		=> sysdate,
 	 X_LAST_UPDATED_BY		=> jtf_resource_utl.updated_by,
 	 X_LAST_UPDATE_LOGIN		=> jtf_resource_utl.login_id
      );

Line: 392

          P_WH_UPDATE_DATE         => l_wh_update_date,
          P_START_DATE_ACTIVE      => l_start_date_active,
          P_END_DATE_ACTIVE        => l_end_date_active,
          P_SALESREP_TERRITORY_ID  => l_salesrep_territory_id,
          X_RETURN_STATUS          => x_return_status,
          X_MSG_COUNT              => x_msg_count,
          X_MSG_DATA               => x_msg_data
       );

Line: 421

          P_WH_UPDATE_DATE         => l_wh_update_date,
          P_START_DATE_ACTIVE      => l_start_date_active,
          P_END_DATE_ACTIVE        => l_end_date_active,
          P_SALESREP_TERRITORY_ID  => l_salesrep_territory_id,
          X_RETURN_STATUS          => x_return_status,
          X_MSG_COUNT              => x_msg_count,
          X_MSG_DATA               => x_msg_data
       );

Line: 450

          P_WH_UPDATE_DATE      	=> l_wh_update_date,
          P_START_DATE_ACTIVE   	=> l_start_date_active,
          P_END_DATE_ACTIVE     	=> l_end_date_active,
          P_SALESREP_TERRITORY_ID	=> l_salesrep_territory_id,
          X_RETURN_STATUS       	=> x_return_status,
          X_MSG_COUNT           	=> x_msg_count,
          X_MSG_DATA            	=> x_msg_data
       );

Line: 527

   PROCEDURE  update_rs_srp_territories(
      P_API_VERSION          	IN   	NUMBER,
      P_INIT_MSG_LIST        	IN   	VARCHAR2,
      P_COMMIT               	IN   	VARCHAR2,
      P_SALESREP_TERRITORY_ID	IN   	JTF_RS_SRP_TERRITORIES.SALESREP_TERRITORY_ID%TYPE,
      P_STATUS			IN   	JTF_RS_SRP_TERRITORIES.STATUS%TYPE,
      P_WH_UPDATE_DATE		IN   	JTF_RS_SRP_TERRITORIES.WH_UPDATE_DATE%TYPE,
      P_START_DATE_ACTIVE	IN   	JTF_RS_SRP_TERRITORIES.START_DATE_ACTIVE%TYPE,
      P_END_DATE_ACTIVE		IN	JTF_RS_SRP_TERRITORIES.END_DATE_ACTIVE%TYPE,
      P_OBJECT_VERSION_NUMBER	IN OUT NOCOPY 	JTF_RS_SRP_TERRITORIES.OBJECT_VERSION_NUMBER%TYPE,
      P_ATTRIBUTE2		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE2%TYPE,
      P_ATTRIBUTE3		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE3%TYPE,
      P_ATTRIBUTE4		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE4%TYPE,
      P_ATTRIBUTE5		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE5%TYPE,
      P_ATTRIBUTE6		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE6%TYPE,
      P_ATTRIBUTE7		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE7%TYPE,
      P_ATTRIBUTE8		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE8%TYPE,
      P_ATTRIBUTE9		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE9%TYPE,
      P_ATTRIBUTE1		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE1%TYPE,
      P_ATTRIBUTE10		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE10%TYPE,
      P_ATTRIBUTE11		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE11%TYPE,
      P_ATTRIBUTE12		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE12%TYPE,
      P_ATTRIBUTE13		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE13%TYPE,
      P_ATTRIBUTE14		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE14%TYPE,
      P_ATTRIBUTE15		IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE15%TYPE,
      P_ATTRIBUTE_CATEGORY	IN   	JTF_RS_SRP_TERRITORIES.ATTRIBUTE_CATEGORY%TYPE,
      X_RETURN_STATUS        	OUT NOCOPY 	VARCHAR2,
      X_MSG_COUNT            	OUT NOCOPY 	NUMBER,
      X_MSG_DATA             	OUT NOCOPY 	VARCHAR2
  )IS

   l_api_version         	CONSTANT NUMBER := 1.0;

Line: 561

   l_wh_update_date		jtf_rs_srp_territories.wh_update_date%type	:= p_wh_update_date;

Line: 595

      SELECT salesrep_id, territory_id
      FROM jtf_rs_srp_territories
      WHERE salesrep_territory_id = l_salesrep_territory_id;

Line: 599

   CURSOR c_salesrep_territory_update( l_salesrep_territory_id IN  NUMBER ) IS
      SELECT
         DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, p_start_date_active) l_start_date_active,
         DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active, p_end_date_active) l_end_date_active,
         DECODE(p_status, fnd_api.g_miss_char, status, p_status) l_status,
         DECODE(p_wh_update_date, fnd_api.g_miss_date, wh_update_date, p_wh_update_date) l_wh_update_date,
	 DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
         DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
         DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
         DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
         DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
         DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
         DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
         DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
         DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
         DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
         DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
         DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
         DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
         DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
         DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
         DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
      FROM jtf_rs_srp_territories
      WHERE salesrep_territory_id = l_salesrep_territory_id;

Line: 624

   salesrep_territory_rec      c_salesrep_territory_update%ROWTYPE;

Line: 627

      SELECT jrs.start_date_active,
             jrs.end_date_active
      FROM jtf_rs_salesreps jrs, jtf_rs_srp_territories jst
      WHERE jrs.salesrep_id = jst.salesrep_id
         AND jst.salesrep_territory_id = l_salesrep_territory_id;

Line: 634

      SELECT rt.start_date_active,
             rt.end_date_active
      FROM ra_territories rt, jtf_rs_srp_territories jst
      WHERE rt.territory_id = jst.territory_id
         AND jst.salesrep_territory_id = l_salesrep_territory_id;

Line: 641

      SAVEPOINT update_rs_srp_territories_pvt;

Line: 659

         'UPDATE_RS_SRP_TERRITORIES',
         'B',
         'C')
    THEN
       jtf_rs_srp_territories_cuhk.update_rs_srp_territories_pre(
          P_SALESREP_TERRITORY_ID    	=> l_salesrep_territory_id,
          P_STATUS              	=> l_status,
          P_WH_UPDATE_DATE      	=> l_wh_update_date,
          P_START_DATE_ACTIVE   	=> l_start_date_active,
          P_END_DATE_ACTIVE     	=> l_end_date_active,
          X_RETURN_STATUS       	=> x_return_status,
          X_MSG_COUNT           	=> x_msg_count,
          X_MSG_DATA            	=> x_msg_data
       );

Line: 686

         'UPDATE_RS_SRP_TERRITORIES',
         'B',
         'V')
    THEN
       jtf_rs_srp_territories_vuhk.update_rs_srp_territories_pre(
          P_SALESREP_TERRITORY_ID       => l_salesrep_territory_id,
          P_STATUS                      => l_status,
          P_WH_UPDATE_DATE              => l_wh_update_date,
          P_START_DATE_ACTIVE           => l_start_date_active,
          P_END_DATE_ACTIVE             => l_end_date_active,
          X_RETURN_STATUS               => x_return_status,
          X_MSG_COUNT                   => x_msg_count,
          X_MSG_DATA                    => x_msg_data
       );

Line: 713

         'UPDATE_RS_SRP_TERRITORIES',
         'B',
         'I')
    THEN
       jtf_rs_srp_territories_iuhk.update_rs_srp_territories_pre(
          P_SALESREP_TERRITORY_ID       => l_salesrep_territory_id,
          P_STATUS                      => l_status,
          P_WH_UPDATE_DATE              => l_wh_update_date,
          P_START_DATE_ACTIVE           => l_start_date_active,
          P_END_DATE_ACTIVE             => l_end_date_active,
          X_RETURN_STATUS               => x_return_status,
          X_MSG_COUNT                   => x_msg_count,
          X_MSG_DATA                    => x_msg_data
       );

Line: 751

    OPEN c_salesrep_territory_update(l_salesrep_territory_id);

Line: 752

    FETCH c_salesrep_territory_update INTO salesrep_territory_rec;

Line: 753

    IF c_salesrep_territory_update%NOTFOUND THEN
       CLOSE c_salesrep_territory_update;

Line: 880

      jtf_rs_srp_territories_pkg.update_row(
         X_SALESREP_TERRITORY_ID => p_salesrep_territory_id,
         X_SALESREP_ID           => l_salesrep_id,
         X_TERRITORY_ID          => l_territory_id,
         X_STATUS                => salesrep_territory_rec.l_status,
         X_START_DATE_ACTIVE     => salesrep_territory_rec.l_start_date_active,
         X_END_DATE_ACTIVE       => salesrep_territory_rec.l_end_date_active,
         X_WH_UPDATE_DATE        => salesrep_territory_rec.l_wh_update_date,
 	 X_OBJECT_VERSION_NUMBER => l_object_version_number,
         X_ATTRIBUTE_CATEGORY    => salesrep_territory_rec.l_attribute_category,
         X_ATTRIBUTE2            => salesrep_territory_rec.l_attribute2,
         X_ATTRIBUTE3            => salesrep_territory_rec.l_attribute3,
         X_ATTRIBUTE4            => salesrep_territory_rec.l_attribute4,
         X_ATTRIBUTE5            => salesrep_territory_rec.l_attribute5,
         X_ATTRIBUTE6            => salesrep_territory_rec.l_attribute6,
         X_ATTRIBUTE7            => salesrep_territory_rec.l_attribute7,
         X_ATTRIBUTE8            => salesrep_territory_rec.l_attribute8,
         X_ATTRIBUTE9            => salesrep_territory_rec.l_attribute9,
         X_ATTRIBUTE10           => salesrep_territory_rec.l_attribute10,
         X_ATTRIBUTE11           => salesrep_territory_rec.l_attribute11,
         X_ATTRIBUTE12           => salesrep_territory_rec.l_attribute12,
         X_ATTRIBUTE13           => salesrep_territory_rec.l_attribute13,
         X_ATTRIBUTE14           => salesrep_territory_rec.l_attribute14,
         X_ATTRIBUTE15           => salesrep_territory_rec.l_attribute15,
         X_ATTRIBUTE1            => salesrep_territory_rec.l_attribute1,
         X_LAST_UPDATE_DATE      => sysdate,
         X_LAST_UPDATED_BY       => jtf_resource_utl.updated_by,
         X_LAST_UPDATE_LOGIN     => jtf_resource_utl.login_id
      );

Line: 915

         CLOSE c_salesrep_territory_update;

Line: 935

         'UPDATE_RS_SRP_TERRITORIES',
         'A',
         'C')
    THEN
       jtf_rs_srp_territories_cuhk.update_rs_srp_territories_post(
          P_SALESREP_TERRITORY_ID       => l_salesrep_territory_id,
          P_STATUS                      => l_status,
          P_WH_UPDATE_DATE              => l_wh_update_date,
          P_START_DATE_ACTIVE           => l_start_date_active,
          P_END_DATE_ACTIVE             => l_end_date_active,
          X_RETURN_STATUS               => x_return_status,
          X_MSG_COUNT                   => x_msg_count,
          X_MSG_DATA                    => x_msg_data
       );

Line: 962

         'UPDATE_RS_SRP_TERRITORIES',
         'A',
         'V')
    THEN
       jtf_rs_srp_territories_vuhk.update_rs_srp_territories_post(
          P_SALESREP_TERRITORY_ID       => l_salesrep_territory_id,
          P_STATUS                      => l_status,
          P_WH_UPDATE_DATE              => l_wh_update_date,
          P_START_DATE_ACTIVE           => l_start_date_active,
          P_END_DATE_ACTIVE             => l_end_date_active,
          X_RETURN_STATUS               => x_return_status,
          X_MSG_COUNT                   => x_msg_count,
          X_MSG_DATA                    => x_msg_data
       );

Line: 989

         'UPDATE_RS_SRP_TERRITORIES',
         'A',
         'I')
    THEN
       jtf_rs_srp_territories_iuhk.update_rs_srp_territories_post(
          P_SALESREP_TERRITORY_ID       => l_salesrep_territory_id,
          P_STATUS                      => l_status,
          P_WH_UPDATE_DATE              => l_wh_update_date,
          P_START_DATE_ACTIVE           => l_start_date_active,
          P_END_DATE_ACTIVE             => l_end_date_active,
          X_RETURN_STATUS               => x_return_status,
          X_MSG_COUNT                   => x_msg_count,
          X_MSG_DATA                    => x_msg_data
       );

Line: 1016

         'UPDATE_RS_SRP_TERRITORIES',
         'M',
         'M')
      THEN
         IF (jtf_rs_srp_territories_cuhk.ok_to_generate_msg(
            p_salesrep_territory_id  => l_salesrep_territory_id,
            x_return_status          => x_return_status) )
         THEN

         /* Get the bind data id for the Business Object Instance */
            l_bind_data_id := jtf_usr_hks.get_bind_data_id;

Line: 1058

      ROLLBACK TO update_rs_srp_territories_pvt;

Line: 1065

      ROLLBACK TO update_rs_srp_territories_pvt;

Line: 1069

 END update_rs_srp_territories;