APPS.INV_SHORTCHECKPROCESSTRX_PVT SQL Statements

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

Line: 38

  CURSOR L_SelectTrx_csr (l_days in number )IS
	SELECT /*+ INDEX (mmt mtl_material_transactions_n5) */
               organization_id,
	       inventory_item_id
	  FROM mtl_material_transactions
	 WHERE shortage_process_code = 1
           and transaction_date > (sysdate - l_days )
	GROUP BY organization_id,
                 inventory_item_id;

Line: 48

  L_SelectTrx_rec	L_SelectTrx_csr%ROWTYPE;

Line: 55

  SELECT primary_quantity
  FROM   mtl_material_transactions
  WHERE  inventory_item_id 	= p_inventory_item_id
  AND    organization_id   	= p_organization_id
  AND    shortage_process_code 	= 1
  ORDER BY transaction_date DESC,
	   primary_quantity;

Line: 67

  PROCEDURE UpdateTrx ( p_organization_id	 IN NUMBER,
		        p_inventory_item_id	 IN NUMBER,
			p_shortage_process_code  IN NUMBER )
  IS
  BEGIN
    UPDATE mtl_material_transactions
    SET    shortage_process_code = p_shortage_process_code
    WHERE  organization_id   	 = p_organization_id
    AND    inventory_item_id 	 = p_inventory_item_id
    AND    shortage_process_code = 1;

Line: 77

  END UpdateTrx;

Line: 83

    OPEN L_SelectTrx_csr(l_days);

Line: 86

    FETCH L_SelectTrx_csr INTO L_SelectTrx_rec;

Line: 87

    EXIT WHEN L_SelectTrx_csr%NOTFOUND;

Line: 92

        OPEN L_LastTrx_csr ( L_SelectTrx_rec.inventory_item_id,
			     L_SelectTrx_rec.organization_id );

Line: 104

  		p_organization_id	=> L_SelectTrx_rec.organization_id,
  		p_inventory_item_id	=> L_SelectTrx_rec.inventory_item_id,
  		p_comp_att_qty_flag	=> 1,
		p_primary_quantity	=> L_LastTrx_rec.primary_quantity,
  		x_seq_num		=> L_seq_num,
  		x_check_result		=> L_check_result
  		);

Line: 115

       	   UpdateTrx ( L_SelectTrx_rec.organization_id,
            	       L_SelectTrx_rec.inventory_item_id,
                       3 );

Line: 132

  	  	p_organization_id	=> L_SelectTrx_rec.organization_id,
	  	p_inventory_item_id	=> L_SelectTrx_rec.inventory_item_id,
  	  	p_seq_num		=> L_seq_num,
		p_notification_type	=> 'R'
          	);

Line: 140

       		 UpdateTrx ( L_SelectTrx_rec.organization_id,
                   	     L_SelectTrx_rec.inventory_item_id,
                   	     3 );

Line: 148

    		 -- Update checked rows in material transactions table
    		 UpdateTrx ( L_SelectTrx_rec.organization_id,
			     L_SelectTrx_rec.inventory_item_id,
			     2 );

Line: 154

    	      -- Update checked rows in material transactions table
    	      UpdateTrx ( L_SelectTrx_rec.organization_id,
		          L_SelectTrx_rec.inventory_item_id,
			  2 );

Line: 177

       UpdateTrx ( L_SelectTrx_rec.organization_id,
                   L_SelectTrx_rec.inventory_item_id,
                   3 );

Line: 200

    CLOSE L_SelectTrx_csr;