APPS.ONT_PRT_MARGIN_ANA_PKG SQL Statements

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

Line: 7

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

Line: 35

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

Line: 63

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

Line: 93

/*   delete old data from tables   */
DELETE FROM ONT_PRT_MARGIN_ANALYSIS;

Line: 95

DELETE FROM ONT_PRT_MARGIN_ANA_ERR;

Line: 124

  INSERT INTO ONT_PRT_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 ONT_PRT_MARGIN_ANA_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 ONT_PRT_MARGIN_ANA_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 ONT_PRT_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 ONT_PRT_MARGIN_ANA_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 ONT_PRT_MARGIN_ANA_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 ONT_PRT_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 ONT_PRT_MARGIN_ANA_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 ONT_PRT_MARGIN_ANA_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 ONT_PRT_MARGIN_ANALYSIS
  where period=in_period;

Line: 438

select sum(margin)
  into tot_margin_amount
  from ONT_PRT_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;