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

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

Line: 36


   SELECT PLA.location_id                 LOCATION_ID
   ,      CSU.status                      STATUS
   ,      RCR.resource_id                 RESOURCE_ID
   FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
   ,      HZ_CUST_SITE_USES_ALL        CSU
   ,      HZ_CUST_ACCT_SITES_ALL       CAS
   ,      CSP_RS_CUST_RELATIONS       RCR
   ,      HZ_PARTY_SITES               HPS
   ,      HZ_LOCATIONS                 HZL
   WHERE  PLA.location_id       = b_location_id
   AND    CSU.site_use_id       = PLA.site_use_id
   AND    CSU.site_use_code     = 'SHIP_TO'
   AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
   AND    CAS.cust_account_id   = RCR.customer_id
   AND    CAS.party_site_id     = HPS.party_site_id
   AND    HPS.location_id       = HZL.location_id
   AND    PLA.LOCATION_ID       = b_location_id;

Line: 115


PROCEDURE Insert_ACC_Record
  ( p_location_id        IN NUMBER
  )
IS
  CURSOR c_location_ids (b_location_id NUMBER) IS
   SELECT CSU.site_use_id                 CSU_ID
   ,      CAS.cust_acct_site_id           CAS_ID
   ,      RCR.RS_CUST_RELATION_ID         RCR_ID
   ,      HPS.party_site_id               HPS_ID
   ,      RCR.RESOURCE_ID                 RESOURCE_ID
   FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
   ,      HZ_CUST_SITE_USES_ALL        CSU
   ,      HZ_CUST_ACCT_SITES_ALL       CAS
   ,      CSP_RS_CUST_RELATIONS       RCR
   ,      HZ_PARTY_SITES               HPS
   WHERE  PLA.location_id       = b_location_id
   AND    CSU.site_use_id       = PLA.site_use_id
   AND    CSU.site_use_code     = 'SHIP_TO'
   AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
   AND    CAS.cust_account_id   = RCR.customer_id
   AND    CAS.party_site_id     = HPS.party_site_id
   AND    PLA.LOCATION_ID       = b_location_id;

Line: 144


    , 'Entering Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 152


    , 'Inserting ACC record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 165


      , 'Insert ACC Record error: Could not find record associated with PO_LOCATION_ASSOCIATIONS_ALL.LOCATION_ID ' || p_location_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 173


   JTM_HOOK_UTIL_PKG.Insert_Acc
      (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
       , P_ACC_TABLE_NAME         => g_acc_table_name1
       , P_PK1_NAME               => g_pk1_name1
       , P_PK1_NUM_VALUE          => r_location_ids.RCR_ID
       , P_RESOURCE_ID            => l_resource_id
      );

Line: 181


    JTM_HOOK_UTIL_PKG.Insert_Acc
    (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
     , P_ACC_TABLE_NAME         => g_acc_table_name2
     , P_PK1_NAME               => g_pk1_name2
     , P_PK1_NUM_VALUE          => r_location_ids.CAS_ID
     , P_RESOURCE_ID            => l_resource_id
    );

Line: 189


    JTM_HOOK_UTIL_PKG.Insert_Acc
    (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
     , P_ACC_TABLE_NAME         => g_acc_table_name3
     , P_PK1_NAME               => g_pk1_name3
     , P_PK1_NUM_VALUE          => r_location_ids.CSU_ID
     , P_RESOURCE_ID            => l_resource_id
    );

Line: 197


    JTM_HOOK_UTIL_PKG.Insert_Acc
    (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
     , P_ACC_TABLE_NAME         => g_acc_table_name4
     , P_PK1_NAME               => g_pk1_name4
     , P_PK1_NUM_VALUE          => p_location_id
     , P_RESOURCE_ID            => l_resource_id
    );

Line: 206


    CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE( r_location_ids.HPS_ID, l_resource_id );

Line: 214


    , 'Leaving Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 217


END Insert_ACC_Record;

Line: 220


PROCEDURE Update_ACC_Record
  ( p_location_id        IN NUMBER
  )
IS
  CURSOR c_location_ids (b_location_id NUMBER) IS
   SELECT CSU.site_use_id                 CSU_ID
   ,      CAS.cust_acct_site_id           CAS_ID
   ,      RCR.RS_CUST_RELATION_ID         RCR_ID
   ,      HPS.party_site_id               HPS_ID
   ,      HZL.location_id                 HZL_ID
   ,      RCR.resource_id                 RESOURCE_ID
   FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
   ,      HZ_CUST_SITE_USES_ALL        CSU
   ,      HZ_CUST_ACCT_SITES_ALL       CAS
   ,      CSP_RS_CUST_RELATIONS       RCR
   ,      HZ_PARTY_SITES               HPS
   ,      HZ_LOCATIONS                 HZL
   WHERE  PLA.location_id       = b_location_id
   AND    CSU.site_use_id       = PLA.site_use_id
   AND    CSU.site_use_code     = 'SHIP_TO'
   AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
   AND    CAS.cust_account_id   = RCR.customer_id
   AND    CAS.party_site_id     = HPS.party_site_id
   AND    HPS.location_id       = HZL.location_id
   AND    PLA.LOCATION_ID       = b_location_id;

Line: 259


    , 'Entering Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 280


      , 'Update ACC Record error: Could not find record associated with PO_LOCATION_ASSOCIATIONS_ALL.LOCATION_ID '
        || p_location_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 293


    JTM_HOOK_UTIL_PKG.Update_Acc
     ( g_publication_item_name1
      ,g_acc_table_name1
      ,l_resource_id
      ,l_rcr_acc_id
     );

Line: 305


    JTM_HOOK_UTIL_PKG.Update_Acc
     ( g_publication_item_name2
      ,g_acc_table_name2
      ,l_resource_id
      ,l_cas_acc_id
     );

Line: 317


    JTM_HOOK_UTIL_PKG.Update_Acc
     ( g_publication_item_name3
      ,g_acc_table_name3
      ,l_resource_id
      ,l_csu_acc_id
     );

Line: 329


    JTM_HOOK_UTIL_PKG.Update_Acc
     ( g_publication_item_name4
      ,g_acc_table_name4
      ,l_resource_id
      ,l_pla_acc_id
     );

Line: 336


    CSL_HZ_PARTY_SITES_ACC_PKG.UPDATE_PARTY_SITE( r_location_ids.HPS_ID );

Line: 339


    CSL_HZ_LOCATIONS_ACC_PKG.UPDATE_LOCATION( r_location_ids.HZL_ID );

Line: 348


    , 'Leaving Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 351


END Update_ACC_Record;

Line: 353


/*** Private procedure that deletes assignment for resource from acc table ***/
PROCEDURE Delete_ACC_Record
  ( p_location_id IN NUMBER
   ,p_resource_id IN NUMBER
  )
IS
  CURSOR c_location_ids (b_location_id NUMBER) IS
   SELECT CSU.site_use_id                 CSU_ID
   ,      CAS.cust_acct_site_id           CAS_ID
   ,      RCR.RS_CUST_RELATION_ID         RCR_ID
   ,      HPS.party_site_id               HPS_ID
   ,      RCR.RESOURCE_ID                 RESOURCE_ID
   FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
   ,      HZ_CUST_SITE_USES_ALL        CSU
   ,      HZ_CUST_ACCT_SITES_ALL       CAS
   ,      CSP_RS_CUST_RELATIONS       RCR
   ,      HZ_PARTY_SITES               HPS
   WHERE  PLA.location_id       = b_location_id
   AND    CSU.site_use_id       = PLA.site_use_id
   AND    CSU.site_use_code     = 'SHIP_TO'
   AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
   AND    CAS.cust_account_id   = RCR.customer_id
   AND    CAS.party_site_id     = HPS.party_site_id
   AND    PLA.LOCATION_ID       = b_location_id;

Line: 383


    , 'Entering Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 405


      , 'Delete ACC Record error: Could not find record for LOCATION_ID ' || p_location_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 413


      , 'Delete CSP_RS_CUST_RELATIONS acc record for user: ' || p_resource_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 416


    JTM_HOOK_UTIL_PKG.Delete_Acc
     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      , P_ACC_TABLE_NAME         => g_acc_table_name1
      , P_PK1_NAME               => g_pk1_name1
      , P_PK1_NUM_VALUE          => r_location_ids.RCR_ID
      , P_RESOURCE_ID            => p_resource_id
     );

Line: 428


      , 'Delete HZ_CUST_ACCT_SITES_ALL acc record for user: ' || p_resource_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 431


    JTM_HOOK_UTIL_PKG.Delete_Acc
     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
      , P_ACC_TABLE_NAME         => g_acc_table_name2
      , P_PK1_NAME               => g_pk1_name2
      , P_PK1_NUM_VALUE          => r_location_ids.CAS_ID
      , P_RESOURCE_ID            => p_resource_id
     );

Line: 443


      , 'Delete HZ_CUST_SITE_USES_ALL acc record for user: ' || p_resource_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 446


    JTM_HOOK_UTIL_PKG.Delete_Acc
     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
      , P_ACC_TABLE_NAME         => g_acc_table_name3
      , P_PK1_NAME               => g_pk1_name3
      , P_PK1_NUM_VALUE          => r_location_ids.CSU_ID
      , P_RESOURCE_ID            => p_resource_id
     );

Line: 458


      , 'Delete PO_LOCATION_ASSOCIATIONS_ALL acc record for user: ' || p_resource_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 461


    JTM_HOOK_UTIL_PKG.Delete_Acc
     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
      , P_ACC_TABLE_NAME         => g_acc_table_name4
      , P_PK1_NAME               => g_pk1_name4
      , P_PK1_NUM_VALUE          => p_location_id
      , P_RESOURCE_ID            => p_resource_id
     );

Line: 473


      , 'Calling CSL_HZ_PARTY_SITES_ACC_PKG.Delete_Party_Site'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 477


    CSL_HZ_PARTY_SITES_ACC_PKG.Delete_Party_Site( r_location_ids.HPS_ID, p_resource_id );

Line: 485


    , 'Leaving Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 488


END Delete_ACC_Record;

Line: 490


/*** Called before location Insert ***/
PROCEDURE PRE_INSERT_SHIP_LOCATION
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 497


END PRE_INSERT_SHIP_LOCATION;

Line: 499


/*** Called after location Insert ***/
PROCEDURE POST_INSERT_SHIP_LOCATION
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_location_id        NUMBER;

Line: 521


    , 'Entering POST_INSERT hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 525


  /*** Insert record if applicable ***/
  IF Replicate_Record(l_location_id) THEN
    Insert_ACC_Record(l_location_id);

Line: 534


    , 'Leaving POST_INSERT hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 547


    , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 550


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','POST_INSERT_SHIP_LOCATION',sqlerrm);

Line: 553


END POST_INSERT_SHIP_LOCATION;

Line: 555


/* Called before location Update */
PROCEDURE PRE_UPDATE_SHIP_LOCATION
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 565


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','PRE_UPDATE_SHIP_LOCATION',sqlerrm);

Line: 568


END PRE_UPDATE_SHIP_LOCATION;

Line: 570


/* Called after assignment Update */
PROCEDURE POST_UPDATE_SHIP_LOCATION
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_location_id        NUMBER;

Line: 593


    , 'Entering POST_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 598


    Update_ACC_Record(l_location_id);

Line: 605


    , 'Leaving POST_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 616


    , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 619


  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_UPDATE_SHIP_LOCATION',sqlerrm);

Line: 622


END POST_UPDATE_SHIP_LOCATION;

Line: 624


/* Called before assignment Delete */
PROCEDURE PRE_DELETE_SHIP_LOCATION
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 631


END PRE_DELETE_SHIP_LOCATION;

Line: 633


/* Called after assignment Delete */
PROCEDURE POST_DELETE_SHIP_LOCATION
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN

  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 644


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','POST_DELETE_SHIP_LOCATION',sqlerrm);

Line: 647


END POST_DELETE_SHIP_LOCATION;

Line: 650


PROCEDURE Delete_All_ACC_Records
  ( p_resource_id in NUMBER
  , x_return_status OUT NOCOPY varchar2
  )
IS
 CURSOR c_location( b_resource_id NUMBER ) IS
  SELECT PLA.LOCATION_ID
  FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
  ,      HZ_CUST_SITE_USES_ALL        CSU
  ,      HZ_CUST_ACCT_SITES_ALL       CAS
  ,      CSP_RS_CUST_RELATIONS       RCR
  ,      HZ_PARTY_SITES               HPS
  WHERE  CSU.site_use_id       = PLA.site_use_id
  AND    CSU.site_use_code     = 'SHIP_TO'
  AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
  AND    CAS.cust_account_id   = RCR.customer_id
  AND    CAS.party_site_id     = HPS.party_site_id
  AND    RCR.RESOURCE_ID       = b_resource_id;

Line: 675


    , 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 680


    Delete_ACC_Record(r_location.location_id, p_resource_id );

Line: 687


    , 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 694


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','Delete_All_ACC_Records',sqlerrm);

Line: 697


END Delete_All_ACC_Records;

Line: 700


PROCEDURE Insert_All_ACC_Records
  ( p_resource_id in NUMBER
  , x_return_status OUT NOCOPY varchar2
  )
IS
 CURSOR c_location( b_resource_id NUMBER ) IS
  SELECT PLA.LOCATION_ID
  FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
  ,      HZ_CUST_SITE_USES_ALL        CSU
  ,      HZ_CUST_ACCT_SITES_ALL       CAS
  ,      CSP_RS_CUST_RELATIONS       RCR
  ,      HZ_PARTY_SITES               HPS
  WHERE  CSU.site_use_id       = PLA.site_use_id
  AND    CSU.site_use_code     = 'SHIP_TO'
  AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
  AND    CAS.cust_account_id   = RCR.customer_id
  AND    CAS.party_site_id     = HPS.party_site_id
  AND    RCR.RESOURCE_ID       = b_resource_id;

Line: 726


    , 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 732


      Insert_ACC_Record(r_location.location_id);

Line: 740


    , 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 747


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','Insert_All_ACC_Records',sqlerrm);

Line: 750


END Insert_All_ACC_Records;