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.CAC_NOTES_PVT SQL Statements

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

Line: 59


   SELECT 'x' INTO l_dummy
   FROM fnd_lookup_values
   WHERE lookup_code = p_note_type
   AND   lookup_type = 'JTF_NOTE_TYPE'
   AND   language    = USERENV('LANG');

Line: 99


  l_select_id        VARCHAR2(200);

Line: 107


  IS SELECT select_id
     ,      from_table
     ,      where_clause
     FROM   jtf_objects_vl a
     ,      jtf_object_usages b
     WHERE  a.object_code = p_object_type_code
     AND    a.object_code = b.object_code
     AND    b.object_user_code = 'NOTES';

Line: 118


  FETCH cur_object INTO l_select_id,l_tablename,l_where_clause ;

Line: 123


    v_create_string := 'SELECT COUNT(*)  FROM '||l_tablename||
                       ' WHERE '||l_select_id||' = :object_type_id ';

Line: 126


    v_create_string := 'SELECT COUNT(*)  FROM '||l_tablename||
                       ' WHERE '||l_where_clause||
                       ' AND '||l_select_id||' = :object_type_id ';

Line: 236


, p_last_update_date   IN            DATE     := NULL
, p_last_updated_by    IN            NUMBER   := NULL
, p_last_update_login  IN            NUMBER   := NULL
, x_jtf_note_id           OUT NOCOPY NUMBER
, x_return_status         OUT NOCOPY VARCHAR2
, x_msg_count             OUT NOCOPY NUMBER
, x_msg_data              OUT NOCOPY VARCHAR2
)
IS
  l_api_name           CONSTANT VARCHAR2(30)    := 'Create_note';

Line: 269


  IS  SELECT JTF_NOTES_S.NEXTVAL
      FROM DUAL;

Line: 302


    , p_last_update_date        => p_last_update_date
    , p_last_updated_by         => p_last_updated_by
    , p_creation_date           => p_creation_date
    , p_created_by              => p_created_by
    , p_last_update_login       => p_last_update_login
    , p_attribute1              => p_attribute1
    , p_attribute2              => p_attribute2
    , p_attribute3              => p_attribute3
    , p_attribute4              => p_attribute4
    , p_attribute5              => p_attribute5
    , p_attribute6              => p_attribute6
    , p_attribute7              => p_attribute7
    , p_attribute8              => p_attribute8
    , p_attribute9              => p_attribute9
    , p_attribute10             => p_attribute10
    , p_attribute11             => p_attribute11
    , p_attribute12             => p_attribute12
    , p_attribute13             => p_attribute13
    , p_attribute14             => p_attribute14
    , p_attribute15             => p_attribute15
    , p_note_type               => p_note_type
    , x_return_status           => l_return_status
    );

Line: 353


    , p_last_update_date        => p_last_update_date
    , p_last_updated_by         => p_last_updated_by
    , p_creation_date           => p_creation_date
    , p_created_by              => p_created_by
    , p_last_update_login       => p_last_update_login
    , p_attribute1              => p_attribute1
    , p_attribute2              => p_attribute2
    , p_attribute3              => p_attribute3
    , p_attribute4              => p_attribute4
    , p_attribute5              => p_attribute5
    , p_attribute6              => p_attribute6
    , p_attribute7              => p_attribute7
    , p_attribute8              => p_attribute8
    , p_attribute9              => p_attribute9
    , p_attribute10             => p_attribute10
    , p_attribute11             => p_attribute11
    , p_attribute12             => p_attribute12
    , p_attribute13             => p_attribute13
    , p_attribute14             => p_attribute14
    , p_attribute15             => p_attribute15
    , p_note_type               => p_note_type
    , x_return_status           => l_return_status
    );

Line: 462


  insert into JTF_NOTES_B (
    SOURCE_OBJECT_CODE,
    NOTE_STATUS,
    ENTERED_BY,
    ENTERED_DATE,
    NOTE_TYPE,
    JTF_NOTE_ID,
    SOURCE_OBJECT_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	ATTRIBUTE11,
	ATTRIBUTE12,
	ATTRIBUTE13,
	ATTRIBUTE14,
	ATTRIBUTE15,
	PARENT_NOTE_ID
  ) values (
    p_SOURCE_OBJECT_CODE,
    L_NOTE_STATUS,
    NVL(p_entered_by,fnd_global.user_id),
    NVL(p_entered_date,l_current_date),
    P_NOTE_TYPE,
    L_JTF_NOTE_ID,
    P_SOURCE_OBJECT_ID,
    NVL(p_creation_date,l_current_date),
    NVL(p_created_by,fnd_global.user_id),
    NVL(p_last_update_date,l_current_date),
    NVL(p_last_updated_by,fnd_global.user_id),
    NVL(p_last_update_login,fnd_global.login_id),
	P_ATTRIBUTE1,
	P_ATTRIBUTE2,
	P_ATTRIBUTE3,
	P_ATTRIBUTE4,
	P_ATTRIBUTE5,
	P_ATTRIBUTE6,
	P_ATTRIBUTE7,
	P_ATTRIBUTE8,
	P_ATTRIBUTE9,
	P_ATTRIBUTE10,
	P_ATTRIBUTE11,
	P_ATTRIBUTE12,
	P_ATTRIBUTE13,
	P_ATTRIBUTE14,
	P_ATTRIBUTE15,
	P_PARENT_NOTE_ID
  );

Line: 522


  insert into JTF_NOTES_TL (
    JTF_NOTE_ID,
    NOTES,
    NOTES_DETAIL,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    L_JTF_NOTE_ID,
    L_NOTES,
    P_NOTES_DETAIL,
    NVL(p_creation_date,l_current_date),
    NVL(p_created_by,fnd_global.user_id),
    NVL(p_last_update_date,l_current_date),
    NVL(p_last_updated_by,fnd_global.user_id),
    NVL(p_last_update_login,fnd_global.login_id),
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B');

Line: 547


   INSERT INTO JTF_NOTE_CONTEXTS
    ( NOTE_CONTEXT_ID,
	 JTF_NOTE_ID,
	 NOTE_CONTEXT_TYPE_ID,
	 NOTE_CONTEXT_TYPE,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 LAST_UPDATE_LOGIN)
  VALUES (
     jtf_notes_s.nextval,
	L_jtf_note_id,
	P_source_object_id,
	P_source_object_code,
    NVL(p_creation_date,l_current_date),
    NVL(p_created_by,fnd_global.user_id),
    NVL(p_last_update_date,l_current_date),
    NVL(p_last_updated_by,fnd_global.user_id),
    NVL(p_last_update_login,fnd_global.login_id)
	);

Line: 591


     , p_last_update_date        => l_current_date
     , p_last_updated_by         => FND_GLOBAL.USER_ID
     , p_creation_date           => l_current_date
     , p_created_by              => FND_GLOBAL.USER_ID
     , p_last_update_login       => FND_GLOBAL.LOGIN_ID
     , p_attribute1              => p_attribute1
     , p_attribute2              => p_attribute2
     , p_attribute3              => p_attribute3
     , p_attribute4              => p_attribute4
     , p_attribute5              => p_attribute5
     , p_attribute6              => p_attribute6
     , p_attribute7              => p_attribute7
     , p_attribute8              => p_attribute8
     , p_attribute9              => p_attribute9
     , p_attribute10             => p_attribute10
     , p_attribute11             => p_attribute11
     , p_attribute12             => p_attribute12
     , p_attribute13             => p_attribute13
     , p_attribute14             => p_attribute14
     , p_attribute15             => p_attribute15
     , p_note_type               => p_note_type
     , x_return_status           => l_return_status
     , p_jtf_note_id             => l_jtf_note_id
     );

Line: 644


     , p_last_update_date        => l_current_date
     , p_last_updated_by         => FND_GLOBAL.USER_ID
     , p_creation_date           => l_current_date
     , p_created_by              => FND_GLOBAL.USER_ID
     , p_last_update_login       => FND_GLOBAL.LOGIN_ID
     , p_attribute1              => p_attribute1
     , p_attribute2              => p_attribute2
     , p_attribute3              => p_attribute3
     , p_attribute4              => p_attribute4
     , p_attribute5              => p_attribute5
     , p_attribute6              => p_attribute6
     , p_attribute7              => p_attribute7
     , p_attribute8              => p_attribute8
     , p_attribute9              => p_attribute9
     , p_attribute10             => p_attribute10
     , p_attribute11             => p_attribute11
     , p_attribute12             => p_attribute12
     , p_attribute13             => p_attribute13
     , p_attribute14             => p_attribute14
     , p_attribute15             => p_attribute15
     , p_note_type               => p_note_type
     , x_return_status           => l_return_status
     , p_jtf_note_id             => l_jtf_note_id
     );

Line: 693


       , p_last_update_date   => l_current_date
       , p_last_updated_by    => FND_GLOBAL.USER_ID
       , p_creation_date      => l_current_date
       )
    THEN
      l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;

Line: 774


PROCEDURE update_note
------------------------------------------------------------------------------
-- Update_note
--   Updates a note record in the JTF_NOTES_B, JTF_NOTES_TL tables
------------------------------------------------------------------------------
( p_jtf_note_id           IN            NUMBER
, p_notes                 IN            VARCHAR2 := NULL
, p_notes_detail          IN            CLOB     := NULL
, p_note_status           IN            VARCHAR2 := NULL
, p_note_type             IN            VARCHAR2 := NULL
, p_attribute1            IN            VARCHAR2 := NULL
, p_attribute2            IN            VARCHAR2 := NULL
, p_attribute3            IN            VARCHAR2 := NULL
, p_attribute4            IN            VARCHAR2 := NULL
, p_attribute5            IN            VARCHAR2 := NULL
, p_attribute6            IN            VARCHAR2 := NULL
, p_attribute7            IN            VARCHAR2 := NULL
, p_attribute8            IN            VARCHAR2 := NULL
, p_attribute9            IN            VARCHAR2 := NULL
, p_attribute10           IN            VARCHAR2 := NULL
, p_attribute11           IN            VARCHAR2 := NULL
, p_attribute12           IN            VARCHAR2 := NULL
, p_attribute13           IN            VARCHAR2 := NULL
, p_attribute14           IN            VARCHAR2 := NULL
, p_attribute15           IN            VARCHAR2 := NULL
, p_parent_note_id        IN            NUMBER   := NULL
, p_last_update_date      IN            DATE     := NULL
, p_last_updated_by       IN            NUMBER   := NULL
, p_last_update_login     IN            NUMBER   := NULL
, x_return_status            OUT NOCOPY VARCHAR2
, x_msg_count                OUT NOCOPY NUMBER
, x_msg_data                 OUT NOCOPY VARCHAR2
)
IS
  l_api_name           CONSTANT VARCHAR2(30)    := 'Update_note';

Line: 826


  SAVEPOINT update_note_pvt;

Line: 836


                              , 'Update Note'
                              , 'B'
                              , 'C'
                              )
  THEN
    jtf_notes_cuhk.update_note_pre
                  ( p_api_version     => l_api_version
                  , x_msg_count       => l_msg_count
                  , x_msg_data        => l_msg_data
                  , p_jtf_note_id     => p_jtf_note_id
                  , p_entered_by      => FND_GLOBAL.USER_ID
                  , p_last_updated_by => p_last_updated_by
                  , p_notes           => p_notes
                  , p_note_status     => p_note_status
                  , p_note_type       => p_note_type
                  , x_return_status   => l_return_status
                  );

Line: 863


                              , 'Update Note'
                              , 'B'
                              , 'V'
                              )
  THEN
    jtf_notes_vuhk.update_note_pre
                  ( p_api_version     => l_api_version
                  , x_msg_count       => l_msg_count
                  , x_msg_data        => l_msg_data
                  , p_jtf_note_id     => p_jtf_note_id
                  , p_entered_by      => FND_GLOBAL.USER_ID
                  , p_last_updated_by => p_last_updated_by
                  , p_notes           => p_notes
                  , p_note_status     => p_note_status
                  , p_note_type       => p_note_type
                  , x_return_status   => l_return_status
                  );

Line: 914


  UPDATE JTF_NOTES_B
  SET last_updated_by   = NVL(p_last_updated_by,fnd_global.user_id)
  ,   last_update_date  = NVL(p_last_update_date,l_current_date)
  ,   last_update_login = NVL(p_last_update_login,fnd_global.login_id)
  ,   note_status       = NVL(l_note_status,note_status)
  ,   note_type         = l_note_type
  ,   attribute1        = p_attribute1
  ,   attribute2        = p_attribute2
  ,   attribute3        = p_attribute3
  ,   attribute4        = p_attribute4
  ,   attribute5        = p_attribute5
  ,   attribute6        = p_attribute6
  ,   attribute7        = p_attribute7
  ,   attribute8        = p_attribute8
  ,   attribute9        = p_attribute9
  ,   attribute10       = p_attribute10
  ,   attribute11       = p_attribute11
  ,   attribute12       = p_attribute12
  ,   attribute13       = p_attribute13
  ,   attribute14       = p_attribute14
  ,   attribute15       = p_attribute15
  ,   parent_note_id    = p_parent_note_id
  WHERE jtf_note_id = p_jtf_note_id;

Line: 940


  	  UPDATE JTF_NOTES_TL
  	  SET  NOTES             = NVL(l_notes,NOTES)
  	  ,    LAST_UPDATE_DATE  = NVL(p_last_update_date,l_current_date)
  	  ,    LAST_UPDATED_BY   = NVL(p_last_updated_by,fnd_global.user_id)
  	  ,    LAST_UPDATE_LOGIN = NVL(p_last_update_login,fnd_global.login_id)
  	  ,    SOURCE_LANG       = USERENV('LANG')
  	  WHERE JTF_NOTE_ID = p_jtf_note_id;

Line: 948


  	  UPDATE JTF_NOTES_TL
  	  SET  NOTES             = NVL(l_notes,NOTES)
  	  ,    NOTES_DETAIL      = p_notes_detail
  	  ,    LAST_UPDATE_DATE  = NVL(p_last_update_date,l_current_date)
  	  ,    LAST_UPDATED_BY   = NVL(p_last_updated_by,fnd_global.user_id)
  	  ,    LAST_UPDATE_LOGIN = NVL(p_last_update_login,fnd_global.login_id)
  	  ,    SOURCE_LANG       = USERENV('LANG')
  	  WHERE JTF_NOTE_ID = p_jtf_note_id;

Line: 959


                              , 'Update Note'
                              , 'A'
                              , 'C'
                              )
  THEN
    jtf_notes_cuhk.update_note_post
    ( p_api_version     => l_api_version
    , x_msg_count       => l_msg_count
    , x_msg_data        => l_msg_data
    , p_jtf_note_id     => p_jtf_note_id
    , p_entered_by      => FND_GLOBAL.USER_ID
    , p_last_updated_by => p_last_updated_by
    , p_notes           => l_notes
    , p_note_status     => l_note_status
    , p_note_type       => l_note_type
    , x_return_status   => l_return_status
    );

Line: 986


                              , 'Update Note'
                              , 'A'
                              , 'V'
                              )
  THEN
    jtf_notes_vuhk.update_note_post
    ( p_api_version     => l_api_version
    , x_msg_count       => l_msg_count
    , x_msg_data        => l_msg_data
    , p_jtf_note_id     => p_jtf_note_id
    , p_entered_by      => FND_GLOBAL.USER_ID
    , p_last_updated_by => p_last_updated_by
    , p_notes           => l_notes
    , p_note_status     => l_note_status
    , p_note_type       => l_note_type
    , x_return_status   => l_return_status
    );

Line: 1025


                     , p_last_updated_by => p_last_updated_by
                     , p_notes           => l_notes
                     , p_note_status     => l_note_status
                     , p_note_type       => l_note_type
                     , x_return_status   => l_return_status
                     )
    THEN
      l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;

Line: 1056


  SELECT source_object_code
  ,      source_object_id
  INTO l_source_object_code
  ,    l_source_object_id
  FROM jtf_notes_b
  WHERE jtf_note_id = p_jtf_note_id;

Line: 1063


  JTF_NOTES_EVENTS_PVT.RaiseUpdateNote
  ( p_NoteID            => p_jtf_note_id
  , p_SourceObjectCode  => l_source_object_code
  , p_SourceObjectID    => l_source_object_id
  );

Line: 1079


     ROLLBACK TO update_note_pvt;

Line: 1088


      ROLLBACK TO update_note_pvt;

Line: 1097


     ROLLBACK TO update_note_pvt;

Line: 1113


END update_note;

Line: 1115


PROCEDURE delete_note
------------------------------------------------------------------------------
-- delete_note
--   deletes a note record in the JTF_NOTES_B, JTF_NOTES_TL tables
------------------------------------------------------------------------------
( p_jtf_note_id           IN            NUMBER
, x_return_status            OUT NOCOPY VARCHAR2
, x_msg_count                OUT NOCOPY NUMBER
, x_msg_data                 OUT NOCOPY VARCHAR2
)
IS
  l_api_name       CONSTANT VARCHAR2(30)    := 'delete_note';

Line: 1133


  SAVEPOINT delete_note_pvt;

Line: 1143


  SELECT source_object_code
  ,      source_object_id
  INTO l_source_object_code
  ,    l_source_object_id
  FROM jtf_notes_b
  WHERE jtf_note_id = p_jtf_note_id;

Line: 1153


  DELETE FROM jtf_note_contexts WHERE jtf_note_id = p_jtf_note_id;

Line: 1154


  DELETE FROM jtf_notes_tl      WHERE jtf_note_id = p_jtf_note_id;

Line: 1155


  DELETE FROM jtf_notes_b       WHERE jtf_note_id = p_jtf_note_id;

Line: 1158


  JTF_NOTES_EVENTS_PVT.RaiseDeleteNote
  ( p_NoteID            => p_jtf_note_id
  , p_SourceObjectCode  => l_source_object_code
  , p_SourceObjectID    => l_source_object_id
  );

Line: 1174


     ROLLBACK TO delete_note_pvt;

Line: 1197


END delete_note;

Line: 1210


, p_last_update_date     IN            DATE     := NULL
, p_last_updated_by      IN            NUMBER   := NULL
, p_last_update_login    IN            NUMBER   := NULL
, x_note_context_id         OUT NOCOPY NUMBER
, x_return_status           OUT NOCOPY VARCHAR2
, x_msg_count               OUT NOCOPY NUMBER
, x_msg_data                OUT NOCOPY VARCHAR2
)
IS
  -- Cursor that will check for duplicates
  CURSOR c_duplicate
  ( b_jtf_note_id           IN  NUMBER
  , b_note_context_type     IN  VARCHAR2
  , b_note_context_type_id  IN  NUMBER
  )IS SELECT note_context_id
      FROM jtf_note_contexts
      WHERE jtf_note_id          = b_jtf_note_id
      AND   note_context_type    = b_note_context_type
      AND   note_context_type_id = b_note_context_type_id;

Line: 1265


    INSERT INTO JTF_NOTE_CONTEXTS
    (
	 NOTE_CONTEXT_ID,
	 JTF_NOTE_ID,
	 NOTE_CONTEXT_TYPE_ID,
	 NOTE_CONTEXT_TYPE,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 LAST_UPDATE_LOGIN
	)
    VALUES
	(
     NVL(p_note_context_id,jtf_notes_s.nextval),
	 p_jtf_note_id,
	 p_note_context_type_id,
	 p_note_context_type,
     NVL(p_creation_date,l_current_date),
     NVL(p_created_by,fnd_global.user_id),
     NVL(p_last_update_date,l_current_date),
     NVL(p_last_updated_by,fnd_global.user_id),
     NVL(p_last_update_login,fnd_global.login_id)
	)
    RETURNING note_context_id INTO x_note_context_id;

Line: 1346


PROCEDURE update_note_context
------------------------------------------------------------------------------
-- update_note_context
--   updates a record in the JTF_NOTE_CONTEXTS table.
------------------------------------------------------------------------------
( p_note_context_id       IN            NUMBER
, p_jtf_note_id           IN            NUMBER   := NULL
, p_note_context_type     IN            VARCHAR2 := NULL
, p_note_context_type_id  IN            NUMBER   := NULL
, p_last_update_date      IN            DATE     := NULL
, p_last_updated_by       IN            NUMBER   := NULL
, p_last_update_login     IN            NUMBER   := NULL
, x_return_status            OUT NOCOPY VARCHAR2
, x_msg_count                OUT NOCOPY NUMBER
, x_msg_data                 OUT NOCOPY VARCHAR2
)
IS
  l_api_name      CONSTANT VARCHAR2(200) := 'update_note_context';

Line: 1370


  SAVEPOINT update_note_context_pvt;

Line: 1387


  UPDATE JTF_NOTE_CONTEXTS
  SET
	 JTF_NOTE_ID          = NVL(p_jtf_note_id,JTF_NOTE_ID),
	 NOTE_CONTEXT_TYPE_ID = NVL(p_note_context_type_id,NOTE_CONTEXT_TYPE_ID),
	 NOTE_CONTEXT_TYPE    = NVL(p_note_context_type,NOTE_CONTEXT_TYPE),
	 LAST_UPDATE_DATE     = NVL(p_last_update_date,SYSDATE),
	 LAST_UPDATED_BY      = NVL(p_last_updated_by,fnd_global.user_id),
	 LAST_UPDATE_LOGIN    = NVL(p_last_update_login,fnd_global.login_id)
  WHERE NOTE_CONTEXT_ID = p_note_context_id;

Line: 1406


     ROLLBACK TO update_note_context_pvt;

Line: 1415


      ROLLBACK TO update_note_context_pvt;

Line: 1424


     ROLLBACK TO update_note_context_pvt;

Line: 1441


END update_note_context;

Line: 1444


PROCEDURE delete_note_context
------------------------------------------------------------------------------
-- delete_note_context
--   deletes a record in the JTF_NOTE_CONTEXTS table.
------------------------------------------------------------------------------
( p_note_context_id       IN            NUMBER
, x_return_status            OUT NOCOPY VARCHAR2
, x_msg_count                OUT NOCOPY NUMBER
, x_msg_data                 OUT NOCOPY VARCHAR2
)
IS
  l_api_name VARCHAR2(2000) := 'delete_note_context';

Line: 1461


  SAVEPOINT delete_note_context_pvt;

Line: 1466


  DELETE FROM JTF_NOTE_CONTEXTS
  WHERE NOTE_CONTEXT_ID = p_note_context_id;

Line: 1478


     ROLLBACK TO delete_note_context_pvt;

Line: 1495


END delete_note_context;

Line: 1511


   )IS SELECT employee_id
       ,      customer_id
       ,      supplier_id
       ,      user_name
       FROM fnd_user
       WHERE user_id = b_user_id;

Line: 1524


   )IS SELECT full_name
       ,      employee_number
       FROM per_all_people_f
       WHERE person_id = b_employee_id;

Line: 1535


   )IS SELECT party_name
       ,      party_number
       FROM hz_parties
       WHERE party_id = b_party_id;

Line: 1546


   ) IS SELECT LAST_NAME|| ',' || FIRST_NAME || MIDDLE_NAME full_name,
               VENDOR_CONTACT_ID
          FROM po_vendor_contacts
         WHERE VENDOR_CONTACT_ID = b_supplier_id ;