APPS.GMF_MEMO_INTERFACE SQL Statements

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

Line: 5

  PROCEDURE update_state (p_customer_trx_id IN NUMBER) IS
    max_id	NUMBER;

Line: 8

    SELECT MAX(ams.customer_trx_id)
    INTO   max_id
    FROM   gl_memo_sta ams
    WHERE  ams.updated_flag = 'Y';

Line: 12

    /* initialize the table if there are no updated values in it. */

    IF max_id IS NULL THEN
      INSERT INTO gl_memo_sta (customer_trx_id, updated_flag)
      VALUES (p_customer_trx_id, 'Y');

Line: 21

      UPDATE gl_memo_sta ams
      SET    ams.customer_trx_id = p_customer_trx_id
      WHERE  ams.updated_flag = 'Y';

Line: 25

  END update_state;

Line: 43

	/* Bug 2403594. Insert customer_trx_ids of new memos */

	SELECT	nvl(MAX(interface_date),TO_DATE(2440589,'J'))
	INTO	t_interface_date
	FROM	gl_memo_sta;

Line: 49

	INSERT INTO gl_memo_sta
	(customer_trx_id, updated_flag, interface_date)
	(
		SELECT
			distinct rct.customer_trx_id,'N', NULL
		FROM 	ra_customer_trx_all rct,
			ra_cust_trx_types_all rctt
		WHERE
			rct.cust_trx_type_id = rctt.cust_trx_type_id
		AND	rctt.TYPE IN ('CM', 'DM')
		AND	UPPER(rct.complete_flag) = 'Y'
		AND	rct.last_update_date >= trunc(t_interface_date)
		AND	UPPER(rctt.attribute10) = 'YES'
		AND	NOT EXISTS (
				SELECT 1
				FROM gl_memo_sta s
				WHERE s.customer_trx_id = rct.customer_trx_id)
	);

Line: 75

		and to select first row from the returned set  */

	SELECT	customer_trx_id, type
        INTO	t_customer_trx_id1, t_trx_type1
        FROM   (
		SELECT rct.customer_trx_id,
			rctt.type
		FROM	ra_customer_trx_all rct,
			ra_cust_trx_types_all rctt,
			gl_memo_sta ams
		WHERE  rct.customer_trx_id = ams.customer_trx_id
		AND    rct.customer_trx_id > last_trx_id
		AND    rct.cust_trx_type_id = rctt.cust_trx_type_id
		AND    rctt.type IN ('CM', 'DM')
		/* B1043050 changed upper to lower and 'N' to 'n' */
		AND    LOWER(ams.updated_flag) = 'n'
		ORDER  BY 1
		)
	WHERE    ROWNUM = 1;

Line: 107

	UPDATE	gl_memo_sta ams
	SET	updated_flag = 'Y',
		interface_date = sysdate
	WHERE	customer_trx_id	= t_customer_trx_id1;

Line: 120

  PROCEDURE insert_error (
        t_customer_trx_id       IN      NUMBER,
        error_status            OUT     NOCOPY NUMBER
    ) IS
  BEGIN
	/* Bug 2403594 */
	UPDATE	gl_memo_sta ams
	SET	updated_flag = 'N', interface_date = NULL
	WHERE	customer_trx_id	= t_customer_trx_id;

Line: 132

  END insert_error;

Line: 145

    SELECT NVL(rctt.attribute10, ' ')
    FROM   ra_cust_trx_types_all	rctt,
	   ra_customer_trx_all	rct
    WHERE  rct.customer_trx_id	= t_customer_trx_id
    AND    rct.cust_trx_type_id	= rctt.cust_trx_type_id;

Line: 153

    SELECT msi.segment1
    FROM   mtl_system_items msi
    WHERE  msi.inventory_item_id=t_inventory_item_id;

Line: 160

    SELECT dualum_ind, lot_ctl
    FROM	ic_item_mst
    WHERE 	item_no = i_item_no;