For Loop :
Example :
In this example used a pakage pkg_mm.prcinsitemstk
<<Pakage>>
<<Procedure>>
PROCEDURE prc_insitemstk (
p_column_name IN VARCHAR2, -- holds column name
p_column_val IN VARCHAR2, -- holds column value
p_dept_no IN hr_dept.dept_no%TYPE, -- holds department/store number
p_item_no IN mm_item.item_no%TYPE, -- holds goods/item number
p_qty IN mm_itemstock.qty%TYPE, -- holds quantity of purchase/receive/issue
p_uom IN mm_uom.uom_no%TYPE, -- holds unit of measurement number of item/goods
p_exp_date IN mm_itemstock.exp_date%TYPE, -- holds expire date of item/goods
p_unit_price IN mm_itemstock.unit_price%TYPE, -- holds unit price of item / goods
p_tran_flag IN mm_itemstock.tran_flag%TYPE,
-- holds transaction flag of item/goods such as 'PR','II'
p_entered_by IN mm_itemstock.entered_by%TYPE,
-- holds ID of employee/user who made transaction
p_batch_no IN mm_itemstock.batch_no%TYPE, -- holds batch no of item / goods
p_company_no IN VARCHAR2,
-- olds qcchdno of MM_QACHKCHD tablw
p_chkchd_no IN VARCHAR2,
-- Additional parameter for future use if needed
p_add3 IN VARCHAR2
-- Additional parameter for future use if needed
);
type rc_bewstock is record (
dept_no mm_itemstock.dept_no%type,
item_no mm_itemstock.item_no%type,
batch_no mm_itemstock.batch_no%type,
batchno varchar2(30),
exp_date mm_itemstock.exp_date%type,
expdate varchar2(30),
qty mm_itemstock.qty%type);
type ref_bewstock is ref cursor return rc_bewstock;
<<>>
<<body>>
PROCEDURE prc_insitemstk (
p_column_name IN VARCHAR2, -- holds column name
p_column_val IN VARCHAR2, -- holds column value
p_dept_no IN hr_dept.dept_no%TYPE,
-- holds department/store number
p_item_no IN mm_item.item_no%TYPE,
-- holds goods/item number
p_qty IN mm_itemstock.qty%TYPE,
-- holds quantity of purchase/receive/issue
p_uom IN mm_uom.uom_no%TYPE,
-- holds unit of measurement number of item/goods
p_exp_date IN mm_itemstock.exp_date%TYPE,
-- holds expire date of item/goods
p_unit_price IN mm_itemstock.unit_price%TYPE,
-- holds unit price of item / goods
p_tran_flag IN mm_itemstock.tran_flag%TYPE,
-- holds transaction flag of item/goods such as 'PR','II'
p_entered_by IN mm_itemstock.entered_by%TYPE,
-- holds ID of employee/user who made transaction
p_batch_no IN mm_itemstock.batch_no%TYPE,
-- holds batch no of item / goods
p_company_no IN VARCHAR2,
-- Additional parameter for future use if needed
p_chkchd_no IN VARCHAR2,
-- Additional parameter for future use if needed
p_add3 IN VARCHAR2
-- Additional parameter for future use if needed
)
IS
/*
Purpose: Insert data into Item Stock Table
Created By: Aftab Ahmed
Created Time: 17-11-2008
Change History:
=====================================================================
SL Who When What
*/
dml_stm VARCHAR2 (4000);
v_qty mm_itemstock.qty%TYPE;
v_uom_no mm_uom.uom_no%TYPE;
v_idnttype mm_item.identify_type%TYPE;
v_exists NUMBER;
v_error VARCHAR2 (4000);
v_stock_no mm_itemstock.stock_no%TYPE;
v_mkey mm_method.method_key%TYPE;
v_cycle NUMBER := 1;
v_prntstk mm_itemstock.prnt_stock_no%TYPE;
v_batchno mm_itemstock.batch_no%TYPE;
v_expdate mm_itemstock.exp_date%TYPE;
vsumamt NUMBER := 0;
-- VSUMQNTY NUMBER:=0;
BEGIN
--$$ Extract unit of measurement number and identify type of a item/goods such as 'Grooup', 'Unique' $$--
SELECT uom_no, identify_type
INTO v_uom_no, v_idnttype
FROM mm_item
WHERE item_no = p_item_no;
--#######################################################################################################--
/*-$$ Exchange item/goods quantity based on uom_no of mm_itemsetup table
using global function $$-*/
v_qty := fnc_uomconversion (p_uom, p_qty, v_uom_no);
--#######################################################################--
IF p_tran_flag IN ('OB', 'PR', 'FR', 'ERR', 'LT', 'LRR', 'RA')
/* OB=Opening Balance
PR = Parchase Receive
FR = Free Receive
IRR=Internal Return Receive
ERR=External Replace Receive
SR = Sales Return
LT=Loan Taken
LRR=Loan Return Receive
RA=Reconciliation Add
*/
THEN
--$$ Check group or unique item type $$--
IF v_idnttype = 'U'
THEN
v_cycle := v_qty;
v_qty := 1;
END IF;
--####################################--
FOR i IN 1 .. v_cycle
LOOP
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| CASE
WHEN v_qty IS NULL
THEN 'NULL'
ELSE TO_CHAR (v_qty)
END
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
BEGIN
-- TO MAKE A LINK FROM WHICH grnQA ITEMS ARE RECEIVING AGAINST TRANSFER, USE CASE TAKEN FROM BRANCH-TO-BRANCH.
--ADDED BY ZAKIR ON 05-SEP-2010
IF p_tran_flag = 'LT' AND p_chkchd_no IS NOT NULL THEN
UPDATE MM_ITEMSTOCK SET CHKCHD_NO=p_chkchd_no WHERE stock_no=v_stock_no;
END IF;
EXCEPTION WHEN OTHERS THEN RAISE;
END;
END LOOP;
ELSIF p_tran_flag IN ('IRV')
-- IRV = Internal Receive
THEN
DECLARE
v_issno VARCHAR2 (30);
BEGIN
BEGIN
SELECT m.issue_no
INTO v_issno
FROM mm_receivechd c, mm_receivemst m
WHERE m.receive_no = c.receive_no
AND c.rcv_chd_no = p_column_val;
EXCEPTION
WHEN OTHERS
THEN
v_issno := NULL;
END;
IF v_issno IS NOT NULL
THEN
DECLARE
CURSOR cur_fifo
IS
SELECT stock_no, qty
FROM (SELECT a.stock_no,
ABS (a.qty)
- NVL ((SELECT SUM (NVL (ABS (qty), 0))
FROM mm_itemstock
WHERE tran_flag = 'IRV'
AND ref_stock_no = a.stock_no),
0
) qty
FROM mm_itemstock a,
(SELECT issuechd_no
FROM mm_issuemst a, mm_issuechd b
WHERE a.issue_no = b.issue_no
AND a.to_dept = p_dept_no
AND b.item_no = p_item_no
AND a.issue_no = v_issno) b
WHERE a.issuechd_no = b.issuechd_no
ORDER BY stock_no)
WHERE qty > 0;
BEGIN
FOR i IN cur_fifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no, batch_no, exp_date
INTO v_prntstk, v_batchno, v_expdate
FROM (SELECT stock_no, ref_stock_no, batch_no,
exp_date
FROM mm_itemstock
START WITH stock_no = i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--#######################################--
IF v_qty <= i.qty
THEN
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| v_qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
EXIT;
ELSIF v_qty > i.qty
THEN
v_qty := v_qty - i.qty;
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| i.qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
ELSIF p_tran_flag IN ('IRR')
-- IRR = Internal Return Receive
THEN
DECLARE
v_issno VARCHAR2 (30);
BEGIN
BEGIN
SELECT r.issue_no
INTO v_issno
FROM mm_returnmst r
WHERE r.return_no =
(SELECT m.return_no
FROM mm_receivechd c, mm_receivemst m
WHERE m.receive_no = c.receive_no
AND c.rcv_chd_no = p_column_val)
AND r.issue_no IS NOT NULL
AND r.receive_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
v_issno := NULL;
END;
--####################################--
IF v_issno IS NOT NULL
THEN
DECLARE
CURSOR cur_fifo
IS
SELECT stock_no, qty
FROM (SELECT a.stock_no,
ABS (a.qty)
- NVL ((SELECT SUM (NVL (ABS (qty), 0))
FROM mm_itemstock
WHERE tran_flag = 'IRR'
AND ref_stock_no = a.stock_no),
0
) qty
FROM mm_itemstock a,
(SELECT issuechd_no
FROM mm_issuemst a, mm_issuechd b
WHERE a.issue_no = b.issue_no
AND a.from_dept = p_dept_no
AND b.item_no = p_item_no
AND a.issue_no = v_issno) b
WHERE a.issuechd_no = b.issuechd_no
ORDER BY stock_no)
WHERE qty > 0;
BEGIN
FOR i IN cur_fifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no, batch_no, exp_date
INTO v_prntstk, v_batchno, v_expdate
FROM (SELECT stock_no, ref_stock_no, batch_no,
exp_date
FROM mm_itemstock
START WITH stock_no = i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--#######################################--
IF v_qty <= i.qty
THEN
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| v_qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
EXIT;
ELSIF v_qty > i.qty
THEN
v_qty := v_qty - i.qty;
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| i.qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
ELSIF v_issno IS NULL
THEN
DECLARE
CURSOR cur_lifo
IS
SELECT stock_no, qty
FROM (SELECT a.stock_no,
ABS (a.qty)
- NVL ((SELECT SUM (NVL (ABS (qty), 0))
FROM mm_itemstock
WHERE tran_flag = 'IRR'
AND ref_stock_no = a.stock_no),
0
) qty
FROM mm_itemstock a,
(SELECT rtn_chd_no
FROM mm_returnmst a, mm_returnchd b
WHERE a.return_no = b.return_no
AND a.to_dept = p_dept_no
AND b.item_no = p_item_no) b
WHERE a.rtn_chd_no = b.rtn_chd_no)
WHERE qty > 0;
BEGIN
FOR i IN cur_lifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no, batch_no, exp_date
INTO v_prntstk, v_batchno, v_expdate
FROM (SELECT stock_no, ref_stock_no, batch_no,
exp_date
FROM mm_itemstock
START WITH stock_no = i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--######################################--
IF v_qty <= i.qty
THEN
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| v_qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
EXIT;
ELSIF v_qty > i.qty
THEN
v_qty := v_qty - i.qty;
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| i.qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
ELSIF p_tran_flag IN ('SR')
-- SR = Sales Return
THEN
DECLARE
CURSOR cur_fifo
IS
SELECT stock_no, ABS (qty) qty
FROM mm_itemstock
WHERE tran_flag = 'SD'
AND trx_item_no = p_column_val
AND dept_no = p_dept_no
AND item_no = p_item_no;
BEGIN
FOR i IN cur_fifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no, batch_no, exp_date
INTO v_prntstk, v_batchno, v_expdate
FROM (SELECT stock_no, ref_stock_no, batch_no,
exp_date
FROM mm_itemstock
START WITH stock_no = i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--#######################################--
IF v_qty <= i.qty
THEN
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| v_qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
EXIT;
ELSIF v_qty > i.qty
THEN
v_qty := v_qty - i.qty;
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| v_expdate
|| ''''
|| ','
|| ''''
|| v_batchno
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| i.qty
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
ELSIF p_tran_flag IN
('SD', 'II', 'FI', 'ER', 'CS', 'LG', 'LRF', 'RS', 'DM', 'EX',
'O','IR','ER')
/*
SD =Sales(Direct)
II = Internal Issue
FI= Free Issue
IR=Internal Return
ER = External Return
CS=Consume
LG=Loan Given
LRF=Loan Refund
RS= Reconciliation Substruct
DM= Damage item
EX= Expired item
'O'= Others for example goods has been stolen
*/
THEN
BEGIN
--$$ Extract goods/item storing method such as 'FIFO', 'LIFO' $$--
SELECT method_key
INTO v_mkey
FROM mm_method a, mm_item b
WHERE a.method_no(+) = b.method_no AND b.item_no = p_item_no;
-- v_inv_amt:= fnc_module(v_inv_amt);
--v_inv_amt := nvl(pkg_pharmacy.v_modno,'MOD_06');
IF v_active = 'X'
THEN
BEGIN
SELECT m.active_flag
INTO v_active
FROM fn_mappingacc m
WHERE m.module_no = nvl(v_mod_no,'MOD_06')
AND m.vrtype_code = 'JV'
AND company_no = p_company_no;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_active := 'N';
WHEN OTHERS
THEN
raise_application_error (-20001, SQLERRM || ' ' || '1');
END;
END IF;
insert into ssss(sssss)
values('p_dept_no '||p_dept_no||' p_item_no '||p_item_no||'p_batch_no'||p_batch_no);
--##############################################################--
IF NVL (v_mkey, 'FF') = 'FF'
--FF=FIFO
THEN
DECLARE
CURSOR cur_fifo
IS
SELECT stock_no, stock_available
FROM mmv_stockwisestatus
WHERE dept_no = p_dept_no
AND item_no = p_item_no
AND NVL (batch_no, '-') =
NVL (p_batch_no, NVL (batch_no, '-'))
AND NVL (chkchd_no, '-') =
NVL (p_chkchd_no, NVL (chkchd_no, '-'))
AND stock_available > 0
ORDER BY stock_no;
BEGIN
FOR i IN cur_fifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no
INTO v_prntstk
FROM (SELECT stock_no, ref_stock_no
FROM mm_itemstock
START WITH stock_no =i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--####################################--
IF v_qty <= i.stock_available
THEN
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
BEGIN
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| - (v_qty)
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END;
-- this block is added by zakir,SA, on 12/004/10, purpose of to sum total stock value for Accounts integration.
--to find out purchse price
BEGIN
--to find and validate integration is exists of not.
IF v_active = 'Y'
THEN
BEGIN
SELECT po_amt, inv_amt
INTO v_po_amt, v_inv_amt
FROM mmv_stockwisevalue
WHERE stock_no = v_prntstk;
EXCEPTION
WHEN OTHERS
THEN
v_po_amt := 0;
v_inv_amt := 0;
END;
IF v_po_amt IS NOT NULL OR v_po_amt <> 0
THEN
vsumamt := (v_po_amt * v_qty) + vsumamt;
ELSIF v_inv_amt IS NOT NULL OR v_inv_amt <> 0
THEN
vsumamt := (v_inv_amt * v_qty) + vsumamt;
END IF;
END IF;
END;
EXIT;
ELSIF v_qty > i.stock_available
THEN
v_qty := v_qty - i.stock_available;
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
BEGIN
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| - (i.stock_available)
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END;
-- this block is added by zakir,SA, on 12/004/10, purpose of to sum total stock value for Accounts integration.
--to find out purchse price
---under 2nd condition
BEGIN
--to find and validate integration is exists of not.
IF v_active = 'Y'
THEN
BEGIN
SELECT po_amt, inv_amt
INTO v_po_amt, v_inv_amt
FROM mmv_stockwisevalue
WHERE stock_no = v_prntstk;
EXCEPTION
WHEN OTHERS
THEN
v_po_amt := 0;
v_inv_amt := 0;
END;
IF v_po_amt IS NOT NULL OR v_po_amt <> 0
THEN
vsumamt :=
(v_po_amt * i.stock_available) + vsumamt;
ELSIF v_inv_amt IS NOT NULL OR v_inv_amt <> 0
THEN
vsumamt :=
(v_inv_amt * i.stock_available) + vsumamt;
END IF;
END IF;
END;
END IF;
END LOOP;
---ADDED THIS BLOCK FOR ACCOUNTS INTEGRATION OF ITEM CONSUMPTION/SALES
IF p_tran_flag = 'CS'
THEN --CONSUMPTION
BEGIN
UPDATE mm_csmverify
SET csm_amt = vsumamt
WHERE csm_verifyno = p_column_val;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
ELSIF p_tran_flag = 'LG'
THEN
IF TRIM (p_column_name) = 'TRN_CHD_NO'
THEN
BEGIN
UPDATE mm_transferchd
SET trn_amt = vsumamt
WHERE trn_chd_no = p_column_val;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
END IF;
elsif trim (p_tran_flag) = 'SD' then
if p_column_name = 'TRX_ITEM_NO' then
begin
insert into ph_salesamt(TRX_ITEM_NO,
amt,
company_no)
values(p_column_val,
vsumamt,
p_company_no);
exception
when others
then
v_error := sqlerrm;
end;
end if;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
/*
ELSIF NVL (v_mkey, 'FF') = 'LF'
--LF=LIFO
THEN
DECLARE
CURSOR cur_lifo
IS
SELECT stock_no, stock_available
FROM mmv_stockwisestatus
WHERE dept_no = p_dept_no
AND item_no = p_item_no
AND stock_available > 0
ORDER BY stock_no;
BEGIN
FOR i IN cur_lifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no
INTO v_prntstk
FROM (SELECT stock_no, ref_stock_no
FROM mm_itemstock
START WITH stock_no = i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--##########################################--
IF v_qty <= i.stock_available
THEN
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| - (v_qty)
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
EXIT;
ELSIF v_qty > i.stock_available
THEN
v_qty := v_qty - i.stock_available;
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| - (i.stock_available)
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
*/
END IF;
---end of procedure
vsumamt := 0;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
ELSIF p_tran_flag IN ('IR')
/*
IR=Internal Return
*/
THEN
DECLARE
CURSOR cur_fifo
IS
SELECT stock_no, stock_available
FROM (SELECT y.stock_no,
ABS (y.qty)
- NVL ((SELECT SUM (NVL (ABS (qty), 0))
FROM mm_itemstock
WHERE tran_flag = 'IR'
AND ref_stock_no = y.stock_no),
0
) stock_available
FROM mm_itemstock y,
(SELECT rcv_chd_no
FROM mm_returnmst a,
mm_returnchd b,
mm_receivechd c
WHERE a.return_no = b.return_no
AND a.receive_no = c.receive_no
AND b.rtn_chd_no = p_column_val) z
WHERE y.rcv_chd_no = z.rcv_chd_no)
WHERE stock_available > 0;
BEGIN
FOR i IN cur_fifo
LOOP
--$$ Extract parent stock number $$--
BEGIN
SELECT stock_no
INTO v_prntstk
FROM (SELECT stock_no, ref_stock_no
FROM mm_itemstock
START WITH stock_no = i.stock_no
CONNECT BY PRIOR ref_stock_no = stock_no)
WHERE ref_stock_no IS NULL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--####################################--
IF v_qty <= i.stock_available
THEN
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| - (v_qty)
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
EXIT;
ELSIF v_qty > i.stock_available
THEN
v_qty := v_qty - i.stock_available;
prc_build_pk (' ',
'SEQ_MMITEMSTOCK',
'S',
p_company_no,
v_stock_no
);
dml_stm :=
' INSERT INTO MM_ITEMSTOCK (
STOCK_NO,
DEPT_NO,
ENTERED_BY,
ENTRY_TIMESTAMP,
EXP_DATE,
BATCH_NO,
ITEM_NO,
QTY,
PRNT_STOCK_NO,
REF_STOCK_NO,
TRAN_DATE,
TRAN_FLAG,
COMPANY_NO,
UNIT_PRICE,'
|| p_column_name
|| ')
VALUES('
|| ''''
|| v_stock_no
|| ''''
|| ','
|| p_dept_no
|| ','
|| ''''
|| p_entered_by
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_exp_date
|| ''''
|| ','
|| ''''
|| p_batch_no
|| ''''
|| ','
|| ''''
|| p_item_no
|| ''''
|| ','
|| - (i.stock_available)
|| ','
|| ''''
|| v_prntstk
|| ''''
|| ','
|| ''''
|| i.stock_no
|| ''''
|| ','
|| 'TO_DATE('
|| ''''
|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ''''
|| ',''DD/MM/YYYY HH24:MI:SS'')'
|| ','
|| ''''
|| p_tran_flag
|| ''''
|| ','
|| ''''
|| p_company_no
|| ''''
|| ','
|| CASE
WHEN p_unit_price IS NULL
THEN 'NULL'
ELSE TO_CHAR (p_unit_price)
END
|| ','
|| ''''
|| p_column_val
|| ''''
|| ')';
EXECUTE IMMEDIATE dml_stm;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
END;
END IF;
/*IF v_error IS NULL
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;*/
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
--DBMS_OUTPUT.put_line (v_error);
END prc_insitemstk;
<<>>
MM_RECEIVECHD table column list :
1. RCV_CHD_NO
2. RECEIVE_QTY
3.
mm_receivechd pre_insert :
BEGIN
PRC_BUILD_PK (USER, 'SEQ_RECEIVECHD', 'C',:global.g_company_no, :MM_RECEIVECHD.RCV_CHD_NO);
:MM_RECEIVECHD.BASE_QTY := fnc_ItemUomCnv(:MM_RECEIVECHD.ITEM_NO, :MM_RECEIVECHD.RECEIVE_QTY, :MM_RECEIVECHD.UOM_NO);
--FNC_BUILD_PK('T', 'MM_ISSUEMST','ISSUE_NO');
--:MM_ISSUEMST.ISSUED:='Y';
--:MM_ISSUEMST.TRN_DATE:=SYSDATE;
:MM_RECEIVECHD.COMPANY_NO:=:global.g_company_no;
:MM_RECEIVECHD.RECEIVE_NO := :MM_RECEIVEMST.RECEIVE_NO;
:MM_RECEIVECHD.ENTERED_BY := :global.g_emp_no;
:MM_RECEIVECHD.ENTRY_TIMESTAMP := SYSDATE;
EXCEPTION
WHEN OTHERS THEN MSGBOX(SQLERRM||' '||SQLCODE);
END;
<>
GO_BLOCK('MM_RECEIVECHD');
FIRST_RECORD;
BEGIN
LOOP
--$$ Calling package procedure to insert data into mm_itemstock table $$--
pkg_mm.prc_insitemstk
('RCV_CHD_NO',
:MM_RECEIVECHD.rcv_chd_no,
:MM_RECEIVEMST.TO_DEPT,
:MM_RECEIVECHD.item_no,
:MM_RECEIVECHD.receive_qty,
:MM_RECEIVECHD.uom_no,
SYSDATE,
NULL,
'IRV',
:global.g_emp_no,
NULL,
:global.g_company_no,
NULL,
NULL
);
--$$ End of calling package procedure to insert data into mm_itemstock table $$--
EXIT WHEN :SYSTEM.LAST_RECORD='TRUE';
NEXT_RECORD;
END LOOP;
FIRST_RECORD;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
Time in Dhaka 
0 comments:
Post a Comment