Solved

PL/SQL Block Custom Menu, Duplicate Records

  • 5 November 2022
  • 3 replies
  • 305 views
Curious_User
Rank
Userlevel 2
Badge +7

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 

icon

Best answer by Curious_User 5 May 2023, 00:54

View original

3 replies

Curious_User
Rank
Userlevel 2
Badge +7

I have solved this myself.

KristenGastaldo
Rank
Userlevel 7
Badge +18

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?

Curious_User
Rank
Userlevel 2
Badge +7

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