APPS.CN_AGGRT_TRX_PKG SQL Statements

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

Line: 7

  G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 8

  G_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 11

  G_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 45

	SELECT nvl(intelligent_flag, 'N'), org_id
	  FROM cn_calc_submission_batches_all
	  WHERE logical_batch_id = (SELECT logical_batch_id
	                              FROM cn_process_batches_all
								 WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1);

Line: 52

	select ch.direct_salesrep_id,
	  ch.processed_period_id,
	  ch.processed_date,
	  nvl(ch.rollup_date, ch.processed_date),
	  ch.comp_group_id,
	  ch.revenue_class_id,
	  ch.trx_type,
	  sum(ch.transaction_amount),
	  sum(ch.quantity)
	  from cn_commission_headers_all ch,
	  cn_process_batches_all pb
	  WHERE pb.physical_batch_id = p_physical_batch_id
	  AND ch.direct_salesrep_id = pb.salesrep_id
	  AND ch.org_id = pb.org_id
	  AND ch.processed_date BETWEEN pb.start_date AND pb.end_date
	  AND ((g_intel_calc_flag = 'N' AND ch.status = 'CLS') OR (g_intel_calc_flag = 'Y' AND ch.status = 'CLS' AND ch.parent_header_id IS NULL))
	  group by ch.direct_salesrep_id,
	  ch.processed_period_id,
	  ch.processed_date,
	  nvl(ch.rollup_date, ch.processed_date),
	  ch.comp_group_id,
	  ch.revenue_class_id,
	  ch.trx_type;

Line: 88

       insert into cn_commission_headers_all
       (commission_header_id,
	direct_salesrep_id,
	processed_date,
	processed_period_id,
	trx_type,
	status,
	rollup_date,
	comp_group_id,
	revenue_class_id,
	transaction_amount,
	quantity,
	pre_processed_code,
	parent_header_id,
	creation_date,
	created_by,
	org_id)
   values
   (cn_commission_headers_s.nextval,
	rep_ids(i),
	processed_dates(i),
	period_ids(i),
	trx_types(i),
	'CLS_SUM',
	rollup_dates(i),
	group_ids(i),
	rev_class_ids(i),
	amounts(i),
	units(i),
	'CRPC',
	-1,
	sysdate,
	g_created_by,
	l_org_id)
    returning commission_header_id bulk collect INTO header_ids;

Line: 125

       UPDATE cn_commission_headers_all
       SET parent_header_id = header_ids(i),
        -- clku, update the last updated info
           last_update_date = G_LAST_UPDATE_DATE,
           last_updated_by = G_LAST_UPDATED_BY,
           last_update_login = G_LAST_UPDATE_LOGIN
       WHERE direct_salesrep_id = rep_ids(i)
       AND processed_period_id= period_ids(i)
       AND processed_date = processed_dates(i)
       AND nvl(rollup_date, processed_date) = rollup_dates(i)
       AND nvl(comp_group_id, -999999) = nvl(group_ids(i), -999999)
       AND revenue_class_id = rev_class_ids(i)
       AND trx_type = trx_types(i)
       AND ((g_intel_calc_flag = 'N' AND status = 'CLS') OR (g_intel_calc_flag = 'Y' AND status = 'CLS' AND parent_header_id IS NULL))
	   AND org_id = l_org_id;