Hello Experts,
We have a below package procedure with that we are trying to update the table based on different if..else condition but its not updating the records based on condition. its updating the records based on last condition. For example, for first condition if hpp_part = 'Y' and for second condition prod_type_abc = 'C' then table should be updated based on both the conditions but currently its updating based on second/Last conditions,
Package Spec:
CREATE OR REPLACE PACKAGE cct_test AS
TYPE cct_queue_tbl_type IS
TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
g_queuetypeelem cct_queue_tbl_type;
g_prodtypeelem cct_prod_type;
g_chemicalarray cct_queue_prod_class_tbl_type;
g_lightningarray cct_queue_prod_class_tbl_type;
g_kittingarray cct_queue_prod_class_tbl_type;
g_hpp_part cct_quote_parts.hpp_part%TYPE;
g_prime cct_quote_parts.prime%TYPE;
g_code VARCHAR2(50);
g_msg VARCHAR2(50);
PROCEDURE initialize;
PROCEDURE create_parts_queue (
p_quote_number IN cct_quote.quote_number%TYPE,
p_quote_revision IN cct_quote_revisions.quote_revision%TYPE,
p_return_status OUT NOCOPY VARCHAR2
);
END cct_test;
Package Body:
CREATE OR REPLACE PACKAGE BODY cct_test AS
PROCEDURE initialize
AS
BEGIN
g_chemicalarray := cct_queue_prod_class_tbl_type(
1,
2,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
15,
16,
17,
18,
19,
21,
23,
25,
26,
27,
28,
29,
30,
32,
50,
58,
61,
70,
71,
77,
82,
586,
587,
588,
589,
590,
591,
592,
593,
594,
595
);
g_lightningarray := cct_queue_prod_class_tbl_type(
56,
59,
62,
63,
287,
288,
289,
290,
291,
292
);
g_kittingarray := cct_queue_prod_class_tbl_type(
192,
261,
370
);
g_hpp_part := 'Y';
-- g_prime := 'inventory';
g_queuetypeelem(1) := 'HPP';
g_queuetypeelem(2) := 'Chemical';
g_queuetypeelem(3) := 'Lighting';
g_queuetypeelem(4) := 'Kitting';
g_queuetypeelem(5) := 'Advance Sourcing';
g_queuetypeelem(6) := 'Unknown';
g_prodtypeelem := cct_prod_type(
'C',
'R'
);
END initialize;
PROCEDURE create_parts_queue (
p_quote_number IN cct_quote.quote_number%TYPE,
p_quote_revision IN cct_quote_revisions.quote_revision%TYPE,
p_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR curqueue IS
SELECT
cqp.hpp_part,
cqp.quote_revision_id,
cqp.prod_type_abc,
cqp.prod_class,
cqp.prime,
cqp.total_onhand_whses,
cq.opportunity_id
FROM
cct_quote_parts cqp,
cct_quote_revisions cqr,
cct_quote cq
WHERE
cqp.quote_revision_id = cqr.revision_id
AND
cqr.quote_id = cq.quote_id
AND
cq.quote_number = p_quote_number
AND
cqr.quote_revision = p_quote_revision;
l_cursor_val curqueue%rowtype;
l_quote_number cct_quote.quote_number%TYPE;
l_quote_revision cct_quote_revisions.quote_revision%TYPE;
l_opportunity_id cct_quote.opportunity_id%TYPE;
l_quote_revision_id cct_quote_parts.quote_revision_id%TYPE;
l_status VARCHAR2(20);
BEGIN
initialize;
l_quote_number := p_quote_number;
l_quote_revision := p_quote_revision;
-- OPEN curqueue;
-- LOOP
-- FETCH curqueue INTO l_cursor_val;
FOR i IN curqueue LOOP
--l_quote_revision_id := i.quote_revision_id;
-- l_opportunity_id := i.opportunity_id;
-- QUEUE TYPE : HPP
IF
i.hpp_part = g_hpp_part
THEN
UPDATE cct_quote_parts
SET
queue_id = (
SELECT
queue_id
FROM
cct_parts_queue xpq
WHERE
xpq.queue_name = g_queuetypeelem(1)
)
WHERE
quote_revision_id = i.quote_revision_id;
-- QUEUE TYPE : CHEMICAL
ELSIF ( ( ( i.prod_type_abc = g_prodtypeelem(1) ) OR ( i.prod_type_abc = g_prodtypeelem(2) ) ) OR (
i.prod_class MEMBER OF g_chemicalarray
) ) THEN
UPDATE cct_quote_parts
SET
queue_id = (
SELECT
queue_id
FROM
cct_parts_queue xpq
WHERE
xpq.queue_name = g_queuetypeelem(2)
)
WHERE
quote_revision_id = i.quote_revision_id;
END IF;
COMMIT;
EXIT WHEN curqueue%notfound;
END LOOP;
IF
l_status = 'SUCCESS'
THEN
p_return_status := 'SUCCESS';
ELSE
p_return_status := 'FAILURE';
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_return_status := 'FAILURE';
g_code := sqlcode;
g_msg := substr(
sqlerrm,
1,
50
);
cct_create_errors.record_error(
p_quote_revision_id => l_quote_revision_id,
p_opportunity_id => l_opportunity_id,
p_quote_number => l_quote_number,
p_quote_revision => l_quote_revision,
p_error_message => g_msg,
p_error_type => g_code,
p_module_name => 'CCT_TEST.create_parts_queue',
p_creation_date => TO_DATE(
SYSDATE,
'DD-MM-RRRR HH24:MI:SS'
),
p_created_by => user
);
CLOSE curqueue;
END create_parts_queue;
END cct_test;
Please suggest.
Thanks.