Toad World® Forums

How to fixed code in Oracle


#1

I am doing manually fixed the code of each product that come in new state. I assigned Flag as N manually. If the same product I already assign Y and when it come to new state. It come to N but I want it as Y in oracle. Anybody know how to fixed the code in either Stored procedure or somewhere else and how I can do it. So that I won’t have to do manually.
The whole idea behind the request is to have all new product get automatically mapped to no and any existing codes that are already yes that come into get automatically mapped to yes…

Below is the Stored Procedure. Data are coming from Legacy system. We cann’t used trigger we have to update in the stored procedure there is something is missing that why it is not showing right result and we have to set the Comm_flag manually. Can anybody help me in that and let me know what to do :

CREATE OR REPLACE PROCEDURE AUTO_ASSIGN_NEW_PRODUCTS(
arg_billing_code varchar default ‘%’,
arg_limit_rows integer default 1000000,
arg_min_days_back_effective integer default 5,
arg_test_mode varchar default null,
arg_debug varchar default null
)
AS

– Purpose: Assign product code to commission or non-comm status automatically

– Used by WebApps CCST front-end

– New product codes come into CCST and show up as “Product Alerts” to the users.
– The user goes into the system and sets the commission flag accordingly and also
– assigns a number of points (points were one of the first ways to commission
– sales reps, but is no longer used). If there is a matching product code for
– the same billing system, the new product code will be assigned the same
– commission status and points as the existing one(s).

– If the same product code has multiple commission values, it will be skipped and logged.

– The stored procedure UPDATE_ALERT_P should be run afterwards so that the Product Alert count is updated.

v_status_cd ccst.process_cntl_status.status_cd%TYPE;
v_proc_name varchar(20) default ‘autoassign’;
v_effective_date date default trunc( sysdate, ‘mon’ ); – first day of the current month
v_billing_rec ccst.billing_code_v%ROWTYPE;
v_msg varchar(240);
v_test_mode boolean default false;
v_debug boolean default false;
v_update_user ccst.billing_code_v.ccst_userid%TYPE default ‘AUTOASSIGN’;
v_commissionable_flag ccst.billing_code_v.commissionable_flag%TYPE;
v_commission_points ccst.billing_code_v.commission_points%TYPE;
v_count_changed integer default 0;
v_count_visited integer default 0;
CURSOR c_new_products( arg_bill_code_like varchar default ‘%’ )
IS
SELECT *
FROM ccst.billing_code_v
WHERE init_sales_review_dte IS NULL
AND (commission_points IS NULL OR commissionable_flag IS NULL)
AND data_source_code IN (‘CAMS’, ‘DPI’)
AND billing_code like arg_bill_code_like
ORDER BY billing_code
– FOR UPDATE – Causes ‘fetch out of sequence’
;
PROCEDURE insert_message( v_msg_text varchar, arg_force boolean default false )
IS
v_msg_type ccst.messages.msg_type%TYPE default ‘INFO’;
BEGIN
IF v_test_mode THEN
v_msg_type := ‘TESTING’;
END IF;
IF v_debug OR arg_force THEN
INSERT INTO ccst.messages
VALUES ( null, v_proc_name, sysdate, v_update_user, v_msg_type, v_msg_text );
END IF;
END;
PROCEDURE debug_message( v_msg_text varchar )
IS
BEGIN
IF v_debug OR v_test_mode THEN
DBMS_OUTPUT.PUT_LINE( v_proc_name || ': ’ || v_msg_text );
END IF;
END;
BEGIN
IF arg_debug is not null THEN
v_debug := true;
END IF;
IF arg_test_mode is not null THEN
v_test_mode := true;
END IF;

– verify that data load is not going on
BEGIN
SELECT status_cd INTO v_status_cd
FROM ccst.process_cntl_status
WHERE appl_cd=‘CCST’ AND cntl_typ_cd=1;
IF v_status_cd = ‘L’ THEN
v_msg := ’ status code is ’ || v_status_cd || ‘, in load, exiting’;
debug_message( v_msg );
insert_message( v_msg );
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_msg := ’ ERROR! no status code found for CCST type=1, exiting…’;
debug_message( v_msg );
insert_message( v_msg );
RETURN;
END;

v_msg := ’ STATUS CODE IS ’ || V_STATUS_CD || ‘, CCST not in load state’;
debug_message( v_msg );
insert_message( v_msg );

– If the current date is “close” to the beginning of the month, make the effective date 5 days back
IF v_effective_date > ( sysdate - arg_min_days_back_effective ) THEN
v_effective_date := sysdate - arg_min_days_back_effective;
v_msg := ’ going back into prior month, ’ ||
v_effective_date || ’ is earlier than ’ || trunc( sysdate, ‘mon’ );
debug_message( v_msg );
insert_message( v_msg );
END IF;
COMMIT;

– EEG: We could do this as a simple update, but we have to run a stored procedure for each one to update monthly sale
OPEN c_new_products( arg_billing_code );
LOOP
FETCH c_new_products INTO v_billing_rec;
EXIT WHEN c_new_products%NOTFOUND;
EXIT WHEN c_new_products%ROWCOUNT > arg_limit_rows;

– see if there are existing assignments for this bill code that are not inconsistent
BEGIN
SELECT commissionable_flag, max(commission_points)
INTO v_commissionable_flag, v_commission_points
FROM ccst.billing_code_v
WHERE init_sales_review_dte is not null
AND commission_points is not null
AND commissionable_flag is not null
AND billing_code = v_billing_rec.billing_code
AND data_source_code = v_billing_rec.data_source_code
GROUP BY commissionable_flag – HAVING count(*) = 1; – exclude unless only one commission flag
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_msg := ’ No previously assigned found: ’ ||
v_billing_rec.billing_code || ’ ’ ||
v_billing_rec.billing_org_code || ’ ’ ||
v_billing_rec.data_source_code;
debug_message( v_msg );
insert_message( v_msg );
GOTO NEXT_ITEM;
WHEN TOO_MANY_ROWS THEN
v_msg := ’ billcode has both Y/N commission flags: ’ ||
v_billing_rec.billing_code || ’ ’ ||
v_billing_rec.billing_org_code || ’ ’ ||
v_billing_rec.data_source_code;
debug_message( v_msg );
insert_message( v_msg );
GOTO NEXT_ITEM;
END;

v_msg := v_billing_rec.billing_code || ’ ’ ||
v_billing_rec.billing_org_code || ’ ’ ||
v_commissionable_flag || ’ ’ ||
v_commission_points || ’ ’ ||
v_effective_date || ’ ’ ||
v_update_user || ’ ';

IF v_test_mode THEN
v_msg := 'Test mode. Record would be updated: ’ || v_msg;
insert_message( v_msg );
debug_message( v_msg );
GOTO NEXT_ITEM;
END IF;

v_msg := 'Updating billing code: ’ || v_msg;
insert_message( v_msg, arg_force => true );
COMMIT;

– at this point, we have all the info we need to update the record, so let’s do that
– update:
– init sales review date = now
– last sales review date = now
– update_dttm = now
– userid = autoupdate
– effective_date = trunc(sysdate, ‘mon’)
– commissionable_flag = copy existing
– this used to use WHERE CURRENT, but there was some kind of
– race condition with the DB_LINK:
– ORA-01002: fetch out of sequence
– ORA-02063: preceding line from CTRP
UPDATE ccst.billing_code_v
SET
init_sales_review_dte = sysdate,
last_sales_review_dte = sysdate,
ccst_update_dttm = sysdate,
ccst_userid = v_update_user,
ccst_effective_date = v_effective_date,
commissionable_flag = v_commissionable_flag,
commission_points = v_commission_points
WHERE – CURRENT OF c_new_products;
billing_code = v_billing_rec.billing_code
AND billing_org_code = v_billing_rec.billing_org_code
AND data_source_code = v_billing_rec.data_source_code;
COMMIT;

v_count_changed := v_count_changed 1;

– now run stored procedure to pull orders in if we’ve made something commissionable
ccst.upd_monthly_sale_p( v_billing_rec.data_source_code, v_billing_rec.billing_org_code,
v_billing_rec.billing_code, v_billing_rec.billing_code_key,
v_billing_rec.mktng_product_code,
v_billing_rec.commission_points, v_effective_date );
COMMIT;

<<NEXT_ITEM>>
NULL;
END LOOP;
v_count_visited := c_new_products%ROWCOUNT;
CLOSE c_new_products;

v_msg := ’ Complete. ’ || v_count_changed || ‘/’ || v_count_visited || ’ changed/visited’;
debug_message( v_msg );
insert_message( v_msg, arg_force => true );
COMMIT;

END AUTO_ASSIGN_NEW_PRODUCTS;


#2

Hi,

This forum is used for SQL Optimizer for Oracle related discussions. You may have better success getting responses to your query in a general Oracle SQL/PLSQL discussion forum such as this one http://tech.groups.yahoo.com/group/TOADSQL/.

Regards,
Greg