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

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

Line: 11


procedure INSERT_ROW (
   X_ROWID                IN out NOCOPY VARCHAR2,
   X_VOL_HIST_ID          IN out NOCOPY NUMBER,
   X_VOL_HIST_NUM         IN out NOCOPY NUMBER,
   X_LINE_ITEM_ID         IN NUMBER,
   X_PERIOD_ID            IN NUMBER,
   X_START_DATE           IN DATE,
   X_END_DATE             IN DATE,
   X_GRP_DATE_ID          IN NUMBER,
   X_GROUP_DATE           IN DATE,
   X_REPORTING_DATE       IN DATE,
   X_DUE_DATE             IN DATE,
   X_INVOICING_DATE       IN DATE,
   X_ACTUAL_GL_ACCOUNT_ID IN NUMBER,
   X_ACTUAL_AMOUNT        IN NUMBER,
   X_DAILY_ACTUAL_AMOUNT  in NUMBER,
   X_VOL_HIST_STATUS_CODE IN VARCHAR2,
   X_REPORT_TYPE_CODE     IN VARCHAR2,
   X_CERTIFIED_BY         IN NUMBER,
   X_ACTUAL_EXP_CODE      IN VARCHAR2,
   X_FOR_GL_ACCOUNT_ID    IN NUMBER,
   X_FORECASTED_AMOUNT    IN NUMBER,
   X_FORECASTED_EXP_CODE  IN VARCHAR2,
   X_VARIANCE_EXP_CODE    IN VARCHAR2,
   X_COMMENTS             IN VARCHAR2,
   X_ATTRIBUTE_CATEGORY   IN VARCHAR2,
   X_ATTRIBUTE1           IN VARCHAR2,
   X_ATTRIBUTE2           IN VARCHAR2,
   X_ATTRIBUTE3           IN VARCHAR2,
   X_ATTRIBUTE4           IN VARCHAR2,
   X_ATTRIBUTE5           IN VARCHAR2,
   X_ATTRIBUTE6           IN VARCHAR2,
   X_ATTRIBUTE7           IN VARCHAR2,
   X_ATTRIBUTE8           IN VARCHAR2,
   X_ATTRIBUTE9           IN VARCHAR2,
   X_ATTRIBUTE10          IN VARCHAR2,
   X_ATTRIBUTE11          IN VARCHAR2,
   X_ATTRIBUTE12          IN VARCHAR2,
   X_ATTRIBUTE13          IN VARCHAR2,
   X_ATTRIBUTE14          IN VARCHAR2,
   X_ATTRIBUTE15          IN VARCHAR2,
   X_ORG_ID               IN NUMBER,
   X_CREATION_DATE        IN DATE,
   X_CREATED_BY           IN NUMBER,
   X_LAST_UPDATE_DATE     IN DATE,
   X_LAST_UPDATED_BY      IN NUMBER,
   X_LAST_UPDATE_LOGIN    IN NUMBER
)
IS
   CURSOR C IS
      SELECT ROWID
      FROM PN_VAR_VOL_HIST_ALL
      WHERE VOL_HIST_ID = X_VOL_HIST_ID;

Line: 69


   PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.INSERT_ROW (+)');

Line: 74


   SELECT  nvl(max(hist.VOL_HIST_NUM),0)
   INTO    X_VOL_HIST_NUM
   FROM    PN_VAR_VOL_HIST_ALL hist
   WHERE   hist.LINE_ITEM_ID    =  X_LINE_ITEM_ID;

Line: 85


      SELECT  pn_var_vol_hist_s.nextval
      INTO    X_VOL_HIST_ID
      FROM    dual;

Line: 99


   INSERT INTO PN_VAR_VOL_HIST_ALL
   (
      VOL_HIST_ID,
      VOL_HIST_NUM,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      LINE_ITEM_ID,
      PERIOD_ID,
      START_DATE,
      END_DATE,
      GRP_DATE_ID,
      GROUP_DATE,
      REPORTING_DATE,
      DUE_DATE,
      INVOICING_DATE,
      ACTUAL_GL_ACCOUNT_ID,
      ACTUAL_AMOUNT,
      DAILY_ACTUAL_AMOUNT,
      VOL_HIST_STATUS_CODE,
      REPORT_TYPE_CODE,
      CERTIFIED_BY,
      ACTUAL_EXP_CODE,
      FOR_GL_ACCOUNT_ID,
      FORECASTED_AMOUNT,
      FORECASTED_EXP_CODE,
      VARIANCE_EXP_CODE,
      COMMENTS,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      ORG_ID
   )
   VALUES
   (
      X_VOL_HIST_ID,
      X_VOL_HIST_NUM,
      X_LAST_UPDATE_DATE,
      X_LAST_UPDATED_BY,
      X_CREATION_DATE,
      X_CREATED_BY,
      X_LAST_UPDATE_LOGIN,
      X_LINE_ITEM_ID,
      X_PERIOD_ID,
      X_START_DATE,
      X_END_DATE,
      X_GRP_DATE_ID,
      X_GROUP_DATE,
      X_REPORTING_DATE,
      X_DUE_DATE,
      X_INVOICING_DATE,
      X_ACTUAL_GL_ACCOUNT_ID,
      X_ACTUAL_AMOUNT,
      l_return_daily_amount,
      X_VOL_HIST_STATUS_CODE,
      X_REPORT_TYPE_CODE,
      X_CERTIFIED_BY,
      X_ACTUAL_EXP_CODE,
      X_FOR_GL_ACCOUNT_ID,
      X_FORECASTED_AMOUNT,
      X_FORECASTED_EXP_CODE,
      X_VARIANCE_EXP_CODE,
      X_COMMENTS,
      X_ATTRIBUTE_CATEGORY,
      X_ATTRIBUTE1,
      X_ATTRIBUTE2,
      X_ATTRIBUTE3,
      X_ATTRIBUTE4,
      X_ATTRIBUTE5,
      X_ATTRIBUTE6,
      X_ATTRIBUTE7,
      X_ATTRIBUTE8,
      X_ATTRIBUTE9,
      X_ATTRIBUTE10,
      X_ATTRIBUTE11,
      X_ATTRIBUTE12,
      X_ATTRIBUTE13,
      X_ATTRIBUTE14,
      X_ATTRIBUTE15,
      X_ORG_ID
   ) ;

Line: 204


   UPDATE pn_var_lines_all
   SET sales_vol_update_flag = 'Y'
   WHERE line_item_id = x_line_item_id;

Line: 208


   PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.INSERT_ROW (-)');

Line: 210


END INSERT_ROW;

Line: 262


      SELECT *
      FROM  PN_VAR_VOL_HIST_ALL
      WHERE VOL_HIST_ID = X_VOL_HIST_ID
      FOR UPDATE OF VOL_HIST_ID NOWAIT;

Line: 511


procedure UPDATE_ROW (
   X_VOL_HIST_ID          in NUMBER,
   X_VOL_HIST_NUM         in NUMBER,
   X_LINE_ITEM_ID         in NUMBER,
   X_PERIOD_ID            in NUMBER,
   X_START_DATE           in DATE,
   X_END_DATE             in DATE,
   X_GRP_DATE_ID          in NUMBER,
   X_GROUP_DATE           in DATE,
   X_REPORTING_DATE       in DATE,
   X_DUE_DATE             in DATE,
   X_INVOICING_DATE       in DATE,
   X_ACTUAL_GL_ACCOUNT_ID in NUMBER,
   X_ACTUAL_AMOUNT        in NUMBER,
   X_DAILY_ACTUAL_AMOUNT  in NUMBER,
   X_VOL_HIST_STATUS_CODE in VARCHAR2,
   X_REPORT_TYPE_CODE     in VARCHAR2,
   X_CERTIFIED_BY         in NUMBER,
   X_ACTUAL_EXP_CODE      in VARCHAR2,
   X_FOR_GL_ACCOUNT_ID    in NUMBER,
   X_FORECASTED_AMOUNT    in NUMBER,
   X_FORECASTED_EXP_CODE  in VARCHAR2,
   X_VARIANCE_EXP_CODE    in VARCHAR2,
   X_COMMENTS             in VARCHAR2,
   X_ATTRIBUTE_CATEGORY   in VARCHAR2,
   X_ATTRIBUTE1           in VARCHAR2,
   X_ATTRIBUTE2           in VARCHAR2,
   X_ATTRIBUTE3           in VARCHAR2,
   X_ATTRIBUTE4           in VARCHAR2,
   X_ATTRIBUTE5           in VARCHAR2,
   X_ATTRIBUTE6           in VARCHAR2,
   X_ATTRIBUTE7           in VARCHAR2,
   X_ATTRIBUTE8           in VARCHAR2,
   X_ATTRIBUTE9           in VARCHAR2,
   X_ATTRIBUTE10          in VARCHAR2,
   X_ATTRIBUTE11          in VARCHAR2,
   X_ATTRIBUTE12          in VARCHAR2,
   X_ATTRIBUTE13          in VARCHAR2,
   X_ATTRIBUTE14          in VARCHAR2,
   X_ATTRIBUTE15          in VARCHAR2,
   X_LAST_UPDATE_DATE     in DATE,
   X_LAST_UPDATED_BY      in NUMBER,
   X_LAST_UPDATE_LOGIN    in NUMBER
)
IS

   l_return_daily_amount   NUMBER := 0;

Line: 561


   PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.UPDATE_ROW (+)');

Line: 572


   UPDATE PN_VAR_VOL_HIST_ALL
   SET
      VOL_HIST_NUM         = X_VOL_HIST_NUM,
      LINE_ITEM_ID         = X_LINE_ITEM_ID,
      PERIOD_ID            = X_PERIOD_ID,
      START_DATE           = X_START_DATE,
      END_DATE             = X_END_DATE,
      GRP_DATE_ID          = X_GRP_DATE_ID,
      GROUP_DATE           = X_GROUP_DATE,
      REPORTING_DATE       = X_REPORTING_DATE,
      DUE_DATE             = X_DUE_DATE,
      INVOICING_DATE       = X_INVOICING_DATE,
      ACTUAL_GL_ACCOUNT_ID = X_ACTUAL_GL_ACCOUNT_ID,
      ACTUAL_AMOUNT        = X_ACTUAL_AMOUNT,
      DAILY_ACTUAL_AMOUNT  = l_return_daily_amount,
      VOL_HIST_STATUS_CODE = X_VOL_HIST_STATUS_CODE,
      REPORT_TYPE_CODE     = X_REPORT_TYPE_CODE,
      CERTIFIED_BY         = X_CERTIFIED_BY,
      ACTUAL_EXP_CODE      = X_ACTUAL_EXP_CODE,
      FOR_GL_ACCOUNT_ID    = X_FOR_GL_ACCOUNT_ID,
      FORECASTED_AMOUNT    = X_FORECASTED_AMOUNT,
      FORECASTED_EXP_CODE  = X_FORECASTED_EXP_CODE,
      VARIANCE_EXP_CODE    = X_VARIANCE_EXP_CODE,
      COMMENTS             = X_COMMENTS,
      ATTRIBUTE_CATEGORY   = X_ATTRIBUTE_CATEGORY,
      ATTRIBUTE1           = X_ATTRIBUTE1,
      ATTRIBUTE2           = X_ATTRIBUTE2,
      ATTRIBUTE3           = X_ATTRIBUTE3,
      ATTRIBUTE4           = X_ATTRIBUTE4,
      ATTRIBUTE5           = X_ATTRIBUTE5,
      ATTRIBUTE6           = X_ATTRIBUTE6,
      ATTRIBUTE7           = X_ATTRIBUTE7,
      ATTRIBUTE8           = X_ATTRIBUTE8,
      ATTRIBUTE9           = X_ATTRIBUTE9,
      ATTRIBUTE10          = X_ATTRIBUTE10,
      ATTRIBUTE11          = X_ATTRIBUTE11,
      ATTRIBUTE12          = X_ATTRIBUTE12,
      ATTRIBUTE13          = X_ATTRIBUTE13,
      ATTRIBUTE14          = X_ATTRIBUTE14,
      ATTRIBUTE15          = X_ATTRIBUTE15,
      LAST_UPDATE_DATE     = X_LAST_UPDATE_DATE,
      LAST_UPDATED_BY      = X_LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN    = X_LAST_UPDATE_LOGIN
   WHERE VOL_HIST_ID = X_VOL_HIST_ID
   ;

Line: 622


   UPDATE pn_var_lines_all
   SET sales_vol_update_flag = 'Y'
   WHERE line_item_id = x_line_item_id;

Line: 626


   PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.UPDATE_ROW (-)');

Line: 628


END UPDATE_ROW;

Line: 637


procedure DELETE_ROW (
  X_VOL_HIST_ID in NUMBER
) IS

   /* Get the details of line item id for thsi volume history */
   CURSOR line_item_cur IS
     SELECT line_item_id
       FROM pn_var_vol_hist_all
      WHERE vol_hist_id = x_vol_hist_id;

Line: 649


   PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.DELETE_ROW (+)');

Line: 651


   /* Update the sales_vol_update_flag to 'Y' for line for which volume history
      is deleted */
   FOR rec IN line_item_cur LOOP

      UPDATE pn_var_lines_all
      SET sales_vol_update_flag = 'Y'
      WHERE line_item_id = rec.line_item_id;

Line: 663


   DELETE FROM PN_VAR_VOL_HIST_ALL
   WHERE VOL_HIST_ID = X_VOL_HIST_ID;

Line: 670


   PNP_DEBUG_PKG.debug ('PN_VAR_VOL_HIST_PKG.DELETE_ROW (-)');

Line: 672


END DELETE_ROW;

Line: 743


   X_LAST_UPDATE_DATE     in DATE,
   X_LAST_UPDATED_BY      in NUMBER,
   X_LAST_UPDATE_LOGIN    in NUMBER
)
IS

   l_return_daily_amount   NUMBER := 0;

Line: 753


     SELECT *
     FROM pn_var_vol_hist_all
     WHERE vol_hist_id = x_vol_hist_id;

Line: 772


      UPDATE PN_VAR_VOL_HIST_ALL
      SET
         vol_hist_num         = NVL( x_vol_hist_num, rec.vol_hist_num),
         line_item_id         = x_line_item_id,
         period_id            = NVL( x_period_id, rec.period_id),
         start_date           = NVL( x_start_date, rec.start_date),
         end_date             = NVL( x_end_date, rec.end_date),
         grp_date_id          = NVL( x_grp_date_id, rec.grp_date_id),
         group_date           = NVL( x_group_date, rec.group_date),
         reporting_date       = NVL( x_reporting_date, rec.reporting_date),
         due_date             = NVL( x_due_date, rec.due_date),
         invoicing_date       = NVL( x_invoicing_date, rec.invoicing_date),
         actual_gl_account_id = NVL( x_actual_gl_account_id, rec.actual_gl_account_id),
         actual_amount        = NVL( x_actual_amount, rec.actual_amount),
         daily_actual_amount  = NVL( l_return_daily_amount, rec.daily_actual_amount),
         vol_hist_status_code = NVL( x_vol_hist_status_code, rec.vol_hist_status_code),
         report_type_code     = NVL( x_report_type_code, rec.report_type_code),
         certified_by         = NVL( x_certified_by, rec.certified_by),
         actual_exp_code      = NVL( x_actual_exp_code, rec.actual_exp_code),
         for_gl_account_id    = NVL( x_for_gl_account_id, rec.for_gl_account_id),
         forecasted_amount    = NVL( x_forecasted_amount, rec.forecasted_amount),
         forecasted_exp_code  = NVL( x_forecasted_exp_code, rec.forecasted_exp_code),
         variance_exp_code    = NVL( x_variance_exp_code, rec.variance_exp_code),
         comments             = NVL( x_comments, rec.comments),
         attribute_category   = NVL( x_attribute_category, rec.attribute_category),
         attribute1           = NVL( x_attribute1, rec.attribute1),
         attribute2           = NVL( x_attribute2, rec.attribute2),
         attribute3           = NVL( x_attribute3, rec.attribute3),
         attribute4           = NVL( x_attribute4, rec.attribute4),
         attribute5           = NVL( x_attribute5, rec.attribute5),
         attribute6           = NVL( x_attribute6, rec.attribute6),
         attribute7           = NVL( x_attribute7, rec.attribute7),
         attribute8           = NVL( x_attribute8, rec.attribute8),
         attribute9           = NVL( x_attribute9, rec.attribute9),
         attribute10          = NVL( x_attribute10, rec.attribute10),
         attribute11          = NVL( x_attribute11, rec.attribute11),
         attribute12          = NVL( x_attribute12, rec.attribute12),
         attribute13          = NVL( x_attribute13, rec.attribute13),
         attribute14          = NVL( x_attribute14, rec.attribute14),
         attribute15          = NVL( x_attribute15, rec.attribute15),
         last_update_date     = NVL( x_last_update_date, rec.last_update_date),
         last_updated_by      = NVL( x_last_updated_by, rec.last_updated_by),
         last_update_login    = NVL( x_last_update_login, rec.last_update_login)
      WHERE vol_hist_id = x_vol_hist_id
      ;

Line: 822


      UPDATE pn_var_lines_all
      SET sales_vol_update_flag = 'Y'
      WHERE line_item_id = x_line_item_id;