IMPLEMATING PL/SQL IN DATASTAGE

[bobby]

Hi,
Someone has designed this in PL/sql ,Its inserting the Fictious Record upon a condition, Its selcting from Same Table , Inserting in same Table and updating same table, Plz advice the approach in Datastage.
Thanks
Bobby

********************************/

v_dt_diff number(2):=0;
v_ird varchar2(11);
--get prism records just loaded into Beats
cursor c1 is
select *
from eqt_movement_app
where rec_create_date between (SYSDATE -15/(24*60)) AND
SYSDATE and
filesrc='P';

--match above to prior latest records moving goods to ID locations
cursor c2 is
select *
from eqt_movement_app
where ird_no=v_ird and
filesrc='D' and
direction='S' and
(to_loc like '810%' OR
to_loc like '820%') and
record_id=(select max(record_id)
from eqt_movement_app
where ird_no=v_ird and
filesrc='D' and
direction='S' and
(to_loc like '810%' OR
to_loc like '820%'));

--find last R00 neutral install depot movement to dealer transaction
cursor c3 is
select *
from eqt_movement_app
where ird_no=v_ird and
filesrc='D' and
direction='S' and
(from_loc like '810%' OR
from_loc like '820%') and
record_id=(select max(record_id)
from eqt_movement_app
where ird_no=v_ird and
filesrc='D' and
direction='S' and
(from_loc like '810%' OR
from_loc like '820%'));

BEGIN

for i in c1 loop
v_ird:=i.ird_no;

IF i.direction = 'R' then --pyee dec7 2004 return logic
for r in c3 loop

insert into eqt_movement_app(IRD_NO,direction,from_loc,to_loc,movement_date,
upc_code,SMART_CARD_NO,SAP_PROCESSED,LAST_MODIFIED,FILESRC,
USR,FILENAME,model_no)
values(i.IRD_NO,r.direction,i.to_loc,r.from_loc,i.movement_date,
r.upc_code,r.SMART_CARD_NO,0,sysdate,'D','SYSOP','fictReturn_'||sysdate,
r.model_no);

commit;

end loop; /* c3 cursor -> r PRISM return logic */
--------------------------------------------------------------------------------------------------
ELSE --pyee dec 7 2004 sale logic
for j in c2 loop

if j.to_loc like '820%' and i.from_loc <> '900420001' and upper(i.direction)='S' then


dbms_output.put_line('INVALID:rental sold as purchase sale '||i.ird_no); --pyee

insert into eqt_movement_app(IRD_NO,direction,from_loc,to_loc,movement_date,
upc_code,SMART_CARD_NO,SAP_PROCESSED,LAST_MODIFIED,FILESRC,
USR,FILENAME,model_no)
values(i.IRD_NO,i.direction,j.to_loc,i.from_loc,i.movement_date - 1,
j.upc_code,j.SMART_CARD_NO,4,sysdate,'D','SYSOP',
'rent2purchase_'||sysdate,j.model_no);
commit;
--b)update _app table where prism record just entered matches j.ird_no
update eqt_movement_app
set sap_processed=4
where record_id=i.record_id and
ird_no=i.ird_no and
sap_processed=0 and
filesrc='P';

commit;

ELSE

if j.to_loc like '820%' then --pyee dec7 2004 rental dated 2 days before
insert into eqt_movement_app(IRD_NO,direction,from_loc,to_loc,movement_date,
upc_code,SMART_CARD_NO,SAP_PROCESSED,LAST_MODIFIED,FILESRC,
USR,FILENAME,model_no)
values(i.IRD_NO,i.direction,j.to_loc,i.from_loc,i.movement_date - 2,
j.upc_code,j.SMART_CARD_NO,0,sysdate,'D','SYSOP','fictitious_'||sysdate,
j.model_no);
else --pyee dec 7 2004
insert into eqt_movement_app(IRD_NO,direction,from_loc,to_loc,movement_date,
upc_code,SMART_CARD_NO,SAP_PROCESSED,LAST_MODIFIED,FILESRC,
USR,FILENAME,model_no)
values(i.IRD_NO,i.direction,j.to_loc,i.from_loc,i.movement_date - 1,
j.upc_code,j.SMART_CARD_NO,0,sysdate,'D','SYSOP','fictitious_'||sysdate,
j.model_no);
end if; --pyee dec7 2004 if j_loc like 820% then
commit;

-- pyee dec7 2004 END IF; /* If v_dif_DT_dif < 1 */


END IF; -- if j.to_loc like 820% and i.from_loc <> 900420001


end loop; -- cursor c2 -> find last trans to ROL

END IF; --pyee dec7 2004 If i.direction = R (PRISM return transaction)

v_ird :=null;
v_dt_diff:=null;

end loop; -- cursor c1 -> find all current prism records loaded

END SP_CHECK_ID_LOCATION;

[ray.wurlod]

There seem to be a lot of folks called "someone" at your place!

There are basically two approaches to "implementing" PL/SQL in DataStage.

One is to execute the PL/SQL unchanged from a shell script via a call to DSExecute. Capture the output directly from the call or by redirection of the shell script's stdout.

The other is to analyse what the PL/SQL does and to design this using DataStage components. This is a more robust solution, in that it insulates you better against a future decision to move away from Oracle.