APPS.ICX_MARGIN_WEB_ANA_PKG SQL Statements

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

Line: 9

SELECT
	cmt.build_id,
	cmt.customer_id,
	cmt.primary_salesrep_id,
	cmt.territory_id,
	cmt.inventory_item_id,
	cmt.org_id,
	cmt.sold_to_customer_name,
	sum(invoiced_amount) sum_inv_amt,
	sum(cogs_amount) sum_cogs_amt
FROM
	CST_MARGIN_TEMP cmt,
	CST_MARGIN_BUILD cmb
WHERE
	cmt.build_id = cmb.build_id AND
	cmt.gl_date >= cmb.to_date - 7 AND
	cmt.build_id = (select max(build_id) from cst_margin_temp)
GROUP BY
	cmt.build_id,
	cmt.customer_id,
	cmt.primary_salesrep_id,
	cmt.territory_id,
	cmt.inventory_item_id,
	cmt.org_id,
	cmt.sold_to_customer_name
;

Line: 36

SELECT
	cmt.build_id,
	cmt.customer_id,
	cmt.primary_salesrep_id,
	cmt.territory_id,
	cmt.inventory_item_id,
	cmt.org_id,
	cmt.sold_to_customer_name,
	sum(invoiced_amount) sum_inv_amt,
	sum(cogs_amount) sum_cogs_amt
FROM
	CST_MARGIN_TEMP cmt,
	CST_MARGIN_BUILD cmb
WHERE
	cmt.build_id = cmb.build_id AND
	cmt.gl_date >= cmb.to_date - 30 AND
	cmt.build_id = (select max(build_id) from cst_margin_temp)
GROUP BY
	cmt.build_id,
	cmt.customer_id,
	cmt.primary_salesrep_id,
	cmt.territory_id,
	cmt.inventory_item_id,
	cmt.org_id,
	cmt.sold_to_customer_name
;

Line: 63

SELECT
	cmt.build_id,
	cmt.customer_id,
	cmt.primary_salesrep_id,
	cmt.territory_id,
	cmt.inventory_item_id,
	cmt.org_id,
	cmt.sold_to_customer_name,
	sum(invoiced_amount) sum_inv_amt,
	sum(cogs_amount) sum_cogs_amt
FROM
	CST_MARGIN_TEMP cmt,
	CST_MARGIN_BUILD cmb
WHERE
	cmt.build_id = cmb.build_id AND
	cmt.gl_date >= cmb.to_date - 90 AND
	cmt.build_id = (select max(build_id) from cst_margin_temp)
GROUP BY
	cmt.build_id,
	cmt.customer_id,
	cmt.primary_salesrep_id,
	cmt.territory_id,
	cmt.inventory_item_id,
	cmt.org_id,
	cmt.sold_to_customer_name
;

Line: 93

/*   delete date from tables   */
DELETE FROM ICX_MARGIN_ANALYSIS;

Line: 95

DELETE FROM ICX_MARGIN_ANALYSIS_ERR;

Line: 124

  INSERT INTO icx_margin_analysis
	(
	build_id,
	customer_id,
	primary_salesrep_id,
	territory_id,
	inventory_item_id,
	org_id,
	sold_to_customer_name,
	invoiced_amount,
	cogs_amount,
	margin,
	period
	)
  VALUES
	(
	wkly_amt_rec.build_id,
	wkly_amt_rec.customer_id,
	wkly_amt_rec.primary_salesrep_id,
	wkly_amt_rec.territory_id,
	wkly_amt_rec.inventory_item_id,
	wkly_amt_rec.org_id,
	wkly_amt_rec.sold_to_customer_name,
	wkly_amt_rec.sum_inv_amt,
	wkly_amt_rec.sum_cogs_amt,
	sum_margin,
	'7D'
	);

Line: 161

        INSERT into icx_margin_analysis_err
          (
          build_id,
          customer_id,
          primary_salesrep_id,
	  territory_id,
          inventory_item_id,
          period
          )
        VALUES
          (
          tmp_build_id,
          tmp_customer_id,
          tmp_pri_salesrep_id,
	  tmp_territory_id,
          tmp_inv_item_id,
          '7D'
          );

Line: 180

        INSERT into icx_margin_analysis_err
          (
          build_id,
          customer_id,
          primary_salesrep_id,
	  territory_id,
          inventory_item_id,
          period
          )
        VALUES
          (
          tmp_build_id,
          tmp_customer_id,
          tmp_pri_salesrep_id,
	  tmp_territory_id,
          tmp_inv_item_id,
          '7D'
          );

Line: 230

  INSERT INTO icx_margin_analysis
	(
	build_id,
	customer_id,
	primary_salesrep_id,
	territory_id,
	inventory_item_id,
	org_id,
	sold_to_customer_name,
	invoiced_amount,
	cogs_amount,
	margin,
	period
	)
  VALUES
	(
	mnthly_amt_rec.build_id,
	mnthly_amt_rec.customer_id,
	mnthly_amt_rec.primary_salesrep_id,
	mnthly_amt_rec.territory_id,
	mnthly_amt_rec.inventory_item_id,
	mnthly_amt_rec.org_id,
	mnthly_amt_rec.sold_to_customer_name,
	mnthly_amt_rec.sum_inv_amt,
	mnthly_amt_rec.sum_cogs_amt,
	sum_margin,
	'30D'
	);

Line: 267

        INSERT into icx_margin_analysis_err
          (
          build_id,
          customer_id,
          primary_salesrep_id,
	  territory_id,
          inventory_item_id,
          period
          )
        VALUES
          (
          tmp_build_id,
          tmp_customer_id,
          tmp_pri_salesrep_id,
	  tmp_territory_id,
          tmp_inv_item_id,
          '30D'
          );

Line: 286

        INSERT into icx_margin_analysis_err
          (
          build_id,
          customer_id,
          primary_salesrep_id,
	  territory_id,
          inventory_item_id,
          period
          )
        VALUES
          (
          tmp_build_id,
          tmp_customer_id,
          tmp_pri_salesrep_id,
	  tmp_territory_id,
          tmp_inv_item_id,
          '30D'
          );

Line: 336

  INSERT INTO icx_margin_analysis
	(
	build_id,
	customer_id,
	primary_salesrep_id,
	territory_id,
	inventory_item_id,
	org_id,
	sold_to_customer_name,
	invoiced_amount,
	cogs_amount,
	margin,
	period
	)
  VALUES
	(
	qrtrly_amt_rec.build_id,
	qrtrly_amt_rec.customer_id,
	qrtrly_amt_rec.primary_salesrep_id,
	qrtrly_amt_rec.territory_id,
	qrtrly_amt_rec.inventory_item_id,
	qrtrly_amt_rec.org_id,
	qrtrly_amt_rec.sold_to_customer_name,
	qrtrly_amt_rec.sum_inv_amt,
	qrtrly_amt_rec.sum_cogs_amt,
	sum_margin,
	'90D'
	);

Line: 373

        INSERT into icx_margin_analysis_err
          (
          build_id,
          customer_id,
          primary_salesrep_id,
	  territory_id,
          inventory_item_id,
          period
          )
        VALUES
          (
          tmp_build_id,
          tmp_customer_id,
          tmp_pri_salesrep_id,
	  tmp_territory_id,
          tmp_inv_item_id,
          '90D'
          );

Line: 392

        INSERT into icx_margin_analysis_err
          (
          build_id,
          customer_id,
          primary_salesrep_id,
	  territory_id,
          inventory_item_id,
          period
          )
        VALUES
          (
          tmp_build_id,
          tmp_customer_id,
          tmp_pri_salesrep_id,
	  tmp_territory_id,
          tmp_inv_item_id,
          '90D'
          );

Line: 424

select sum(invoiced_amount)
  into tot_inv_amount
  from icx_margin_analysis
  where period=in_period;

Line: 438

select sum(margin)
  into tot_margin_amount
  from icx_margin_analysis
  where period=in_period;

Line: 449

select distinct concatenated_segments
  into temp_item_num
  from mtl_system_items_kfv
  where inventory_item_id = in_item_id;