Hi,
we have a requirement to update a column(queue_id) based on different conditions. as per below anonymous block, we want to update cct_quote_parts table based on different conditions.
we have taken one condition in case statement but its not updating the cct_quote_parts table,
Anonymous block:
SET SERVEROUTPUT ON;
DECLARE
TYPE av_test IS RECORD (
lv_hpp_part VARCHAR2(50),
lv_quote_revision_id VARCHAR2(50),
lv_prod_type_abc VARCHAR2(50),
lv_prod_class VARCHAR2(50),
lv_prime VARCHAR2(50),
lv_total_onhand_whses VARCHAR2(50),
lv_opportunity_id VARCHAR2(50),
lv_source_products VARCHAR2(50),
lv_cost_used_for_quote VARCHAR2(50),
lv_item_tag VARCHAR2(50)
);
TYPE av_test_tab IS
TABLE OF av_test;
av_test_tab_av av_test_tab;
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);
g_code VARCHAR2(100);
g_msg VARCHAR2(2000);
BEGIN
SELECT DISTINCT
cqp.hpp_part,
cqp.quote_revision_id,
cqp.prod_type_abc,
cqp.prod_class,
cqp.prime,
cqp.total_onhand_whses,
cq.opportunity_id,
co.source_products,
cqp.cost_used_for_quote,
cqp.item_tag
BULK COLLECT INTO
av_test_tab_av
FROM
cct_quote_parts cqp,
cct_quote_revisions cqr,
cct_quote cq,
cct_opportunity co
WHERE
cqp.quote_revision_id = cqr.revision_id
AND
cqr.quote_id = cq.quote_id
AND
co.opportunity_id = cq.opportunity_id
AND
cqr.quote_revision = '0.2'
AND
cq.quote_number = 'Q000000066';
dbms_output.put_line(av_test_tab_av.count);
FOR i IN av_test_tab_av.first..av_test_tab_av.last LOOP
dbms_output.put_line('working fine ' || av_test_tab_av(i).lv_quote_revision_id);
UPDATE cct_quote_parts a
SET
queue_id = (
SELECT
CASE
WHEN a.hpp_part = 'Y' THEN 1
END
queue_id
FROM
cct_parts_queue b
)
WHERE
a.quote_revision_id = av_test_tab_av(i).lv_quote_revision_id
AND
a.queue_id IS NULL;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
g_code := sqlcode;
g_msg := substr(
sqlerrm,
1,
64
);
dbms_output.put_line(g_code || ' - ' || g_msg);
END;
in case statement, we'll have to put more conditions. by using FORALL, we need to update the each record and not need to call cct_quote_parts atable again and again. It should update the table, record by record.
How can we achieve it? Please suggest.
Thanks.