Topic: SQL*Plus and PL/SQL >> Urgent
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| pran |
Posted: Jan 08, 2008 05:42:04 AM |
Total Post: 3
Joined: Jan, 2008
|
Hi All,
i m using nested cursors. one static and other using dynamic query
on the basis of the records selected in the dynamic cursor the records are updated
but at the end of the program few records are not getting updated.
i dont know the reson probably u guys can help me.
thanks in advance
i m putting whole code here please help me out
CREATE OR REPLACE PROCEDURE SP_PROCESS_997_auto IS
lv_gs02 epm_997_inb.gs02%TYPE;
lv_ak101 epm_997_inb.ak101%TYPE;
lv_ak102 epm_997_inb.ak102%TYPE;
lv_ak201 epm_997_inb_ak2.ak201%TYPE;
lv_ak202 epm_997_inb_ak2.ak202%TYPE;
lv_ak501 epm_997_inb_ak2.ak501%TYPE;
lv_ak502 epm_997_inb_ak2.ak502%TYPE;
lv_ak503 epm_997_inb_ak2.ak503%TYPE;
lv_ak504 epm_997_inb_ak2.ak504%TYPE;
lv_ak505 epm_997_inb_ak2.ak505%TYPE;
lv_ak506 epm_997_inb_ak2.ak506%TYPE;
lv_ak901 epm_997_inb.ak901%TYPE;
lv_ak905 epm_997_inb.ak905%TYPE;
lv_ak906 epm_997_inb.ak906%TYPE;
lv_ak907 epm_997_inb.ak907%TYPE;
lv_ak908 epm_997_inb.ak908%TYPE;
lv_ak909 epm_997_inb.ak909%TYPE;
lv_source_timestamp epm_997_inb.source_timestamp%TYPE;
lv_pk_997 epm_997_inb.pk_997%TYPE;
lv_pk_997_ak2 epm_997_inb_ak2.pk_997_ak2%TYPE;
lv_base_pk epm_650_01.pk_650_01%TYPE;
lv_base_transaction_id polaris2_valid_transactions.transaction_id%TYPE;
lv_base_t997_ack_code epm_650_01.t997_ack_code%TYPE;
lv_base_t997_error_code epm_650_01.t997_error_code%TYPE;
lv_base_t997_source_timestamp epm_650_01.t997_source_timestamp%TYPE;
lv_t997_error_code epm_650_01.t997_error_code%TYPE;
lv_direction VARCHAR2(1);
lv_transaction_id polaris2_valid_transactions.transaction_id%TYPE;
lv_valid_transaction_id polaris2_valid_transactions.transaction_id%TYPE;
lv_997_count NUMBER;
lv_tmp_pk_997 epm_997_inb.pk_997%TYPE;
type base_table_csr_type IS REF CURSOR;
base_table_csr base_table_csr_type;
lv_cursor_def VARCHAR2(8000);
lv_run_number transaction_control.run_number%TYPE;
lv_ora_error_code transaction_error_log.ora_error_code%TYPE;
lv_ora_error_msg transaction_error_log.ora_error_msg%TYPE;
lv_error_message transaction_error_log.error_message%TYPE;
lv_warning_flag VARCHAR2(1);
lv_specific_flag VARCHAR2(1);
lv_initial_flag VARCHAR2(1);
lv_found_flag VARCHAR2(1);
CURSOR epm_997_csr IS
SELECT a.ak102, b.ak201, b.ak202, b.ak501, b.ak502, b.ak503, b.ak504,
b.ak505, b.ak506, a.source_timestamp, a.pk_997, b.pk_997_ak2, 'I'
FROM epm_997_inb a, epm_997_inb_ak2 b
WHERE a.insert_timestamp >= to_date(to_char(sysdate,'MM-DD-YYYY')||' 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND a.insert_timestamp <= to_date(to_char(sysdate,'MM-DD-YYYY')||' 23:59:59','MM-DD-YYYY HH24:MI:SS')
AND a.pk_997 = b.pk_997
UNION
SELECT c.ak102, d.ak201, d.ak202, d.ak501, d.ak502, d.ak503, d.ak504,
d.ak505, d.ak506, c.source_timestamp, c.pk_997, d.pk_997_ak2, 'O'
FROM epm_997_outb c, epm_997_outb_ak2 d
WHERE c.insert_timestamp >= to_date(to_char(sysdate,'MM-DD-YYYY')||' 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND c.insert_timestamp <= to_date(to_char(sysdate,'MM-DD-YYYY')||' 23:59:59','MM-DD-YYYY HH24:MI:SS')
AND c.pk_997 = d.pk_997
ORDER BY 10;
CURSOR epm_997_without_ak2_csr IS
SELECT a.gs02, a.ak101, a.ak102, a.ak901, a.ak905, a.ak906, a.ak907, a.ak908, a.ak909,
a.source_timestamp, a.pk_997, 'I'
FROM epm_997_inb a
WHERE NOT EXISTS (SELECT b.pk_997
FROM epm_997_inb_ak2 b
WHERE b.pk_997 = a.pk_997)
AND a.insert_timestamp >= to_date(to_char(sysdate,'MM-DD-YYYY')||' 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND a.insert_timestamp <= to_date(to_char(sysdate,'MM-DD-YYYY')||' 23:59:59','MM-DD-YYYY HH24:MI:SS')
UNION
SELECT c.gs02, c.ak101, c.ak102, c.ak901, c.ak905, c.ak906, c.ak907, c.ak908, c.ak909,
c.source_timestamp, c.pk_997, 'O'
FROM epm_997_outb c
WHERE NOT EXISTS (SELECT d.pk_997
FROM epm_997_outb_ak2 d
WHERE d.pk_997 = c.pk_997)
AND c.insert_timestamp >= to_date(to_char(sysdate,'MM-DD-YYYY')||' 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND c.insert_timestamp <= to_date(to_char(sysdate,'MM-DD-YYYY')||' 23:59:59','MM-DD-YYYY HH24:MI:SS')
ORDER BY 5;
CURSOR valid_tran_csr IS
SELECT transaction_id
FROM polaris2_valid_transactions
WHERE transaction_id LIKE lv_transaction_id || '%'
AND UPPER(inb_outb_fl) <> UPPER(lv_direction);
CURSOR unmatched_csr IS
SELECT pk_997
FROM epm_997_unmatched
WHERE pk_997 = lv_pk_997
AND pk_997_ak2 = lv_pk_997_ak2
AND source_timestamp = lv_source_timestamp;
BEGIN
lv_warning_flag := 'N';
-- Log the stored procedure execution
SELECT run_number_seq.NEXTVAL INTO lv_run_number FROM dual;
INSERT INTO transaction_control (run_number, program_name, program_status, program_start_timestamp)
VALUES (lv_run_number, 'SP_PROCESS_997', 'RUNNING', SYSDATE);
COMMIT;
-- Process each Inbound and Outbound 997 that contains the AK2 segment
OPEN epm_997_csr;
FETCH epm_997_csr INTO lv_ak102, lv_ak201, lv_ak202, lv_ak501, lv_ak502, lv_ak503, lv_ak504, lv_ak505, lv_ak506, lv_source_timestamp, lv_pk_997, lv_pk_997_ak2, lv_direction;
LOOP
EXIT WHEN epm_997_csr%NOTFOUND;
lv_specific_flag := 'Y';
-- Ensure that critical 997 values are not NULL
IF lv_ak102 IS NOT NULL AND -- Group Control Number
lv_ak202 IS NOT NULL AND -- Transaction Set Control Number
lv_ak201 IS NOT NULL THEN -- Transaction Set Identifier
-- Convert AK201 into the base transaction table name
lv_transaction_id := NULL;
IF UPPER(SUBSTR(lv_ak201,1,1)) = 'A' THEN -- 814 series
lv_transaction_id := '814_' || UPPER(SUBSTR(lv_ak201,2,2));
ELSIF UPPER(SUBSTR(lv_ak201,1,1)) = 'B' THEN -- 867 series
lv_transaction_id := '867_' || UPPER(SUBSTR(lv_ak201,2,2));
ELSIF UPPER(SUBSTR(lv_ak201,1,1)) = 'C' THEN -- 810 series
lv_transaction_id := '810_' || UPPER(SUBSTR(lv_ak201,2,2));
ELSIF UPPER(SUBSTR(lv_ak201,1,1)) = 'D' THEN -- 820 series
lv_transaction_id := '820_' || UPPER(SUBSTR(lv_ak201,2,2));
ELSIF UPPER(SUBSTR(lv_ak201,1,1)) = 'E' THEN -- 824 series
lv_transaction_id := '824';
ELSIF UPPER(SUBSTR(lv_ak201,1,1)) = 'F' THEN -- 650 series
lv_transaction_id := '650_' || UPPER(SUBSTR(lv_ak201,2,2));
ELSIF UPPER(lv_ak201) = '650' THEN -- 650 series
lv_transaction_id := '650';
lv_specific_flag := 'N';
ELSIF UPPER(lv_ak201) = '810' THEN -- 810_02
lv_transaction_id := '810_02';
ELSIF UPPER(lv_ak201) = '814' THEN -- 814 series
lv_transaction_id := '814';
lv_specific_flag := 'N';
ELSIF UPPER(lv_ak201) = '820' THEN -- 820_02
lv_transaction_id := '820_02';
ELSIF UPPER(lv_ak201) = '824' THEN -- 824
lv_transaction_id := '824';
ELSIF UPPER(lv_ak201) = '867' THEN -- 867 series
lv_transaction_id := '867';
lv_specific_flag := 'N';
ELSE
lv_warning_flag := 'Y';
lv_error_message := 'Transaction not processed because an unknown AK201 value (' || lv_ak201 ||
') was found for primary keys: PK_997 = ' || TO_CHAR(lv_pk_997) || ', PK_997_AK2 = ' ||
TO_CHAR(lv_pk_997_ak2) || '.';
INSERT INTO transaction_error_log (run_number, error_log_id, error_message, error_timestamp)
VALUES (lv_run_number, error_log_id_seq.NEXTVAL, lv_error_message, SYSDATE);
END IF;
-- Do not process the 997 transaction if AK201 contains an unknown value
IF lv_transaction_id IS NOT NULL THEN
-- Build a dynamic query to lookup the original transaction(s) in the P2 base tables
lv_cursor_def := NULL;
IF lv_specific_flag = 'Y' THEN
lv_cursor_def := 'SELECT pk_' || TRIM(lv_transaction_id) || ', ''' || TRIM(lv_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND st02 = ''' || lv_ak202 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
ELSE
lv_initial_flag := 'Y';
OPEN valid_tran_csr;
FETCH valid_tran_csr INTO lv_valid_transaction_id;
LOOP
EXIT WHEN valid_tran_csr%NOTFOUND;
IF lv_initial_flag = 'Y' THEN
lv_initial_flag := 'N';
lv_cursor_def := 'SELECT pk_' || TRIM(lv_valid_transaction_id) || ', ''' || TRIM(lv_valid_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_valid_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND st02 = ''' || lv_ak202 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
ELSE
lv_cursor_def := lv_cursor_def || ' UNION SELECT pk_' || TRIM(lv_valid_transaction_id) || ', ''' || TRIM(lv_valid_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_valid_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND st02 = ''' || lv_ak202 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
END IF;
FETCH valid_tran_csr INTO lv_valid_transaction_id;
END LOOP;
CLOSE valid_tran_csr;
END IF;
-- Check to determine if there is a base table query to lookup the original transaction(s)
IF lv_cursor_def IS NOT NULL THEN
-- Concatenate the multiple 997 error codes into a single value
lv_t997_error_code := NULL;
IF lv_ak502 IS NOT NULL OR
lv_ak503 IS NOT NULL OR
lv_ak504 IS NOT NULL OR
lv_ak505 IS NOT NULL OR
lv_ak506 IS NOT NULL THEN
IF lv_ak502 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak502;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak502;
END IF;
END IF;
IF lv_ak503 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak503;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak503;
END IF;
END IF;
IF lv_ak504 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak504;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak504;
END IF;
END IF;
IF lv_ak505 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak505;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak505;
END IF;
END IF;
IF lv_ak506 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak506;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak506;
END IF;
END IF;
END IF;
-- Count the number of 997 transactions for the current GS06 and ST02
lv_997_count := 0;
IF lv_direction = 'I' THEN
SELECT COUNT(*) INTO lv_997_count FROM epm_997_inb a, epm_997_inb_ak2 b where a.pk_997 = b.pk_997 and a.ak102 = lv_ak102 and b.ak201 = lv_ak201 and b.ak202 = lv_ak202;
ELSE
SELECT COUNT(*) INTO lv_997_count FROM epm_997_outb a, epm_997_outb_ak2 b where a.pk_997 = b.pk_997 and a.ak102 = lv_ak102 and b.ak201 = lv_ak201 and b.ak202 = lv_ak202;
END IF;
-- Lookup the original transaction(s) in the appropriate P2 base tables
lv_found_flag := 'N';
OPEN base_table_csr FOR lv_cursor_def;
FETCH base_table_csr INTO lv_base_pk, lv_base_transaction_id, lv_base_t997_ack_code, lv_base_t997_error_code, lv_base_t997_source_timestamp;
LOOP
EXIT WHEN base_table_csr%NOTFOUND;
lv_found_flag := 'Y';
-- Update the 997 columns in the base table
IF NVL(lv_base_t997_ack_code, '!') IN ('A', 'E') THEN
-- Set the 997 Multiple Flag to Y if an ACK already exists
IF lv_997_count > 1 THEN
EXECUTE IMMEDIATE('UPDATE epm_' || TRIM(lv_base_transaction_id) || ' SET t997_ack_code = ''' || lv_base_t997_ack_code || ''', t997_source_timestamp = TO_DATE(''' || TO_CHAR(lv_base_t997_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS''), t997_error_code = ''' || lv_base_t997_error_code || ''', t997_multiple_fl = ''Y'' WHERE pk_' || TRIM(lv_base_transaction_id) || ' = ' || TO_CHAR(lv_base_pk));
ELSE
EXECUTE IMMEDIATE('UPDATE epm_' || TRIM(lv_base_transaction_id) || ' SET t997_ack_code = ''' || lv_base_t997_ack_code || ''', t997_source_timestamp = TO_DATE(''' || TO_CHAR(lv_base_t997_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS''), t997_error_code = ''' || lv_base_t997_error_code || ''', t997_multiple_fl = NULL WHERE pk_' || TRIM(lv_base_transaction_id) || ' = ' || TO_CHAR(lv_base_pk));
END IF;
ELSE
IF lv_997_count > 1 THEN
EXECUTE IMMEDIATE('UPDATE epm_' || TRIM(lv_base_transaction_id) || ' SET t997_ack_code = ''' || lv_ak501 || ''', t997_source_timestamp = TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS''), t997_error_code = ''' || lv_t997_error_code || ''', t997_multiple_fl = ''Y'' WHERE pk_' || TRIM(lv_base_transaction_id) || ' = ' || TO_CHAR(lv_base_pk));
ELSE
EXECUTE IMMEDIATE('UPDATE epm_' || TRIM(lv_base_transaction_id) || ' SET t997_ack_code = ''' || lv_ak501 || ''', t997_source_timestamp = TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS''), t997_error_code = ''' || lv_t997_error_code || ''', t997_multiple_fl = NULL WHERE pk_' || TRIM(lv_base_transaction_id) || ' = ' || TO_CHAR(lv_base_pk));
END IF;
END IF;
FETCH base_table_csr INTO lv_base_pk, lv_base_transaction_id, lv_base_t997_ack_code, lv_base_t997_error_code, lv_base_t997_source_timestamp;
END LOOP;
CLOSE base_table_csr;
IF lv_found_flag = 'N' THEN
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, lv_pk_997_ak2, lv_source_timestamp, lv_direction, lv_ak102, lv_ak201, lv_ak202, lv_ak501, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
ELSE
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, lv_pk_997_ak2, lv_source_timestamp, lv_direction, lv_ak102, lv_ak201, lv_ak202, lv_ak501, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
ELSE
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, lv_pk_997_ak2, lv_source_timestamp, lv_direction, lv_ak102, lv_ak201, lv_ak202, lv_ak501, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
ELSE
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, lv_pk_997_ak2, lv_source_timestamp, lv_direction, lv_ak102, lv_ak201, lv_ak202, lv_ak501, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
FETCH epm_997_csr INTO lv_ak102, lv_ak201, lv_ak202, lv_ak501, lv_ak502, lv_ak503, lv_ak504, lv_ak505, lv_ak506, lv_source_timestamp, lv_pk_997, lv_pk_997_ak2, lv_direction;
END LOOP;
CLOSE epm_997_csr;
COMMIT;
-- Process each Inbound and Outbound 997 that does not contain the AK2 segment
OPEN epm_997_without_ak2_csr;
FETCH epm_997_without_ak2_csr INTO lv_gs02, lv_ak101, lv_ak102, lv_ak901, lv_ak905,
lv_ak906, lv_ak907, lv_ak908, lv_ak909, lv_source_timestamp, lv_pk_997, lv_direction;
LOOP
EXIT WHEN epm_997_without_ak2_csr%NOTFOUND;
lv_specific_flag := 'Y';
-- Ensure that critical 997 values are not NULL
IF lv_ak101 IS NOT NULL AND -- Transaction Set Identifier
lv_ak102 IS NOT NULL AND -- Group Control Number
lv_gs02 IS NOT NULL THEN -- Competitive Retailer DUNS Number
-- Convert AK101 into the base transaction table name
lv_transaction_id := NULL;
IF UPPER(lv_ak101) = 'MO' THEN -- 650 series
lv_transaction_id := '650';
lv_specific_flag := 'N';
ELSIF UPPER(lv_ak101) = 'IN' THEN -- 810_02
lv_transaction_id := '810_02';
ELSIF UPPER(lv_ak101) = 'GE' THEN -- 814 series
lv_transaction_id := '814';
lv_specific_flag := 'N';
ELSIF UPPER(lv_ak101) = 'RA' THEN -- 820_02
lv_transaction_id := '820_02';
ELSIF UPPER(lv_ak101) = 'AG' THEN -- 824
lv_transaction_id := '824';
ELSIF UPPER(lv_ak101) = 'PT' THEN -- 867 series
lv_transaction_id := '867';
lv_specific_flag := 'N';
ELSE
lv_warning_flag := 'Y';
lv_error_message := 'Transaction not processed because an unknown AK101 value (' || lv_ak101 ||
') was found for primary key: PK_997 = ' || TO_CHAR(lv_pk_997) || '.';
INSERT INTO transaction_error_log (run_number, error_log_id, error_message, error_timestamp)
VALUES (lv_run_number, error_log_id_seq.NEXTVAL, lv_error_message, SYSDATE);
END IF;
-- Do not process the 997 transaction if AK101 contains an unknown value
IF lv_transaction_id IS NOT NULL THEN
-- Build a dynamic query to lookup the original transaction(s) in the P2 base tables
lv_cursor_def := NULL;
IF lv_specific_flag = 'Y' THEN
lv_cursor_def := 'SELECT pk_' || TRIM(lv_transaction_id) || ', ''' || TRIM(lv_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND cr_duns_no = ''' || lv_gs02 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
ELSE
lv_initial_flag := 'Y';
OPEN valid_tran_csr;
FETCH valid_tran_csr INTO lv_valid_transaction_id;
LOOP
EXIT WHEN valid_tran_csr%NOTFOUND;
IF lv_initial_flag = 'Y' THEN
lv_initial_flag := 'N';
lv_cursor_def := 'SELECT pk_' || TRIM(lv_valid_transaction_id) || ', ''' || TRIM(lv_valid_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_valid_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND cr_duns_no = ''' || lv_gs02 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
ELSE
lv_cursor_def := lv_cursor_def || ' UNION SELECT pk_' || TRIM(lv_valid_transaction_id) || ', ''' || TRIM(lv_valid_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_valid_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND cr_duns_no = ''' || lv_gs02 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
END IF;
FETCH valid_tran_csr INTO lv_valid_transaction_id;
END LOOP;
CLOSE valid_tran_csr;
END IF;
-- Check to determine if there is a base table query to lookup the original transaction(s)
IF lv_cursor_def IS NOT NULL THEN
-- Concatenate the multiple 997 error codes into a single value
lv_t997_error_code := NULL;
IF lv_ak905 IS NOT NULL OR
lv_ak906 IS NOT NULL OR
lv_ak907 IS NOT NULL OR
lv_ak908 IS NOT NULL OR
lv_ak909 IS NOT NULL THEN
IF lv_ak905 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak905;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak905;
END IF;
END IF;
IF lv_ak906 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak906;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak906;
END IF;
END IF;
IF lv_ak907 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak907;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak907;
END IF;
END IF;
IF lv_ak908 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak908;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak908;
END IF;
END IF;
IF lv_ak909 IS NOT NULL THEN
IF lv_t997_error_code IS NULL THEN
lv_t997_error_code := lv_ak909;
ELSE
lv_t997_error_code := lv_t997_error_code || ',' || lv_ak909;
END IF;
END IF;
END IF;
-- Lookup the original transaction(s) in the appropriate P2 base tables
lv_found_flag := 'N';
OPEN base_table_csr FOR lv_cursor_def;
FETCH base_table_csr INTO lv_base_pk, lv_base_transaction_id, lv_base_t997_ack_code, lv_base_t997_error_code, lv_base_t997_source_timestamp;
LOOP
EXIT WHEN base_table_csr%NOTFOUND;
lv_found_flag := 'Y';
-- Update the 997 columns in the base table
IF NVL(lv_base_t997_ack_code, '!') IN ('A', 'E') THEN
NULL;
ELSE
EXECUTE IMMEDIATE('UPDATE epm_' || TRIM(lv_base_transaction_id) || ' SET t997_ack_code = ''' || lv_ak901 || ''', t997_source_timestamp = TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS''), t997_error_code = ''' || lv_t997_error_code || ''' WHERE pk_' || TRIM(lv_base_transaction_id) || ' = ' || TO_CHAR(lv_base_pk));
END IF;
FETCH base_table_csr INTO lv_base_pk, lv_base_transaction_id, lv_base_t997_ack_code, lv_base_t997_error_code, lv_base_t997_source_timestamp;
END LOOP;
CLOSE base_table_csr;
IF lv_found_flag = 'N' THEN
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, 0, lv_source_timestamp, lv_direction, lv_ak102, NULL, NULL, NULL, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
ELSE
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, 0, lv_source_timestamp, lv_direction, lv_ak102, NULL, NULL, NULL, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
ELSE
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, 0, lv_source_timestamp, lv_direction, lv_ak102, NULL, NULL, NULL, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
ELSE
-- Store the unmatched 997 transaction
OPEN unmatched_csr;
FETCH unmatched_csr INTO lv_tmp_pk_997;
IF unmatched_csr%NOTFOUND THEN
INSERT INTO epm_997_unmatched (pk_997, pk_997_ak2, source_timestamp, direction, ak102, ak201, ak202, ak501, insert_timestamp)
VALUES (lv_pk_997, 0, lv_source_timestamp, lv_direction, lv_ak102, NULL, NULL, NULL, SYSDATE);
END IF;
CLOSE unmatched_csr;
END IF;
FETCH epm_997_without_ak2_csr INTO lv_gs02, lv_ak101, lv_ak102, lv_ak901, lv_ak905,
lv_ak906, lv_ak907, lv_ak908, lv_ak909, lv_source_timestamp, lv_pk_997, lv_direction;
END LOOP;
COMMIT;
-- Log the stored procedure execution
IF lv_warning_flag = 'Y' THEN
UPDATE transaction_control SET program_status = 'WARNING', program_end_timestamp = SYSDATE
WHERE run_number = lv_run_number;
ELSE
UPDATE transaction_control SET program_status = 'COMPLETED', program_end_timestamp = SYSDATE
WHERE run_number = lv_run_number;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
lv_ora_error_code := SQLCODE;
lv_ora_error_msg := SUBSTR(SQLERRM, 1, 100);
UPDATE transaction_control SET program_status = 'ERROR', program_end_timestamp = SYSDATE
WHERE run_number = lv_run_number;
INSERT INTO transaction_error_log (run_number, error_log_id, ora_error_code, ora_error_msg, error_timestamp)
VALUES (lv_run_number, error_log_id_seq.NEXTVAL, lv_ora_error_code, lv_ora_error_msg, SYSDATE);
COMMIT;
END;
/
|
|
|
 Murtuja |
| Posted: Jan 08, 2008 08:18:12 AM |  |
|
Total Post: 857
Joined: Jan, 2006
|
Hi,
It's not at all possible.No one can spare time to read and understand your 1000 lines code.You only can better understand it and debug it.
Best Luck !!!
|
|
|
|
|
| jayesh |
| Posted: Jan 08, 2008 08:32:09 AM | |
|
Total Post: 60
Joined: Jan, 2008
|
Its better if u put the expected error part of code then only one can help u out. Its not possible to go through the entire code.
sorry...
|
|
|
|
|
| pran |
| Posted: Jan 08, 2008 08:32:36 AM | |
|
Total Post: 3
Joined: Jan, 2008
|
Hi
sorry for long code.
i will put basic idea of that
there is one cursor which fetches records
CURSOR epm_997_without_ak2_csr IS
SELECT a.gs02, a.ak101, a.ak102, a.ak901, a.ak905, a.ak906, a.ak907, a.ak908, a.ak909,
a.source_timestamp, a.pk_997, 'I'
FROM epm_997_inb a
WHERE NOT EXISTS (SELECT b.pk_997
FROM epm_997_inb_ak2 b
WHERE b.pk_997 = a.pk_997)
AND a.insert_timestamp >= to_date('1/1/2008|' 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND a.insert_timestamp <= to_date('1/1/2008' 23:59:59','MM-DD-YYYY HH24:MI:SS')
UNION
SELECT c.gs02, c.ak101, c.ak102, c.ak901, c.ak905, c.ak906, c.ak907, c.ak908, c.ak909,
c.source_timestamp, c.pk_997, 'O'
FROM epm_997_outb c
WHERE NOT EXISTS (SELECT d.pk_997
FROM epm_997_outb_ak2 d
WHERE d.pk_997 = c.pk_997)
AND c.insert_timestamp >= to_date('1/1/2008' 00:00:00','MM-DD-YYYY HH24:MI:SS')
AND c.insert_timestamp <= to_date('1/1/2008' 23:59:59','MM-DD-YYYY HH24:MI:SS')
ORDER BY 5;
and based on some condition with these the records are selected frombase table using dynamic query
SELECT pk_' || TRIM(lv_transaction_id) || ', ''' || TRIM(lv_transaction_id) || ''', t997_ack_code, t997_error_code, t997_source_timestamp FROM epm_' || TRIM(lv_transaction_id) || ' WHERE gs06 = ' || lv_ak102 || ' AND cr_duns_no = ''' || lv_gs02 || ''' AND source_timestamp <= TO_DATE(''' || TO_CHAR(lv_source_timestamp, 'MM-DD-YYYY HH24:MI:SS') || ''',''MM-DD-YYYY HH24:MI:SS'')';
now the first cursor is opened (epm_997_without_ak2_csr) and looped and than second cursor is opened using ref cursor
in this logic base table records are updated which are selected through dynamic cursor
but this cursor is having some problem is not able to select or fetch all the records needed. conditions in where clause is right
no exceptions are raised .............please help
|
|
|
|
|
| Murtuja |
| Posted: Jan 08, 2008 08:38:54 AM | |
|
Total Post: 857
Joined: Jan, 2006
|
You can put dbms_output at various locations in this procedure and check whether you are getting appropriate data,whether data processed appropriatly.
|
|
|
|
|
| pran |
| Posted: Jan 14, 2008 06:58:16 AM | |
|
Total Post: 3
Joined: Jan, 2008
|
hi guys,
Please reply to this......... i cannot debug it inproduction environment its working fine in development.
Thanks in advance!
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 327 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
