APPS.OZF_RESALE_PVT SQL Statements

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

Line: 18

SELECT org_id
  FROM ozf_resale_batches_all
 WHERE resale_batch_id = p_id;

Line: 347

SELECT *
  FROM ozf_resale_lines_int
 WHERE status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
   AND resale_batch_id = p_id;

Line: 354

SELECT NVL(batch_count,0)
  FROM ozf_resale_batches
 WHERE resale_batch_id = pc_batch_id;

Line: 359

SELECT count(1)
  FROM ozf_resale_lines_int
 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
   AND resale_batch_id = p_id;

Line: 365

SELECT count(1)
  FROM ozf_resale_lines_int
 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
   AND resale_batch_id = p_id;

Line: 419

      DELETE FROM ozf_resale_logs_all
      WHERE resale_id_type = 'BATCH'
      AND resale_id = p_resale_batch_id;

Line: 447

   update ozf_resale_lines_int
   set dispute_code = null
   where resale_batch_id = p_resale_batch_id
   and status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;

Line: 453

   update ozf_resale_lines_int
   set status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
   where resale_batch_id = p_resale_batch_id
   and status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
   and tracing_flag = 'T';

Line: 469

   OZF_RESALE_COMMON_PVT.Update_Duplicates (
      p_api_version        => 1.0,
      p_init_msg_list      => FND_API.G_FALSE,
      p_commit             => FND_API.G_FALSE,
      p_validation_level   => FND_API.G_VALID_LEVEL_FULL,
      p_resale_batch_id    => p_resale_batch_id,
      p_resale_batch_type  => l_batch_type,
      p_batch_status       => l_batch_status,
      x_batch_status       => l_batch_status,
      x_return_status      => l_return_status,
      x_msg_count          => l_msg_count,
      x_msg_data           => l_msg_data);

Line: 587

      update ozf_resale_batches_all
      set status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
      where resale_batch_id = p_resale_batch_id;

Line: 870

  SELECT batch_number
  FROM ozf_resale_batches
  WHERE resale_batch_id = cv_batch_id;

Line: 1031

SELECT resale_batch_id
FROM ozf_resale_batches
WHERE report_start_date >= p_start_date
AND   report_end_date <= p_end_date
AND   status_code = 'CLOSED';

Line: 1038

SELECT resale_batch_id
FROM ozf_resale_batches
WHERE report_start_date >= p_start_date
AND   report_end_date <= p_end_date
AND   partner_cust_account_id = p_cust_account_id
AND   status_code = 'CLOSED';

Line: 1046

  SELECT resale_batch_id
  FROM ozf_resale_batches
  WHERE partner_cust_account_id = cv_cust_account_id
  AND   status_code = 'CLOSED';

Line: 1163

		UPDATE ozf_resale_batches_all
		SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
		WHERE resale_batch_id = l_batch_id_tbl(j);

Line: 1167

		UPDATE ozf_resale_batches_all
		SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_ACCRUALS
		WHERE resale_batch_id = l_batch_id_tbl(j);

Line: 1343

      l_budget_data := DBMS_XMLGEN.getXml('SELECT ''FUND'' AccrualType,
						  util.utilization_id UtilizationId,
						  util.fund_id FundId,
						  NVL(map.xref_line_id_value,oe.list_line_id) DiscountLineId,
						  util.product_id ProductId,
						  line.resale_header_id OrderId,
						  line.resale_line_id OrderLineId,
						  line.quantity ShippedQuantity,
						  (line.quantity*NVL(line.selling_price,line.purchase_price)) Revenue,
						  NVL(util.plan_curr_amount,0) AccrualAmount
					   FROM   ozf_resale_lines_all line, ozf_funds_utilized_all_b util,
						  ozf_offers off, ozf_resale_adjustments_all oe,
						  ozf_xref_map map
					   WHERE  line.resale_line_id = util.object_id
					     AND  util.object_type = ''TP_ORDER''
					     AND  util.request_id = fnd_global.conc_request_id
					     AND  util.plan_type = ''OFFR''
					     AND  util.plan_id = off.qp_list_header_id
					     AND  util.price_adjustment_id = oe.resale_adjustment_id
					     AND  util.gl_posted_flag IN (''Y'', ''X'')
					     AND  oe.list_line_id = map.list_line_id(+)
					     AND  NVL(off.budget_offer_yn, ''N'') = ''Y''', 0);

Line: 1366

      l_offer_data := DBMS_XMLGEN.getXml('SELECT ''OFFR'' AccrualType,
						 util.utilization_id UtilizationId,
						 util.plan_id PromotionId,
						 util.fund_id FundId,
						 NVL(map.xref_line_id_value,oe.list_line_id) DiscountLineId,
						 util.product_id ProductId,
						 line.resale_header_id OrderId,
						 line.resale_line_id OrderLineId,
						 util.cust_account_id CustomerId,
						 line.quantity ShippedQuantity,
						 (line.quantity*NVL(line.selling_price,line.purchase_price)) Revenue,
						 NVL(util.plan_curr_amount,0) AccrualAmount
					  FROM   ozf_resale_lines_all line, ozf_funds_utilized_all_b util,
						 ozf_offers off, ozf_resale_adjustments_all oe,
						 ozf_xref_map map
					  WHERE  line.resale_line_id = util.object_id
					    AND  util.object_type = ''TP_ORDER''
					    AND  util.request_id = fnd_global.conc_request_id
					    AND  util.plan_type = ''OFFR''
					    AND  util.plan_id = off.qp_list_header_id
					    AND  util.price_adjustment_id = oe.resale_adjustment_id
					    AND  util.gl_posted_flag = ''Y''
					    AND  oe.list_line_id = map.list_line_id(+)
					    AND  NVL(off.budget_offer_yn, ''N'') = ''N''', 0);

Line: 1495

            DELETE FROM ozf_resale_lines_int_all a
            WHERE a.resale_batch_id IN (
               SELECT b.resale_batch_id
               FROM ozf_resale_batches b
               WHERE b.purge_flag IS NULL
               AND   b.status_code = 'CLOSED'
            );

Line: 1503

            UPDATE ozf_resale_batches
            SET purge_flag = 'T'
            WHERE purge_flag IS NULL
            AND status_code = 'CLOSED';

Line: 1513

            DELETE FROM ozf_resale_lines_int_all a
            WHERE a.data_source_code = p_data_source_code
            AND a.resale_batch_id IN (
               SELECT b.resale_batch_id
               FROM ozf_resale_batches b
               WHERE b.purge_flag IS NULL
               AND   b.status_code = 'CLOSED'
               AND   b.data_source_code = p_data_source_code
            );

Line: 1523

            UPDATE ozf_resale_batches
            SET purge_flag = 'T'
            WHERE purge_flag IS NULL
            AND status_code = 'CLOSED'
            AND data_source_code = p_data_source_code;

Line: 1538

            DELETE FROM ozf_resale_lines_int_all a
            WHERE a.resale_batch_id IN (
               SELECT b.resale_batch_id
               FROM ozf_resale_batches b
               WHERE b.purge_flag IS NULL
               AND   b.resale_batch_id = p_resale_batch_id
               AND   b.status_code NOT IN ('CLOSED', 'PROCESSING', 'PENDING_PAYMENT')
            );

Line: 1547

            DELETE FROM ozf_resale_batches_all
            WHERE resale_batch_id = p_resale_batch_id
            AND   status_code NOT IN ('CLOSED', 'PROCESSING', 'PENDING_PAYMENT');

Line: 1556

            DELETE FROM ozf_resale_lines_int_all a
            WHERE a.data_source_code = p_data_source_code
            AND a.resale_batch_id IN (
               SELECT b.resale_batch_id
               FROM ozf_resale_batches b
               WHERE b.purge_flag IS NULL
               AND   b.data_source_code = p_data_source_code
               AND   b.resale_batch_id = p_resale_batch_id
            );

Line: 1566

            DELETE FROM ozf_resale_batches_all
            WHERE resale_batch_id = p_resale_batch_id
            AND   data_source_code = p_data_source_code
            AND   status_code NOT IN ('CLOSED', 'PROCESSING', 'PENDING_PAYMENT');