Hi all,
I am new to SAP / MAX DB. I am having problem with stored procedures as
I am unable to find good documentation with detailed examples.
Can anybody guide me how to use cursor inside another cursor in a stored
procedures or what could be an equivalent to the following PostgreSQL
procedure.
Thanks
Vinod
========================================================
/*It will post the O.K. items' qty. of a production slip into
mfg_item_stock table.*/
create or replace function mfg_item_stock_post (varchar) RETURNS integer
AS'
declare
slip ALIAS FOR $1; -- Production Slip to be posted
plan_qty numeric(10, 2); -- Planned qty. for a item
prod_qty numeric(10, 2); -- Actual produced qty. for a item
item_type varchar(1); -- Item type
temp_stock numeric(12, 3);
rm varchar(25);
rm_gross numeric(12, 2);
open_items integer;
prod_val RECORD;
jobcard_val RECORD;
affected_jc_val RECORD;
bom RECORD;
begin
--
==========================================================================
-- post the stock in mfg_item_stock table.
FOR prod_val IN SELECT production_dept_code, production_item_code,
production_item_qty FROM mfg_production_m M,
mfg_production_d D
WHERE M.production_slip_no=D.production_slip_no
AND M.production_qa_flag=''A'' -- Approved by QA
AND D.item_qa_flag=''O''
AND M.production_slip_no=slip LOOP
-- ######### RM to be reduced for #########
SELECT INTO item_type item_type_flag FROM mfg_item_p
WHERE item_item_code=prod_val.production_item_code;
IF item_type = ''C'' THEN -- reduce the Raw Material for
component
SELECT INTO rm, rm_gross, temp_stock item_raw_material,
item_rm_gross, COALESCE(stock_item_qty, 0)
FROM mfg_item_p, mfg_item_stock
WHERE
mfg_item_p.item_item_code=mfg_item_stock.stock_item_code
AND item_tem_code=prod_val.production_item_code;
if (temp_stock - rm_gross * prod_val.production_item_qty) < 0
then
RAISE EXCEPTION ''There is insufficient Raw Material to
manufacture %.\nCan not post it.'', prod_val.production_item_code;
else
-- ######### reduce the raw material used #########
UPDATE mfg_item_stock SET
stock_item_qty=COALESCE(stock_item_qty, 0) -
rm_gross *
prod_val.production_item_qty
WHERE stock_item_code=rm
AND stock_dept_code=prod_val.production_dept_code;
end if;
ELSE
if item_type = ''F'' or item_type = ''S'' THEN -- reduce the
components
FOR bom IN SELECT sub_item_code, sub_item_qty FROM
mfg_bom_m
WHERE main_item_code=prod_val.production_item_code
AND bom_version=(SELECT MAX(bom_version) FROM mfg_bom_m
B
WHERE
B.main_item_code=mfg_bom_m.main_item_code) LOOP
SELECT INTO temp_stock COALESCE(stock_item_qty, 0) FROM
mfg_item_stock
WHERE stock_item_code=bom.sub_item_code;
if (temp_stock - bom.sub_item_qty *
prod_val.production_item_qty) < 0
then
RAISE EXCEPTION ''There is insufficient stock of %
to manufacture %.\nCan not post it.''
, bom.sub_item_code,
prod_val.production_item_code;
else
-- ######### reduce the components used #########
UPDATE mfg_item_stock SET
stock_item_qty=COALESCE(stock_item_qty, 0) -
bom.sub_item_qty *
prod_val.production_item_qty
WHERE stock_item_code=bom.sub_item_code
AND stock_dept_code=prod_val.production_dept_code;
end if;
end loop;
end if;
END IF;
-- ######### add the stock of item produced #########
UPDATE mfg_item_stock SET
stock_item_qty=COALESCE(stock_item_qty, 0) +
prod_val.production_item_qty
WHERE stock_item_code=prod_val.production_item_code
AND stock_dept_code=prod_val.production_dept_code;
end loop;
-- ######### set the stock posting flag and date #########
UPDATE mfg_production_m SET stock_posting_flag=''P'',
stock_posting_date=current_date WHERE production_slip_no=slip;
--
==========================================================================
-- Close individual items in jobcards if all planned qty is produced.
FOR jobcard_val IN SELECT production_jobcard_no,
production_item_code,
production_item_qty
FROM mfg_production_d D, mfg_production_m M
WHERE D.production_slip_no=slip
AND M.stock_posting_flag=''P'' -- stock posted
AND M.production_slip_no=D.production_slip_no
AND D.item_qa_flag=''O'' LOOP
UPDATE ppc_jobcard_d SET
item_production_qty=COALESCE(item_production_qty, 0) +
jobcard_val.production_item_qty
WHERE jobcard_item_code=jobcard_val.production_item_code
AND jobcard_jobcard_no=jobcard_val.production_jobcard_no;
-- select planned qty into plan_qty
SELECT INTO plan_qty jobcard_item_qty FROM ppc_jobcard_d
WHERE jobcard_item_code=jobcard_val.production_item_code
AND jobcard_jobcard_no=jobcard_val.production_jobcard_no;
-- select produced qty into prod_qty
SELECT INTO prod_qty COALESCE(item_production_qty, 0)
FROM ppc_jobcard_d
WHERE jobcard_item_code=jobcard_val.production_item_code
AND jobcard_jobcard_no=jobcard_val.production_jobcard_no;
-- if produced qty is >= planned qty close this item
if prod_qty >= plan_qty
then
UPDATE ppc_jobcard_d SET item_status_flag=''C''
WHERE jobcard_item_code=jobcard_val.production_item_code
AND jobcard_jobcard_no=jobcard_val.production_jobcard_no;
end if;
end loop;
--
==========================================================================
-- Close jobcards if no item is pending.
FOR affected_jc_val IN SELECT DISTINCT production_jobcard_no FROM
mfg_production_d
WHERE production_slip_no=slip LOOP
SELECT INTO open_items count(item_status_flag) FROM ppc_jobcard_d
WHERE jobcard_jobcard_no=affected_jc_val.production_jobcard_no
AND item_status_flag=''O'';
if open_items = 0
then
UPDATE ppc_jobcard_m SET jobcard_status_flag=''C'',
jobcard_close_date=current_date
WHERE
jobcard_jobcard_no=affected_jc_val.production_jobcard_no;
end if;
end loop;
return 0;
end;'
LANGUAGE 'plpgsql';
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
