Hi,
Hope this helps:
CREATE DBPROC "xxx"."UPCREATEFORMULA" (IN MFRCODE
VARCHAR(5), IN TAGID VARCHAR(7),
IN PRODID INTEGER, OUT AMT FIXED(11,2))
AS
VAR
LSSTR VARCHAR(400); LSQLSTMT VARCHAR(800); LS_MFRCODE
VARCHAR(5); LS_TAGID VARCHAR(7);
LS_AMT VARCHAR(100);
LI_PRODID INTEGER ;
BEGIN
LS_MFRCODE = MFRCODE;
LS_TAGID = TAGID;
LI_PRODID = PRODID;
LSSTR = '0';
SELECT CHR(DECODE
(VPRATEFORMULA,NULL,0,VPRATEFORMULA)) FROM
xxx.CBRANDMFR,xxx.CPRODUCT WHERE
CPRODUCT.VPRODBRNDCODE = CBRANDMFR.VBRANDMFRCODE AND
CBRANDMFR.CTAGID = CPRODUCT.CTAGID AND VMFRBRANDCODE=
:ls_mfrcode AND CPRODUCT.IPRODUCTID= :li_prodid AND
CBRANDMFR.CTAGID= :ls_tagid;
IF $RC = 0 THEN
BEGIN
FETCH INTO :lsstr;
END;
IF LSSTR <> '0' THEN
SELECT :lsstr FROM xxx.CPRODUCT, SYNERGY.CBRAND
WHERE CPRODUCT.VPRODBRNDCODE=CBRAND.VBRNDCODE AND
CPRODUCT.CTAGID=CBRAND.CTAGID AND CPRODUCT.CTAGID =
:ls_tagid AND IPRODUCTID = :li_prodid;
IF $RC = 0 THEN
BEGIN
FETCH INTO :ls_amt;
SET AMT = NUM(LS_AMT);
END;
END;
=============================
--- Vinod Kumar Singh
<[EMAIL PROTECTED]> wrote:
> 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
=== message truncated ===
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
