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

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

Line: 8


G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 9


G_LAST_UPDATED_BY           NUMBER  := FND_GLOBAL.USER_ID;

Line: 12


G_LAST_UPDATE_LOGIN         NUMBER  := FND_GLOBAL.LOGIN_ID;

Line: 37


   SELECT cn_process_batches_s3.nextval
     INTO x_physical_batch_id
     FROM sys.dual;

Line: 47


 PROCEDURE update_error (x_physical_batch_id NUMBER) IS
   l_user_id  		NUMBER(15) := fnd_global.user_id;

Line: 58


        UPDATE cn_process_batches
      	   SET status_code 	      = 'ERROR'
   	      ,last_update_date       = sysdate
	      ,last_update_login      = l_login_id
	      ,last_updated_by        = l_user_id
	      ,request_id             = l_conc_request_id
	      ,program_application_id = l_prog_appl_id
	      ,program_id             = l_conc_prog_id
	      ,program_update_date    = sysdate
         WHERE physical_batch_id      = x_physical_batch_id;

Line: 68


 END update_error;

Line: 89


	 SELECT
	   salesrep_id,
	   period_id,
	   start_date,
	   end_date,
	   sales_lines_total trx_count
	   FROM
	   cn_process_batches
	   WHERE
	   physical_batch_id = p_physical_batch_id AND
	   status_code = 'IN_USE';

Line: 126


      SELECT cn_commission_headers_s.NEXTVAL
	INTO l_init_commission_header_id FROM dual;

Line: 145


	INSERT INTO cn_commission_headers_all
	  (commission_header_id,
	   direct_salesrep_id,
	   processed_date,
	   processed_period_id,
	   rollup_date,
	   transaction_amount,
	   quantity,
	   discount_percentage,
	   margin_percentage,
	   orig_currency_code,
	   TRANSACTION_AMOUNT_ORIG,
	   trx_type,
	   status,
	   pre_processed_code,
	   COMM_LINES_API_ID,
	   SOURCE_DOC_TYPE,
	   SOURCE_TRX_NUMBER,
	   quota_id,
	   srp_plan_assign_id,
	   revenue_class_id,
	   role_id,
	   comp_group_id,
	   commission_amount,
	   reversal_flag,
	   reversal_header_id,
	   reason_code,
           attribute_category,
	   attribute1,
	   attribute2,
	   attribute3,
	   attribute4,
	   attribute5,
	   attribute6,
	   attribute7,
	   attribute8,
	   attribute9,
	   attribute10,
	   attribute11,
	   attribute12,
	   attribute13,
	   attribute14,
	   attribute15,
	   attribute16,
	   attribute17,
	   attribute18,
	   attribute19,
	   attribute20,
	   attribute21,
	   attribute22,
	   attribute23,
	   attribute24,
	   attribute25,
	   attribute26,
	  attribute27,
	  attribute28,
	  attribute29,
	  attribute30,
	  attribute31,
	  attribute32,
	  attribute33,
	  attribute34,
	  attribute35,
	  attribute36,
	  attribute37,
	  attribute38,
	  attribute39,
	  attribute40,
	  attribute41,
	  attribute42,
	  attribute43,
	  attribute44,
	  attribute45,
	  attribute46,
	  attribute47,
	  attribute48,
	  attribute49,
	  attribute50,
	  attribute51,
	  attribute52,
	  attribute53,
	  attribute54,
	  attribute55,
	  attribute56,
	  attribute57,
	  attribute58,
	  attribute59,
	  attribute60,
	  attribute61,
	  attribute62,
	  attribute63,
	  attribute64,
	  attribute65,
	  attribute66,
	  attribute67,
	  attribute68,
	  attribute69,
	  attribute70,
	  attribute71,
	  attribute72,
	  attribute73,
	  attribute74,
	  attribute75,
	  attribute76,
	  attribute77,
	  attribute78,
	  attribute79,
	  attribute80,
	  attribute81,
	  attribute82,
	  attribute83,
	  attribute84,
	  attribute85,
	  attribute86,
	  attribute87,
	  attribute88,
	  attribute89,
	  attribute90,
	  attribute91,
	  attribute92,
	  attribute93,
	  attribute94,
	  attribute95,
	  attribute96,
	  attribute97,
	  attribute98,
	  attribute99,
	  attribute100,
	  last_update_date,
	  last_updated_by,
	  last_update_login,
	  creation_date,
	  created_by,
	  EXCHANGE_RATE,
	  FORECAST_ID,
	  UPSIDE_QUANTITY,
	  UPSIDE_AMOUNT,
	  UOM_CODE,
	  SOURCE_TRX_ID,
	  SOURCE_TRX_LINE_ID,
	  SOURCE_TRX_SALES_LINE_ID,
	  NEGATED_FLAG,
	  CUSTOMER_ID,
	  INVENTORY_ITEM_ID,
	  ORDER_NUMBER,
	  BOOKED_DATE,
	  INVOICE_NUMBER,
	  INVOICE_DATE,
	  BILL_TO_ADDRESS_ID,
	  SHIP_TO_ADDRESS_ID,
	  BILL_TO_CONTACT_ID,
	  SHIP_TO_CONTACT_ID,
	  ADJ_COMM_LINES_API_ID,
	  ADJUST_DATE,
	  ADJUSTED_BY,
	  REVENUE_TYPE,
	  ADJUST_ROLLUP_FLAG,
	  ADJUST_COMMENTS,
	  ADJUST_STATUS,
	  line_number,
	  type,
	  sales_channel,
	  split_pct,
          split_status,
          org_id)
	  (SELECT
	   cn_commission_headers_s.nextval,
	   batch.salesrep_id,
	   Trunc(api.processed_date),
	   batch.period_id,
	   Trunc(api.rollup_date),
	   api.acctd_transaction_amount,
	   api.quantity,
	   api.discount_percentage,
	   api.margin_percentage,
	   api.transaction_currency_code,
	   api.transaction_amount,
	   api.trx_type,
	   'COL',
	   Nvl(api.pre_processed_code,'CRPC'),
	   api.comm_lines_api_id,
	   api.source_doc_type,
	   api.source_trx_number,
	   api.quota_id,
	   api.srp_plan_assign_id,
	   api.revenue_class_id,
	   api.role_id,
	   api.comp_group_id,
	   api.commission_amount,
	   api.reversal_flag,
	   api.reversal_header_id,
	   api.reason_code,
           api.attribute_category,
	   api.attribute1,
	   api.attribute2,
	   api.attribute3,
	   api.attribute4,
	   api.attribute5,
	   api.attribute6,
	   api.attribute7,
	   api.attribute8,
	   api.attribute9,
	   api.attribute10,
	   api.attribute11,
	   api.attribute12,
	   api.attribute13,
	   api.attribute14,
	   api.attribute15,
	   api.attribute16,
	  api.attribute17,
	  api.attribute18,
	  api.attribute19,
	  api.attribute20,
	  api.attribute21,
	  api.attribute22,
	  api.attribute23,
	  api.attribute24,
	  api.attribute25,
	  api.attribute26,
	  api.attribute27,
	  api.attribute28,
	  api.attribute29,
	  api.attribute30,
	  api.attribute31,
	  api.attribute32,
	  api.attribute33,
	  api.attribute34,
	  api.attribute35,
	  api.attribute36,
	  api.attribute37,
	  api.attribute38,
	  api.attribute39,
	  api.attribute40,
	  api.attribute41,
	  api.attribute42,
	  api.attribute43,
	  api.attribute44,
	  api.attribute45,
	  api.attribute46,
	  api.attribute47,
	  api.attribute48,
	  api.attribute49,
	  api.attribute50,
	  api.attribute51,
	  api.attribute52,
	  api.attribute53,
	  api.attribute54,
	  api.attribute55,
	  api.attribute56,
	  api.attribute57,
	  api.attribute58,
	  api.attribute59,
	  api.attribute60,
	  api.attribute61,
	  api.attribute62,
	  api.attribute63,
	  api.attribute64,
	  api.attribute65,
	  api.attribute66,
	  api.attribute67,
	  api.attribute68,
	  api.attribute69,
	  api.attribute70,
	  api.attribute71,
	  api.attribute72,
	  api.attribute73,
	  api.attribute74,
	  api.attribute75,
	  api.attribute76,
	  api.attribute77,
	  api.attribute78,
	  api.attribute79,
	  api.attribute80,
	  api.attribute81,
	  api.attribute82,
	  api.attribute83,
	  api.attribute84,
	  api.attribute85,
	  api.attribute86,
	  api.attribute87,
	  api.attribute88,
	  api.attribute89,
	  api.attribute90,
	  api.attribute91,
	  api.attribute92,
	  api.attribute93,
	  api.attribute94,
	  api.attribute95,
	  api.attribute96,
	  api.attribute97,
	  api.attribute98,
	  api.attribute99,
	  api.attribute100,
	  sysdate,
	  api.last_updated_by,
	  api.last_update_login,
	  sysdate,
	  api.created_by,
	  api.exchange_rate,
	  api.FORECAST_ID,
	  api.UPSIDE_QUANTITY,
	  api.UPSIDE_AMOUNT,
	  api.UOM_CODE,
	  api.SOURCE_TRX_ID,
	  api.SOURCE_TRX_LINE_ID,
	  api.SOURCE_TRX_SALES_LINE_ID,
	  api.NEGATED_FLAG,
	  api.CUSTOMER_ID,
	  api.INVENTORY_ITEM_ID,
	  api.ORDER_NUMBER,
	  api.BOOKED_DATE,
	  api.INVOICE_NUMBER,
	  api.INVOICE_DATE,
	  api.BILL_TO_ADDRESS_ID,
	  api.SHIP_TO_ADDRESS_ID,
	  api.BILL_TO_CONTACT_ID,
	  api.SHIP_TO_CONTACT_ID,
	  api.ADJ_COMM_LINES_API_ID,
	  api.ADJUST_DATE,
	  api.ADJUSTED_BY,
	  api.REVENUE_TYPE,
	  api.ADJUST_ROLLUP_FLAG,
	  api.ADJUST_COMMENTS,
	  NVL(api.ADJUST_STATUS,'NEW'),
	  api.line_number,
	  api.type,
	  api.sales_channel,
          api.split_pct,
          api.split_status,
	  api.org_id
	  FROM
	  cn_comm_lines_api_all api
	  WHERE
	  api.load_status = 'UNLOADED' AND
	  api.processed_date >= TRUNC(p_start_date) AND
	  api.processed_date < (TRUNC(p_end_date) + 1) AND
	  ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
	  api.trx_type <> 'FORECAST' AND
	  (api.adjust_status <> 'SCA_PENDING') AND --OR api.adjust_status IS NULL) AND
	  api.salesrep_id = batch.salesrep_id AND
	  api.processed_date >= Trunc(batch.start_date) AND
	  api.processed_date < (Trunc(batch.end_date) + 1) AND
      api.org_id = p_org_id);

Line: 489


	INSERT INTO cn_commission_headers_all
	  (commission_header_id,
	   direct_salesrep_id,
	   processed_date,
	   processed_period_id,
	   rollup_date,
	   transaction_amount,
	   quantity,
	   discount_percentage,
	   margin_percentage,
	   orig_currency_code,
	   TRANSACTION_AMOUNT_ORIG,
	   trx_type,
	   status,
	   pre_processed_code,
	   COMM_LINES_API_ID,
	   SOURCE_DOC_TYPE,
	   SOURCE_TRX_NUMBER,
	   quota_id,
	   srp_plan_assign_id,
	   revenue_class_id,
	   role_id,
	   comp_group_id,
	   commission_amount,
	   reversal_flag,
	   reversal_header_id,
	   reason_code,
           attribute_category,
	   attribute1,
	   attribute2,
	   attribute3,
	   attribute4,
	   attribute5,
	   attribute6,
	   attribute7,
	   attribute8,
	   attribute9,
	   attribute10,
	   attribute11,
	   attribute12,
	   attribute13,
	   attribute14,
	   attribute15,
	   attribute16,
	   attribute17,
	   attribute18,
	   attribute19,
	   attribute20,
	   attribute21,
	   attribute22,
	   attribute23,
	   attribute24,
	   attribute25,
	   attribute26,
	  attribute27,
	  attribute28,
	  attribute29,
	  attribute30,
	  attribute31,
	  attribute32,
	  attribute33,
	  attribute34,
	  attribute35,
	  attribute36,
	  attribute37,
	  attribute38,
	  attribute39,
	  attribute40,
	  attribute41,
	  attribute42,
	  attribute43,
	  attribute44,
	  attribute45,
	  attribute46,
	  attribute47,
	  attribute48,
	  attribute49,
	  attribute50,
	  attribute51,
	  attribute52,
	  attribute53,
	  attribute54,
	  attribute55,
	  attribute56,
	  attribute57,
	  attribute58,
	  attribute59,
	  attribute60,
	  attribute61,
	  attribute62,
	  attribute63,
	  attribute64,
	  attribute65,
	  attribute66,
	  attribute67,
	  attribute68,
	  attribute69,
	  attribute70,
	  attribute71,
	  attribute72,
	  attribute73,
	  attribute74,
	  attribute75,
	  attribute76,
	  attribute77,
	  attribute78,
	  attribute79,
	  attribute80,
	  attribute81,
	  attribute82,
	  attribute83,
	  attribute84,
	  attribute85,
	  attribute86,
	  attribute87,
	  attribute88,
	  attribute89,
	  attribute90,
	  attribute91,
	  attribute92,
	  attribute93,
	  attribute94,
	  attribute95,
	  attribute96,
	  attribute97,
	  attribute98,
	  attribute99,
	  attribute100,
	  last_update_date,
	  last_updated_by,
	  last_update_login,
	  creation_date,
	  created_by,
	  EXCHANGE_RATE,
	  FORECAST_ID,
	  UPSIDE_QUANTITY,
	  UPSIDE_AMOUNT,
	  UOM_CODE,
	  SOURCE_TRX_ID,
	  SOURCE_TRX_LINE_ID,
	  SOURCE_TRX_SALES_LINE_ID,
	  NEGATED_FLAG,
	  CUSTOMER_ID,
	  INVENTORY_ITEM_ID,
	  ORDER_NUMBER,
	  BOOKED_DATE,
	  INVOICE_NUMBER,
	  INVOICE_DATE,
	  BILL_TO_ADDRESS_ID,
	  SHIP_TO_ADDRESS_ID,
	  BILL_TO_CONTACT_ID,
	  SHIP_TO_CONTACT_ID,
	  ADJ_COMM_LINES_API_ID,
	  ADJUST_DATE,
	  ADJUSTED_BY,
	  REVENUE_TYPE,
	  ADJUST_ROLLUP_FLAG,
	  ADJUST_COMMENTS,
	  ADJUST_STATUS,
	  line_number,
	  type,
	  sales_channel,
	  split_pct,
          split_status,
          org_id)
	  (SELECT
	   cn_commission_headers_s.nextval,
	   batch.salesrep_id,
	   Trunc(api.processed_date),
	   batch.period_id,
	   Trunc(api.rollup_date),
	   api.acctd_transaction_amount,
	   api.quantity,
	   api.discount_percentage,
	   api.margin_percentage,
	   api.transaction_currency_code,
	   api.transaction_amount,
	   api.trx_type,
	   'COL',
	   Nvl(api.pre_processed_code,'CRPC'),
	   api.comm_lines_api_id,
	   api.source_doc_type,
	   api.source_trx_number,
	   api.quota_id,
	   api.srp_plan_assign_id,
	   api.revenue_class_id,
	   api.role_id,
	   api.comp_group_id,
	   api.commission_amount,
	   api.reversal_flag,
	   api.reversal_header_id,
	   api.reason_code,
           api.attribute_category,
	   api.attribute1,
	   api.attribute2,
	   api.attribute3,
	   api.attribute4,
	   api.attribute5,
	   api.attribute6,
	   api.attribute7,
	   api.attribute8,
	   api.attribute9,
	   api.attribute10,
	   api.attribute11,
	   api.attribute12,
	   api.attribute13,
	   api.attribute14,
	   api.attribute15,
	   api.attribute16,
	  api.attribute17,
	  api.attribute18,
	  api.attribute19,
	  api.attribute20,
	  api.attribute21,
	  api.attribute22,
	  api.attribute23,
	  api.attribute24,
	  api.attribute25,
	  api.attribute26,
	  api.attribute27,
	  api.attribute28,
	  api.attribute29,
	  api.attribute30,
	  api.attribute31,
	  api.attribute32,
	  api.attribute33,
	  api.attribute34,
	  api.attribute35,
	  api.attribute36,
	  api.attribute37,
	  api.attribute38,
	  api.attribute39,
	  api.attribute40,
	  api.attribute41,
	  api.attribute42,
	  api.attribute43,
	  api.attribute44,
	  api.attribute45,
	  api.attribute46,
	  api.attribute47,
	  api.attribute48,
	  api.attribute49,
	  api.attribute50,
	  api.attribute51,
	  api.attribute52,
	  api.attribute53,
	  api.attribute54,
	  api.attribute55,
	  api.attribute56,
	  api.attribute57,
	  api.attribute58,
	  api.attribute59,
	  api.attribute60,
	  api.attribute61,
	  api.attribute62,
	  api.attribute63,
	  api.attribute64,
	  api.attribute65,
	  api.attribute66,
	  api.attribute67,
	  api.attribute68,
	  api.attribute69,
	  api.attribute70,
	  api.attribute71,
	  api.attribute72,
	  api.attribute73,
	  api.attribute74,
	  api.attribute75,
	  api.attribute76,
	  api.attribute77,
	  api.attribute78,
	  api.attribute79,
	  api.attribute80,
	  api.attribute81,
	  api.attribute82,
	  api.attribute83,
	  api.attribute84,
	  api.attribute85,
	  api.attribute86,
	  api.attribute87,
	  api.attribute88,
	  api.attribute89,
	  api.attribute90,
	  api.attribute91,
	  api.attribute92,
	  api.attribute93,
	  api.attribute94,
	  api.attribute95,
	  api.attribute96,
	  api.attribute97,
	  api.attribute98,
	  api.attribute99,
	  api.attribute100,
	  sysdate,
	  api.last_updated_by,
	  api.last_update_login,
	  sysdate,
	  api.created_by,
	  api.exchange_rate,
	  api.FORECAST_ID,
	  api.UPSIDE_QUANTITY,
	  api.UPSIDE_AMOUNT,
	  api.UOM_CODE,
	  api.SOURCE_TRX_ID,
	  api.SOURCE_TRX_LINE_ID,
	  api.SOURCE_TRX_SALES_LINE_ID,
	  api.NEGATED_FLAG,
	  api.CUSTOMER_ID,
	  api.INVENTORY_ITEM_ID,
	  api.ORDER_NUMBER,
	  api.BOOKED_DATE,
	  api.INVOICE_NUMBER,
	  api.INVOICE_DATE,
	  api.BILL_TO_ADDRESS_ID,
	  api.SHIP_TO_ADDRESS_ID,
	  api.BILL_TO_CONTACT_ID,
	  api.SHIP_TO_CONTACT_ID,
	  api.ADJ_COMM_LINES_API_ID,
	  api.ADJUST_DATE,
	  api.ADJUSTED_BY,
	  api.REVENUE_TYPE,
	  api.ADJUST_ROLLUP_FLAG,
	  api.ADJUST_COMMENTS,
	  NVL(api.ADJUST_STATUS,'NEW'),
	  api.line_number,
	  api.type,
	  api.sales_channel,
          api.split_pct,
          api.split_status,
	  api.org_id
	  FROM
	  cn_comm_lines_api_all api
	  WHERE
	  api.load_status = 'UNLOADED' AND
	  api.processed_date >= TRUNC(p_start_date) AND
	  api.processed_date < (TRUNC(p_end_date) + 1) AND
	  ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
	  api.trx_type <> 'FORECAST' AND
	  (api.adjust_status <> 'SCA_PENDING') AND -- OR api.adjust_status IS NULL) AND
	  api.salesrep_id = batch.salesrep_id AND
	  api.processed_date >= Trunc(batch.start_date) AND
	  api.processed_date < (Trunc(batch.end_date) + 1) AND
      (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y') AND
          api.org_id = p_org_id);

Line: 838


	UPDATE cn_comm_lines_api_all api
	  SET load_Status = 'LOADED'
	  WHERE
	  api.load_status  = 'UNLOADED' AND
	  api.processed_date >= TRUNC(p_start_date) AND
	  api.processed_date < (TRUNC(p_end_date) + 1) AND
	  ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
	  api.trx_type <> 'FORECAST' AND
	  (api.adjust_status <> 'SCA_PENDING' ) AND -- OR api.adjust_status IS NULL) AND
	  api.salesrep_id = batch.salesrep_id AND
	  api.processed_date >= Trunc(batch.start_date) AND
	  api.processed_date < (Trunc(batch.end_date) + 1) AND
          api.org_id = p_org_id;

Line: 852


	UPDATE cn_comm_lines_api_all api
	  SET load_Status = 'LOADED'
	  WHERE
	  api.load_status  = 'UNLOADED' AND
	  api.processed_date >= TRUNC(p_start_date) AND
	  api.processed_date < (TRUNC(p_end_date) + 1) AND
	  ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
	  api.trx_type <> 'FORECAST' AND
	  (api.adjust_status <> 'SCA_PENDING' ) AND -- OR api.adjust_status IS NULL) AND
	  api.salesrep_id = batch.salesrep_id AND
	  api.processed_date >= Trunc(batch.start_date) AND
	  api.processed_date < (Trunc(batch.end_date) + 1) AND
      (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y') AND
          api.org_id = p_org_id;

Line: 873


	   SELECT cch.commission_header_id, cch.reversal_flag, cch.reversal_header_id
	     FROM cn_commission_headers cch,
	     (SELECT DISTINCT salesrep_id
	      FROM cn_process_batches
	      WHERE physical_batch_id = p_physical_batch_id
	      AND status_code = 'IN_USE') pb
	     WHERE cch.direct_salesrep_id = pb.salesrep_id
	     AND cch.commission_header_id > l_init_commission_header_id;

Line: 1121


      SELECT  salesrep_id, SUM(sales_lines_total) srp_trx_count
	FROM cn_process_batches
	WHERE logical_batch_id = p_logical_batch_id
	AND status_code = 'IN_USE'
	GROUP BY salesrep_id;

Line: 1134


	 SELECT
	   api.employee_number employee_number,
	   api.salesrep_id salesrep_id,
	   acc.period_id period_id,
	   acc.start_date start_date,
	   acc.end_date end_date,
	   count(*) trx_count
	   FROM
	   cn_comm_lines_api_all api,
	   cn_acc_period_statuses_v acc
	   WHERE
	   api.load_status = 'UNLOADED' AND
	   api.trx_type <> 'FORECAST' AND
  	   (api.adjust_status <> 'SCA_PENDING' ) AND -- OR api.adjust_status IS NULL) AND
	   api.processed_date >= TRUNC(p_start_date) AND
	   api.processed_date < (TRUNC(p_end_date) + 1) AND
	   ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
	   api.processed_date >= acc.start_date AND
	   api.processed_date < (acc.end_date + 1) AND
	   api.org_id = p_org_id AND
	   acc.org_id = p_org_id  --added for the bug 7494675
	   GROUP BY
	   api.employee_number,
	   api.salesrep_id,
	   acc.period_id,
	   acc.start_date,
	   acc.end_date;

Line: 1166


	 SELECT
	   api.employee_number employee_number,
	   api.salesrep_id salesrep_id,
	   acc.period_id period_id,
	   acc.start_date start_date,
	   acc.end_date end_date,
	   count(*) trx_count
	   FROM
	   cn_comm_lines_api_all api,
	   cn_acc_period_statuses_v acc
	   WHERE
	   api.load_status = 'UNLOADED' AND
	   api.trx_type <> 'FORECAST' AND
       (adjust_status <> 'SCA_PENDING' ) AND -- OR adjust_status IS NULL) AND
	   api.processed_date >= TRUNC(p_start_date) AND
	   api.processed_date < (TRUNC(p_end_date) + 1) AND
	   ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
	   api.processed_date >= acc.start_date AND
	   api.processed_date < (acc.end_date + 1) AND
       api.org_id = p_org_id AND
       acc.org_id = p_org_id AND --added for the bug 7494675
       (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y')
	   GROUP BY
	   api.employee_number,
	   api.salesrep_id,
	   acc.period_id,
	   acc.start_date,
	   acc.end_date;

Line: 1197


	 SELECT DISTINCT physical_batch_id
	   FROM cn_process_batches
	   WHERE logical_batch_id = l_logical_batch_id
	   AND status_code = 'IN_USE';

Line: 1305


	  select count(*)
      into   l_count
	  from   cn_period_statuses_all
	  where  period_status = 'O'
	  and    org_id = p_org_id
	  and    (period_set_id, period_type_id) =
               (select period_set_id, period_type_id
	            from   cn_repositories_all
	            where  org_id = p_org_id)
	  and p_start_date between start_date and end_date;

Line: 1330


	  select count(*)
      into   l_count
	  from   cn_period_statuses_all
	  where  period_status = 'O'
	  and    org_id = p_org_id
	  and    (period_set_id, period_type_id) =
               (select period_set_id, period_type_id
	            from   cn_repositories_all
	            where  org_id = p_org_id)
	  and p_end_date between start_date and end_date;

Line: 1393


      SELECT cn_process_batches_s2.NEXTVAL
	INTO l_logical_batch_id
	FROM sys.dual;

Line: 1438


	 SELECT cn_process_batches_s1.NEXTVAL
	   INTO l_process_batch_id
	   FROM sys.dual;

Line: 1441


	 debugmsg('Loader : insert into cn_process_batches....');

Line: 1459


	 INSERT INTO cn_process_batches_all
	   ( process_batch_id
	     ,logical_batch_id
	     ,srp_period_id
	     ,period_id
	     ,end_period_id
	     ,start_date
	     ,end_date
	     ,salesrep_id
	     ,sales_lines_total
	     ,status_code
	     ,process_batch_type
	     ,creation_date
	     ,created_by
	     ,last_update_date
	     ,last_updated_by
	     ,last_update_login
	     ,request_id
	     ,program_application_id
	     ,program_id
	     ,program_update_date
	     ,org_id)
	   VALUES
	   (
	    l_process_batch_id
	    ,l_logical_batch_id
	    ,l_process_batch_id        -- a dummy value for a not null column
	    ,l_period_id_tbl(i)        -- start_period_id
	    ,l_period_id_tbl(i)        -- end_period_id
	    ,l_start_date_tbl(i)       -- start_date
	    ,l_end_date_tbl(i)         -- end_date
	    ,l_srp_id_tbl(i)           -- salesrep_id
	    ,l_count_tbl(i)            -- sales_lines_total
	    ,'IN_USE'                  -- status_code
	    ,'CREATED_BY_LOADER'       -- process_batch_type
	    ,sysdate
	    ,l_user_id
	    ,sysdate
	    ,l_user_id
	    ,l_login_id
	    ,l_conc_request_id
	    ,l_prog_appl_id
	    ,l_conc_prog_id
        ,sysdate
 	    ,p_org_id);

Line: 1730


         UPDATE cn_comm_lines_api_all
         SET load_status = 'SALESREP ERROR'
	 WHERE
	   load_status = 'UNLOADED' AND
	   trx_type <> 'FORECAST' AND
       (adjust_status <> 'SCA_PENDING' ) AND -- OR adjust_status IS NULL) AND
	   processed_date >= TRUNC(p_start_date) AND
	   processed_date < (TRUNC(p_end_date) + 1) AND
	   ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id)) AND
           org_id = p_org_id;