6: max_id NUMBER;
7: BEGIN
8: SELECT MAX(ams.customer_trx_id)
9: INTO max_id
10: FROM gl_memo_sta ams
11: WHERE ams.updated_flag = 'Y';
12: /* initialize the table if there are no updated values in it. */
13:
14: IF max_id IS NULL THEN
11: WHERE ams.updated_flag = 'Y';
12: /* initialize the table if there are no updated values in it. */
13:
14: IF max_id IS NULL THEN
15: INSERT INTO gl_memo_sta (customer_trx_id, updated_flag)
16: VALUES (p_customer_trx_id, 'Y');
17: RETURN;
18: END IF;
19: /* dbms_output.put_line('max='||to_char(max_id)||' p='||to_char(p_customer_trx_id)); */
17: RETURN;
18: END IF;
19: /* dbms_output.put_line('max='||to_char(max_id)||' p='||to_char(p_customer_trx_id)); */
20: IF (p_customer_trx_id > max_id) THEN
21: UPDATE gl_memo_sta ams
22: SET ams.customer_trx_id = p_customer_trx_id
23: WHERE ams.updated_flag = 'Y';
24: END IF;
25: END update_state;
43: /* Bug 2403594. Insert customer_trx_ids of new memos */
44:
45: SELECT nvl(MAX(interface_date),TO_DATE(2440589,'J'))
46: INTO t_interface_date
47: FROM gl_memo_sta;
48:
49: INSERT INTO gl_memo_sta
50: (customer_trx_id, updated_flag, interface_date)
51: (
45: SELECT nvl(MAX(interface_date),TO_DATE(2440589,'J'))
46: INTO t_interface_date
47: FROM gl_memo_sta;
48:
49: INSERT INTO gl_memo_sta
50: (customer_trx_id, updated_flag, interface_date)
51: (
52: SELECT
53: distinct rct.customer_trx_id,'N', NULL
60: AND rct.last_update_date >= trunc(t_interface_date)
61: AND UPPER(rctt.attribute10) = 'YES'
62: AND NOT EXISTS (
63: SELECT 1
64: FROM gl_memo_sta s
65: WHERE s.customer_trx_id = rct.customer_trx_id)
66: );
67:
68: COMMIT;
80: SELECT rct.customer_trx_id,
81: rctt.type
82: FROM ra_customer_trx_all rct,
83: ra_cust_trx_types_all rctt,
84: gl_memo_sta ams
85: WHERE rct.customer_trx_id = ams.customer_trx_id
86: AND rct.customer_trx_id > last_trx_id
87: AND rct.cust_trx_type_id = rctt.cust_trx_type_id
88: AND rctt.type IN ('CM', 'DM')
103: last_trx_id := t_customer_trx_id1;
104:
105: /* Bug 2403594. Mark transactions */
106:
107: UPDATE gl_memo_sta ams
108: SET updated_flag = 'Y',
109: interface_date = sysdate
110: WHERE customer_trx_id = t_customer_trx_id1;
111:
122: error_status OUT NOCOPY NUMBER
123: ) IS
124: BEGIN
125: /* Bug 2403594 */
126: UPDATE gl_memo_sta ams
127: SET updated_flag = 'N', interface_date = NULL
128: WHERE customer_trx_id = t_customer_trx_id;
129: EXCEPTION
130: WHEN OTHERS THEN