Skip to main content

Hello,

I am looking for some input and see if someone can help me find a solution for the below PL/SQL Block that is used for a Right Click Menu.

The blow Block is supposed to fetch 2 tables and then if a value is not in the Customer order table to add them in the 2nd custom table (CUSTOM LU) and ensure there are no duplicate records created.

 

DECLARE
p0_ VARCHAR2(32000);
p1_ VARCHAR2(32000);
p2_ VARCHAR2(32000);
p3_ VARCHAR2(32000):= NULL;
P_ORDER_ VARCHAR2(32000);
P_VIN_ VARCHAR2(17):= NULL;
C_ORDER_ VARCHAR2(32000);
C_VIN_ VARCHAR2(17):= NULL;

CURSOR PLANNED_VINS IS
SELECT DISTINCT &AO.CUSTOMER_ORDER_LINE_CFP.Get_Cf$_Planned_Vin(objkey) 
FROM &AO.customer_order_line WHERE ORDER_NO = &ORDER_NO and &AO.CUSTOMER_ORDER_LINE_CFP.Get_Cf$_Planned_Vin(objkey) is not NULL ;

CURSOR CHASSIS_TRACKER IS
SELECT DISTINCT CF$_FULL_VIN
FROM &AO.C_L_U_CHASSIS_TRACKER_CLV WHERE CF$_ORDER_NO = &ORDER_NO and CF$_FULL_VIN is not null;

BEGIN
OPEN PLANNED_VINS;
OPEN CHASSIS_TRACKER;

LOOP
FETCH PLANNED_VINS  INTO P_VIN_;
FETCH CHASSIS_TRACKER INTO C_VIN_;

IF  C_VIN_ =  P_VIN_ OR C_VIN_ IS NOT NULL OR P_VIN_ IS NULL
THEN
Client_SYS.Clear_Attr(p3_);
ELSE
Client_SYS.Clear_Attr(p3_);
Client_SYS.Add_To_Attr('CF$_FULL_VIN',P_VIN_, p3_);
Client_SYS.Add_To_Attr('CF$_ORDER_NO',&ORDER_NO, p3_);
&AO.C_L_U_CHASSIS_TRACKER_CLP.NEW__( p0_ , p1_ , p2_ , p3_ , 'DO');
Client_SYS.Clear_Attr(p3_);
END IF;
EXIT WHEN PLANNED_VINS%NOTFOUND;
END LOOP;
CLOSE PLANNED_VINS;
CLOSE CHASSIS_TRACKER;
END;

 

Please Help 

I have solved this myself.

Hi @Curious_User  - I’m sorry the community was unable to help. Do you want to share what you did to resolve, in case someone else has the same issue?

The below code is the PL/SQL Block Solution that has worked for my Custom Right Click Menu that will only add data into the Custom LU Table if it is missing without duplication. 

 

DECLARE 
p0_ VARCHAR2(32000);
p1_ VARCHAR2(32000);
p2_ VARCHAR2(32000);
p3_ VARCHAR2(32000);
Q_ORDER_ VARCHAR2(32000);
P_ORDER_ VARCHAR2(32000);
P_VIN_ VARCHAR2(17);
C_ORDER_ VARCHAR2(32000);
C_VIN_ VARCHAR2(17);

CURSOR PLANNED_VINS IS
SELECT DISTINCT col.ORDER_NO, CASE WHEN col.CF$_PLANNED_VIN IS NULL THEN 'P_VIN_ERROR' ELSE col.CF$_PLANNED_VIN END AS CO_VIN, Case when trk.CF$_FULL_VIN is NULL then 'ERROR' else trk.CF$_FULL_VIN end as CH_VIN
FROM agil1app.customer_order_line_CFV col 
LEFT OUTER JOIN agil1app.C_L_U_CHASSIS_TRACKER_CLV trk
ON col.ORDER_NO=trk.CF$_ORDER_NO and trk.CF$_FULL_VIN=col.CF$_PLANNED_VIN
WHERE col.ORDER_NO = Q_ORDER_ and LENGTH(col.CF$_PLANNED_VIN) <= 17 and col.State <> 'Cancelled';

BEGIN
Q_ORDER_ := &ORDER_NO;
OPEN PLANNED_VINS;

LOOP
FETCH PLANNED_VINS INTO P_ORDER_, P_VIN_, C_VIN_;

EXIT WHEN PLANNED_VINS%NOTFOUND;
IF  C_VIN_ != 'ERROR' OR P_VIN_ ='P_VIN_ERROR' THEN NULL;

ELSE
Client_SYS.Clear_Attr(p3_);
Client_SYS.Add_To_Attr('CF$_FULL_VIN',P_VIN_, p3_);
Client_SYS.Add_To_Attr('CF$_ORDER_NO',P_ORDER_, p3_);
AGIL1APP.C_L_U_CHASSIS_TRACKER_CLP.NEW__( p0_ , p1_ , p2_ , p3_ , 'DO');

END IF;
END LOOP;
CLOSE PLANNED_VINS;

END;

Reply


Terms & ConditionsCookie settings