APPS.PMI_LOT_GENEALOGY_PKG SQL Statements

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

Line: 28

  SELECT
      gen1.product_item_id, gen1.product_lot_id,
      gen2.ingred_item_id, gen2.ingred_lot_id
  FROM
      pmi_lot_genealogy gen1, pmi_lot_genealogy gen2,
      (
        SELECT
          product.item_id product_item_id, product.lot_id product_lot_id,
          ingred.item_id ingred_item_id,   ingred.lot_id ingred_lot_id
        FROM
          ic_tran_pnd product, ic_tran_pnd ingred
        WHERE
            product.doc_type = 'PROD'
        AND product.doc_id = cp_doc_id
        AND product.line_type in (1,2)
        AND product.completed_ind = 1
        AND product.lot_id <> 0
        AND ingred.doc_type = 'PROD'
        AND ingred.doc_id = cp_doc_id
        AND ingred.completed_ind = 1
        AND ingred.line_type = -1
        AND ingred.lot_id <> 0
        AND ingred.lot_id <> product.lot_id
        GROUP BY
          product.item_id, product.lot_id,
          ingred.item_id,  ingred.lot_id
        HAVING
          SUM(ingred.trans_qty) <> 0
        OR  SUM(product.trans_qty) <> 0
      ) bom
  WHERE
      gen1.ingred_lot_id = bom.product_lot_id
  AND gen2.product_lot_id = bom.ingred_lot_id;

Line: 72

            SELECT MIN(trans_date) into lv_date
            FROM ic_tran_vw1
            WHERE doc_type = 'PROD'
            AND completed_ind = 1
            AND lot_id <> 0;

Line: 116

owing select.  Each instance of a given pair should be recorded in the associati
on_count.

*/

BEGIN

  FOR gen_rec IN doc_genealogy(pv_doc_id)
  LOOP

    BEGIN
      INSERT INTO pmi_lot_genealogy (
	product_item_id,
	product_lot_id,
	ingred_item_id,
	ingred_lot_id,
	association_count)
      VALUES (
	gen_rec.product_item_id,
	gen_rec.product_lot_id,
	gen_rec.ingred_item_id,
	gen_rec.ingred_lot_id,
	1);

Line: 142

	  UPDATE pmi_lot_genealogy
	  SET association_count = association_count + 1
	  WHERE product_lot_id = gen_rec.product_lot_id
	    AND ingred_lot_id = gen_rec.ingred_lot_id;

Line: 175

 are selected as "current".  This should eliminates the possibility that the
granularity of sysdate will not be sufficient to be be sure of which transactions
are included in what refresh.

*/

BEGIN

  FOR gen_rec IN doc_genealogy(pv_doc_id)
  LOOP
    DELETE FROM pmi_lot_genealogy
    WHERE product_lot_id = gen_rec.product_lot_id
    AND ingred_lot_id = gen_rec.ingred_lot_id
    AND association_count = 1;

Line: 192

      UPDATE pmi_lot_genealogy
      SET association_count = association_count - 1
      WHERE product_lot_id = gen_rec.product_lot_id
      AND ingred_lot_id = gen_rec.ingred_lot_id;

Line: 207

    SELECT item_id, lot_id
    FROM ic_lots_mst
    WHERE lot_id <> 0
    AND creation_date BETWEEN NVL(pp_last_refresh_date, creation_date) AND pp_current_refresh_date;

Line: 215

            INSERT INTO pmi_lot_genealogy
            (product_item_id, product_lot_id, ingred_item_id, ingred_lot_id, association_count)
		VALUES (nl.item_id, nl.lot_id, nl.item_id, nl.lot_id, 1);

Line: 220

                NULL; -- no need to insert

Line: 229

      Select the doc_id's of all batches that have had at least one transaction
      update between the last refresh date and the current refresh date.
    */
    CURSOR updated_docs(cp_last_refresh_date DATE, cp_current_refresh_date DATE)
    IS
    SELECT DISTINCT doc_id
    FROM ic_tran_pnd t
    WHERE t.last_update_date BETWEEN NVL(cp_last_refresh_date, t.last_update_date)
                             AND cp_current_refresh_date
    AND t.doc_type = 'PROD'
    AND t.completed_ind = 1
    AND t.lot_id <> 0;

Line: 250

    FOR doc IN updated_docs(lv_last_refresh_date, lv_current_refresh_date)
    LOOP
	remove_prior_genealogy(doc.doc_id);

Line: 255

        SELECT TABLE_OWNER INTO l_table_owner
        FROM USER_SYNONYMS
        WHERE SYNONYM_NAME = 'PMI_LOT_GENEALOGY';