/*
oracle Complete transaction in package and package body
author:chinayaosir
email: chinayaosir@126.com
blog:blog.csdn.net/chinayaosir
date:4/30/2013
brief Introduction of the content:
1.this project include all quotesheet process on The world's large retail company(walmart,kmart,etc,.)
2. package define and package body implement
oracle包实现完整事务控制
粘出实现代码，愿老人提出缺点，愿新人能够借鉴！
*/
------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE QS_PACK_OTHER AS
/*
-- AUTHOR : chinayaosir
-- EMAIL : chinayaosir@126.com
-- PURPOSE : QUTESHEET COPY AND RE-CALCULATE PACKAGES BODY
-- public function interface list
FUNCTION F_QSCOPY(QSFM VARCHAR2,QSTO VARCHAR2,CUST VARCHAR2,QD DATE,OP VARCHAR2) RETURN VARCHAR2;
FUNCTION F_QSRECAL(QSNO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
*/
/*
F_QSCOPY() Function parameter specification
QSFM:quotesheet copy source qsno#
QSTO:quotesheet copy to target qsno#
CUST:quotesheet copy to customerid on target-qsno#
QD:quotesheet copy to quotedate on target-qsno#
OP:three quotesheet copy option:
I:INSERT,FUNCTION IS ONLY INSERT NEW DATA INTO targetQSNO)
U:UPDATE,FUNCTION IS NEW DATA INSERT,OLD ITEM UPDATEIN INTO targetQSNO)
R:REPACE,FUNCTION IS DELETE ALL DATA,INSERT SOURCE QS DATA INTO targetQSNO)
FUNCTION CALL SAMPLE CODE
F_QSCOPY('Q130414119','Q130422136','WALMART',04/22/2013,'U')
*/
FUNCTION F_QSCOPY(QSFM VARCHAR2,QSTO VARCHAR2,CUST VARCHAR2,QD DATE,OP VARCHAR2) RETURN VARCHAR2;
/*
F_QSRECAL() Function parameter specification
QSNO:re-calculate qsno#
OP:two option:
F:Forward calculate some price structure
R:reverse calculate come price structure
FUNCTION CALL SAMPLE CODE
F_QSRECAL('Q130422136','F')
*/
FUNCTION F_QSRECAL(QSNO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
-------------
--private function list QUOTESHEET COPY
FUNCTION FP_COPY_DELALL(QSTO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
FUNCTION FP_COPY_MASTER(OLD_QSNO VARCHAR2,NEW_QSNO VARCHAR2,NEWCUST VARCHAR2,NEWDT DATE,OP VARCHAR2) RETURN VARCHAR2;
FUNCTION FP_COPY_ITEM(OLD_QSNO VARCHAR2,NEW_QSNO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
FUNCTION FP_COPY_MASTER_FILL(NEW_QSNO VARCHAR2,CUST VARCHAR2) RETURN VARCHAR2;
FUNCTION FP_COPY_ITEM_FILL(NEW_QSNO VARCHAR2,CUST VARCHAR2) RETURN VARCHAR2;
FUNCTION FP_COPY_ITEM_FILL_FREIGHT(CUSTID VARCHAR2,PORTID VARCHAR2,D VARCHAR2) RETURN NUMBER;
FUNCTION FB_COPY_QSRECAL(QSNO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
-------------
--private function list QUOTESHEET RE-CALCULATE
FUNCTION FP_RECAL_PRC(QSNO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
FUNCTION FP_RECAL_PRC_ROW(C VARCHAR2,T VARCHAR2,OP VARCHAR2,QS VARCHAR2,ITEM VARCHAR2,PORT VARCHAR2,P VARCHAR2) RETURN NUMBER;
END;
------------------------------------------------------------------------------------------------------------------------------------------
create or replace package body QS_PACK_OTHER AS
/*
-- AUTHOR : chinayaosir
-- EMAIL : chinayaosir@126.com
-- PURPOSE : QUTESHEET COPY AND RE-CALCULATE PACKAGES BODY
-- public function interface list
FUNCTION F_QSCOPY(QSFM VARCHAR2,QSTO VARCHAR2,CUST VARCHAR2,QD DATE,OP VARCHAR2) RETURN VARCHAR2;
FUNCTION F_QSRECAL(QSNO VARCHAR2,OP VARCHAR2) RETURN VARCHAR2;
--modify history:
01.04/21/2012 finished F_QSCOPY function interface define
02.04/21/2012 finished FP_COPY_DELALL()+ FP_COPY_MASTER()
03.04/22/2012 finished FP_COPY_ITEM(3 table data copy)
04.04/23/2012 finished FP_COPY_ITEM(6 table data copy)
05.04/23/2012 finished FP_COPY_MASTER_FILL(common data auto fill)
06.04/23/2012 finished F_QSRECAL function interface define
07.04/24/2012 finished FP_RECAL_PRC (retrieve all data by qsno)
08.04/24/2012 finished FP_RECAL_PRC_ROW(each item re calculate)
09.04/25/2012 finished FP_COPY_ITEM_FILL(echitem fill freight by customer port)
10.04/26/2012 repaired FP_RECAL_PRC_ROW() update bug (update two times become one times)
11.04/27/2012 finished FB_COPY_QSRECAL() it wiil call by F_QSCOPY() only
11.04/27/2012 repaired F_QSCOPY(re combination database transaction,
old transaction=OP_V1+OP_V2+OP_V3,OP_V4+OP_V5,OP_V6
new transaction=only one)
*/
--public funtion implement list
FUNCTION F_QSCOPY(QSFM VARCHAR2,QSTO VARCHAR2,CUST VARCHAR2,QD DATE,OP VARCHAR2)
RETURN VARCHAR2
IS
OP_V1 VARCHAR2(1);
OP_V2 VARCHAR2(1);
OP_V3 VARCHAR2(1);
OP_V4 VARCHAR2(1);
OP_V5 VARCHAR2(1);
OP_V6 VARCHAR2(1);
OP_D VARCHAR2(1);
BEGIN
OP_D:=NVL(OP,'U');--default operator
OP_V1:=FP_COPY_DELALL(QSTO,OP_D);--if OP is R then clear all data by QSTO
OP_V2:=FP_COPY_MASTER(QSFM,QSTO,CUST,QD,OP_D);
OP_V3:=FP_COPY_ITEM(QSFM,QSTO,OP_D);
OP_V4:=FP_COPY_MASTER_FILL(QSTO,CUST);--fill data into cover common price
OP_V5:=FP_COPY_ITEM_FILL(QSTO,CUST); --fill data into each item price
OP_V6:=FB_COPY_QSRECAL(QSTO,'F');
IF (OP_V1='T' AND OP_V2='T' AND OP_V3='T' AND OP_V4='T' AND OP_V5='T' AND OP_V6='T') THEN
--six function combination a Complete transaction on oralce database
COMMIT;
RETURN ('T');
ELSE
ROLLBACK;
RETURN ('F');
END IF;
RETURN ('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END F_QSCOPY;
--public funtion implement list
FUNCTION F_QSRECAL(QSNO VARCHAR2,OP VARCHAR2)
RETURN VARCHAR2
IS
OP_V1 VARCHAR2(1);
OP_D VARCHAR2(1);
BEGIN
OP_D:=NVL(OP,'F');--default operator
OP_V1:='T';
OP_V1:=FP_RECAL_PRC(QSNO,OP_D);
IF (OP_V1='T' ) THEN
COMMIT;
RETURN ('T');
ELSE
ROLLBACK;
RETURN ('F');
END IF;
RETURN ('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END F_QSRECAL;
--qs copy private function implement list
FUNCTION FP_COPY_DELALL(QSTO VARCHAR2,OP VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF (OP='R') THEN
DELETE FROM Q_QUOTESHEET_OTHER WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QUOTESHEET_PRICE WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QSHEETITEM WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QSHEETITEM_PACKAGING WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QSHEETITEM_PACKING WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QSHEETITEM_DUTY WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QSHEETITEM_PATTERN WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QSHEETITEM_RESULT WHERE QUOTECODE = QSTO ;
DELETE FROM Q_QUOTESHEET WHERE QUOTECODE = QSTO ;
END IF;
RETURN ('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END FP_COPY_DELALL;
--qs copy private function implement list
FUNCTION FP_COPY_MASTER(OLD_QSNO VARCHAR2,NEW_QSNO VARCHAR2,NEWCUST VARCHAR2,NEWDT DATE,OP VARCHAR2)
RETURN VARCHAR2
IS
TYPE TAB_QSMASTER IS TABLE OF Q_QUOTESHEET%ROWTYPE;
TYPE TAB_QSPRC IS TABLE OF Q_QUOTESHEET_PRICE%ROWTYPE;
TYPE TAB_QSOTHER IS TABLE OF Q_QUOTESHEET_OTHER%ROWTYPE;
ROW_MASTER TAB_QSMASTER;
ROW_PRC TAB_QSPRC;
ROW_OTHER TAB_QSOTHER;
TOROW1 NUMBER(10,0);--TARGERT ROWS Q_QUOTESHEET
TOROW2 NUMBER(10,0);--TARGERT ROWS Q_QUOTESHEET_PRICE
TOROW3 NUMBER(10,0);--TARGERT ROWS Q_QUOTESHEET_OTHER
BEGIN
SELECT COUNT(*) INTO TOROW1 FROM Q_QUOTESHEET WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW2 FROM Q_QUOTESHEET_PRICE WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW3 FROM Q_QUOTESHEET_OTHER WHERE QUOTECODE = NEW_QSNO;
--COPY DATA INTO ROW_RECORD
SELECT * BULK COLLECT INTO ROW_MASTER FROM Q_QUOTESHEET WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_PRC FROM Q_QUOTESHEET_PRICE WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_OTHER FROM Q_QUOTESHEET_OTHER WHERE QUOTECODE = OLD_QSNO;
--INSERT/UPDATE Q_QUOTESHEET
IF (TOROW1=0 ) THEN
FOR I IN 1..ROW_MASTER.COUNT LOOP
ROW_MASTER(I).QUOTECODE:=NEW_QSNO;
ROW_MASTER(I).CUSTOMERID:=NEWCUST;
ROW_MASTER(I).QUOTEDATE:=NEWDT;
INSERT INTO Q_QUOTESHEET VALUES ROW_MASTER(I);
END LOOP;
ELSE
IF (OP='I' OR OP='U' OR OP='R' )THEN
FOR I IN 1..ROW_MASTER.COUNT LOOP
ROW_MASTER(I).QUOTECODE:=NEW_QSNO;
ROW_MASTER(I).CUSTOMERID:=NEWCUST;
ROW_MASTER(I).QUOTEDATE:=NEWDT;
UPDATE Q_QUOTESHEET
SET CUSTOMERID =ROW_MASTER(I).CUSTOMERID,
QUOTEDATE =ROW_MASTER(I).QUOTEDATE,
DEFAULTPORT =ROW_MASTER(I).DEFAULTPORT,
DEPTMENTID =ROW_MASTER(I).DEPTMENTID,
BUYER =ROW_MASTER(I).BUYER,
CREATEMAN =ROW_MASTER(I).CREATEMAN,
CATEID =ROW_MASTER(I).CATEID,
INCOTERM =ROW_MASTER(I).INCOTERM,
SEND_TO =ROW_MASTER(I).SEND_TO
WHERE QUOTECODE =ROW_MASTER(I).QUOTECODE;
END LOOP;
END IF;
END IF;
--INSERT/UPDATE Q_QUOTESHEET_PRICE
IF (TOROW2=0 ) THEN
FOR I IN 1..ROW_PRC.COUNT LOOP
ROW_PRC(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QUOTESHEET_PRICE VALUES ROW_PRC(I);
END LOOP;
ELSE
IF (OP='U' OR OP='R' )THEN
FOR I IN 1..ROW_PRC.COUNT LOOP
ROW_PRC(I).QUOTECODE:=NEW_QSNO;
UPDATE Q_QUOTESHEET_PRICE
SET CURRENCY =ROW_PRC(I).CURRENCY,
REMARK =ROW_PRC(I).REMARK
WHERE QUOTECODE = ROW_PRC(I).QUOTECODE;
END LOOP;
END IF;
END IF;
--INSERT/UPDATE Q_QUOTESHEET_OTHER
IF (TOROW3=0) THEN
FOR I IN 1..ROW_OTHER.COUNT LOOP
ROW_OTHER(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QUOTESHEET_OTHER VALUES ROW_OTHER(I);
END LOOP;
ELSE
IF (OP='U' OR OP='R' )THEN
FOR I IN 1..ROW_OTHER.COUNT LOOP
ROW_OTHER(I).QUOTECODE:=NEW_QSNO;
UPDATE Q_QUOTESHEET_OTHER
SET OPENACCOU=ROW_OTHER(I).OPENACCOU,
DAYSAFTER=ROW_OTHER(I).DAYSAFTER
WHERE QUOTECODE =ROW_OTHER(I).QUOTECODE;
END LOOP;
END IF;
END IF;
RETURN('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END FP_COPY_MASTER;
--qs copy private function implement list
FUNCTION FP_COPY_ITEM(OLD_QSNO VARCHAR2,NEW_QSNO VARCHAR2,OP VARCHAR2)
RETURN VARCHAR2
IS
TYPE TAB_ITEM IS TABLE OF Q_QSHEETITEM%ROWTYPE;
TYPE TAB_PACKAGE IS TABLE OF Q_QSHEETITEM_PACKAGING%ROWTYPE;
TYPE TAB_PACK IS TABLE OF Q_QSHEETITEM_PACKING%ROWTYPE;
TYPE TAB_DUTY IS TABLE OF Q_QSHEETITEM_DUTY%ROWTYPE;
TYPE TAB_PTN IS TABLE OF Q_QSHEETITEM_PATTERN%ROWTYPE;
TYPE TAB_RESULT IS TABLE OF Q_QSHEETITEM_RESULT%ROWTYPE;
ROW_ITEM TAB_ITEM;
ROW_PAGE TAB_PACKAGE;
ROW_PACK TAB_PACK;
ROW_DUTY TAB_DUTY;
ROW_PTN TAB_PTN;
ROW_PRCV TAB_RESULT;
TOROW1 NUMBER(10,0);--Q_QSHEETITEM
TOROW2 NUMBER(10,0);--Q_QSHEETITEM_PACKAGING
TOROW3 NUMBER(10,0);--Q_QSHEETITEM_PACKING
TOROW4 NUMBER(10,0);--Q_QSHEETITEM_DUTY
TOROW5 NUMBER(10,0);--Q_QSHEETITEM_PATTERN
TOROW6 NUMBER(10,0);--Q_QSHEETITEM_RESULT
I NUMBER(10,0);--SOURCE QS ROWS
J NUMBER(10,0);--TARGET QS ROWS
BEGIN
SELECT COUNT(*) INTO TOROW1 FROM Q_QSHEETITEM WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW2 FROM Q_QSHEETITEM_PACKAGING WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW3 FROM Q_QSHEETITEM_PACKING WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW4 FROM Q_QSHEETITEM_DUTY WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW5 FROM Q_QSHEETITEM_PATTERN WHERE QUOTECODE = NEW_QSNO;
SELECT COUNT(*) INTO TOROW6 FROM Q_QSHEETITEM_RESULT WHERE QUOTECODE = NEW_QSNO;
--COPY DATA INTO ROW_RECORD
SELECT * BULK COLLECT INTO ROW_ITEM FROM Q_QSHEETITEM WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_PAGE FROM Q_QSHEETITEM_PACKAGING WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_PACK FROM Q_QSHEETITEM_PACKING WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_DUTY FROM Q_QSHEETITEM_DUTY WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_PTN FROM Q_QSHEETITEM_PATTERN WHERE QUOTECODE = OLD_QSNO;
SELECT * BULK COLLECT INTO ROW_PRCV FROM Q_QSHEETITEM_RESULT WHERE QUOTECODE = OLD_QSNO;
--INSERT/UPDATE Q_QSHEETITEM
IF (TOROW1=0) THEN
FOR I IN 1..ROW_ITEM.COUNT LOOP
ROW_ITEM(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QSHEETITEM VALUES ROW_ITEM(I);
END LOOP;
ELSE
FOR I IN 1..ROW_ITEM.COUNT LOOP
ROW_ITEM(I).QUOTECODE:=NEW_QSNO;
SELECT COUNT(*) INTO J FROM Q_QSHEETITEM
WHERE QUOTECODE =ROW_ITEM(I).QUOTECODE AND ITEMNUMBER=ROW_ITEM(I).ITEMNUMBER;
IF (J=0 ) THEN
IF (OP='I' OR OP='U' OR OP='R' )THEN
INSERT INTO Q_QSHEETITEM VALUES ROW_ITEM(I);
END IF;
ELSE
IF (OP='U' OR OP='R' )THEN
UPDATE Q_QSHEETITEM
SET BRIEF_DESC=ROW_ITEM(I).BRIEF_DESC,
DETAILDESC =ROW_ITEM(I).DETAILDESC,
FACTORY_ID =ROW_ITEM(I).FACTORY_ID
WHERE QUOTECODE =ROW_ITEM(I).QUOTECODE AND ITEMNUMBER=ROW_ITEM(I).ITEMNUMBER;
END IF;
END IF;
END LOOP;
END IF;
--INSERT/UPDATE Q_QSHEETITEM_PACKAGING
IF (TOROW2=0) THEN
FOR I IN 1..ROW_PAGE.COUNT LOOP
ROW_PAGE(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QSHEETITEM_PACKAGING VALUES ROW_PAGE(I);
END LOOP;
ELSE
FOR I IN 1..ROW_PAGE.COUNT LOOP
ROW_PAGE(I).QUOTECODE:=NEW_QSNO;
SELECT COUNT(*) INTO J FROM Q_QSHEETITEM_PACKAGING
WHERE QUOTECODE =ROW_PAGE(I).QUOTECODE AND ITEMNUMBER=ROW_PAGE(I).ITEMNUMBER;
IF (J=0) THEN
IF (OP='I' OR OP='U' OR OP='R' )THEN
INSERT INTO Q_QSHEETITEM_PACKAGING VALUES ROW_PAGE(I);
END IF;
ELSE
IF (OP='U' OR OP='R' )THEN
UPDATE Q_QSHEETITEM_PACKAGING
SET PACKAGING =ROW_PAGE(I).PACKAGING,
MATERIAL_P=ROW_PAGE(I).MATERIAL_P,
S_SIZE_L =ROW_PAGE(I).S_SIZE_L,
S_SIZE_W =ROW_PAGE(I).S_SIZE_W
WHERE QUOTECODE =ROW_PAGE(I).QUOTECODE AND ITEMNUMBER=ROW_PAGE(I).ITEMNUMBER;
END IF;
END IF;
END LOOP;
END IF;
--INSERT/UPDATE Q_QSHEETITEM_PACKING
IF (TOROW3=0) THEN
FOR I IN 1..ROW_PACK.COUNT LOOP
ROW_PACK(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QSHEETITEM_PACKING VALUES ROW_PACK(I);
END LOOP;
ELSE
FOR I IN 1..ROW_PACK.COUNT LOOP
ROW_PACK(I).QUOTECODE:=NEW_QSNO;
SELECT COUNT(*) INTO J FROM Q_QSHEETITEM_PACKING
WHERE QUOTECODE =ROW_PACK(I).QUOTECODE AND ITEMNUMBER=ROW_PACK(I).ITEMNUMBER;
IF (J=0) THEN
IF (OP='I' OR OP='U' OR OP='R' )THEN
INSERT INTO Q_QSHEETITEM_PACKING VALUES ROW_PACK(I);
END IF;
ELSE
IF (OP='U' OR OP='R' )THEN
UPDATE Q_QSHEETITEM_PACKING
SET PACKING=ROW_PACK(I).PACKING,
CUFT =ROW_PACK(I).CUFT,
CBM =ROW_PACK(I).CBM,
MASTER_QTY=ROW_PACK(I).MASTER_QTY,
INNER_QTY=ROW_PACK(I).INNER_QTY
WHERE QUOTECODE =ROW_PACK(I).QUOTECODE AND ITEMNUMBER=ROW_PACK(I).ITEMNUMBER;
END IF;
END IF;
END LOOP;
END IF;
--INSERT/UPDATE Q_QSHEETITEM_DUTY
IF (TOROW4=0) THEN
FOR I IN 1..ROW_DUTY.COUNT LOOP
ROW_DUTY(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QSHEETITEM_DUTY VALUES ROW_DUTY(I);
END LOOP;
ELSE
FOR I IN 1..ROW_DUTY.COUNT LOOP
ROW_DUTY(I).QUOTECODE:=NEW_QSNO;
SELECT COUNT(*) INTO J FROM Q_QSHEETITEM_DUTY
WHERE QUOTECODE =ROW_DUTY(I).QUOTECODE AND ITEMNUMBER=ROW_DUTY(I).ITEMNUMBER
AND TARIFF_CAT=ROW_DUTY(I).TARIFF_CAT;
IF (J=0) THEN
IF (OP='I' OR OP='U' OR OP='R' )THEN
INSERT INTO Q_QSHEETITEM_DUTY VALUES ROW_DUTY(I);
END IF;
ELSE
IF (OP='U' OR OP='R' )THEN
UPDATE Q_QSHEETITEM_DUTY
SET TARIFFOFNO=ROW_DUTY(I).TARIFFOFNO,
TARIFFRATE=ROW_DUTY(I).TARIFFRATE,
DUTYOFVALU=ROW_DUTY(I).DUTYOFVALU
WHERE QUOTECODE =ROW_DUTY(I).QUOTECODE AND ITEMNUMBER=ROW_DUTY(I).ITEMNUMBER
AND TARIFF_CAT=ROW_DUTY(I).TARIFF_CAT;
END IF;
END IF;
END LOOP;
END IF;
--INSERT/UPDATE Q_QSHEETITEM_PATTERN
IF (TOROW5=0) THEN
FOR I IN 1..ROW_PTN.COUNT LOOP
ROW_PTN(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QSHEETITEM_PATTERN VALUES ROW_PTN(I);
END LOOP;
ELSE
FOR I IN 1..ROW_PTN.COUNT LOOP
ROW_PTN(I).QUOTECODE:=NEW_QSNO;
SELECT COUNT(*) INTO J FROM Q_QSHEETITEM_PATTERN
WHERE QUOTECODE =ROW_PTN(I).QUOTECODE AND ITEMNUMBER=ROW_PTN(I).ITEMNUMBER
AND PATTERNID=ROW_PTN(I).PATTERNID;
IF (J=0) THEN
IF (OP='I' OR OP='U' OR OP='R' )THEN
INSERT INTO Q_QSHEETITEM_PATTERN VALUES ROW_PTN(I);
END IF;
ELSE
IF (OP='U' OR OP='R' )THEN
UPDATE Q_QSHEETITEM_PATTERN
SET SUB_BRIEF =ROW_PTN(I).SUB_BRIEF,
SUBOF_UPC =ROW_PTN(I).SUBOF_UPC,
PART_SCALE=ROW_PTN(I).PART_SCALE
WHERE QUOTECODE =ROW_PTN(I).QUOTECODE AND ITEMNUMBER=ROW_PTN(I).ITEMNUMBER
AND PATTERNID=ROW_PTN(I).PATTERNID;
END IF;
END IF;
END LOOP;
END IF;
--INSERT/UPDATE Q_QSHEETITEM_RESULT
IF (TOROW6=0) THEN
FOR I IN 1..ROW_PRCV.COUNT LOOP
ROW_PRCV(I).QUOTECODE:=NEW_QSNO;
INSERT INTO Q_QSHEETITEM_RESULT VALUES ROW_PRCV(I);
END LOOP;
ELSE
FOR I IN 1..ROW_PRCV.COUNT LOOP
ROW_PRCV(I).QUOTECODE:=NEW_QSNO;
SELECT COUNT(*) INTO J FROM Q_QSHEETITEM_RESULT
WHERE QUOTECODE =ROW_PRCV(I).QUOTECODE AND ITEMNUMBER=ROW_PRCV(I).ITEMNUMBER
AND SHIPPPORT=ROW_PRCV(I).SHIPPPORT AND DESCPRICE=ROW_PRCV(I).DESCPRICE ;
IF (J=0) THEN
IF (OP='I' OR OP='U' OR OP='R' )THEN
INSERT INTO Q_QSHEETITEM_RESULT VALUES ROW_PRCV(I);
END IF;
ELSE
IF (OP='U' OR OP='R' )THEN
UPDATE Q_QSHEETITEM_RESULT
SET FOB=ROW_PRCV(I).FOB,ELC=ROW_PRCV(I).ELC,MARGIN=ROW_PRCV(I).MARGIN
WHERE QUOTECODE =ROW_PRCV(I).QUOTECODE AND ITEMNUMBER=ROW_PRCV(I).ITEMNUMBER
AND SHIPPPORT=ROW_PRCV(I).SHIPPPORT AND DESCPRICE=ROW_PRCV(I).DESCPRICE ;
END IF;
END IF;
END LOOP;
END IF;
RETURN('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END FP_COPY_ITEM;
--qs copy private function implement list
--FILL COMMON PRICE DATA WITH BASIC DATA WITH QSNO+CUSTOMER
FUNCTION FP_COPY_MASTER_FILL(NEW_QSNO VARCHAR2,CUST VARCHAR2)
RETURN VARCHAR2
IS
CURSOR CURSOR_PRC IS
SELECT * FROM B_CUSTOMERID_PRICE T
WHERE T.CUSTOMERID=CUST AND T.PRCASSORT='QS_COVER' ORDER BY T.SEQUENCE;
TYPE TAB_BPRC IS TABLE OF B_CUSTOMERID_PRICE%ROWTYPE;
ROW_PRC TAB_BPRC;
SQL_STR VARCHAR2(1000);
COLUMN_N VARCHAR2(40);
COLUMN_V NUMBER(10,4);
J NUMBER(10,0);
BEGIN
SQL_STR:='';
OPEN CURSOR_PRC;
FETCH CURSOR_PRC BULK COLLECT INTO ROW_PRC;
FOR J IN 1..ROW_PRC.COUNT LOOP
SQL_STR:='';
COLUMN_N:=ROW_PRC(J).DESCPRICE;
COLUMN_V:=ROUND(ROW_PRC(J).RATE/100,4);
SQL_STR:=SQL_STR||'UPDATE Q_QUOTESHEET_PRICE SET '||COLUMN_N||'='||COLUMN_V;
SQL_STR:=SQL_STR||' WHERE QUOTECODE=:P1';
EXECUTE IMMEDIATE SQL_STR USING NEW_QSNO;
END LOOP;
CLOSE CURSOR_PRC;
RETURN('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END FP_COPY_MASTER_FILL;
--qs copy private function implement list
FUNCTION FP_COPY_ITEM_FILL(NEW_QSNO VARCHAR2,CUST VARCHAR2)
RETURN VARCHAR2
IS
CURSOR CURSOR_ITEM IS
SELECT * FROM QS_VIEW_RESULTPACKQSPRC T
WHERE T.QUOTECODE=NEW_QSNO;
TYPE TAB_ITEMPRC IS TABLE OF QS_VIEW_RESULTPACKQSPRC%ROWTYPE;
ROWP TAB_ITEMPRC;
P_FREIGHT NUMBER(10,4);
SQL_STR VARCHAR2(1000);
J NUMBER(10,0);
BEGIN
OPEN CURSOR_ITEM;
FETCH CURSOR_ITEM BULK COLLECT INTO ROWP;
FOR J IN 1..ROWP.COUNT LOOP
P_FREIGHT :=FP_COPY_ITEM_FILL_FREIGHT(CUST,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE);
SQL_STR :='';
SQL_STR :=SQL_STR||'UPDATE Q_QSHEETITEM_RESULT SET FREIGHT='||P_FREIGHT;
SQL_STR :=SQL_STR||' WHERE QUOTECODE=:P1 AND ITEMNUMBER=:P2 AND SHIPPPORT=:P3 AND DESCPRICE=:P4';
EXECUTE IMMEDIATE SQL_STR USING ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE;
END LOOP;
CLOSE CURSOR_ITEM;
RETURN('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END FP_COPY_ITEM_FILL;
--qs copy private function implement list
FUNCTION FP_COPY_ITEM_FILL_FREIGHT(CUSTID VARCHAR2,PORTID VARCHAR2,D VARCHAR2)
RETURN NUMBER
IS
PRC_V NUMBER(10,4);
BEGIN
PRC_V:=0.0;
SELECT UNIT_PRICE INTO PRC_V
FROM B_CUSTOMERID_FREIGHT WHERE (CUSTOMERID=CUSTID AND SHIPPPORT=PORTID AND DESCPRICE=D);
PRC_V:=ROUND(NVL(PRC_V,0),4);
RETURN(PRC_V);
EXCEPTION
WHEN OTHERS THEN RETURN(0.0);
END FP_COPY_ITEM_FILL_FREIGHT;
--qs copy private function implement list
FUNCTION FB_COPY_QSRECAL(QSNO VARCHAR2,OP VARCHAR2)
RETURN VARCHAR2
IS
OP_V1 VARCHAR2(1);
OP_D VARCHAR2(1);
BEGIN
OP_D:=NVL(OP,'F');--default operator
OP_V1:='T';
OP_V1:=FP_RECAL_PRC(QSNO,OP_D);
RETURN ('T');
EXCEPTION
WHEN OTHERS THEN RETURN('F');
END FB_COPY_QSRECAL;
--qs re-cal private function implement list
FUNCTION FP_RECAL_PRC(QSNO VARCHAR2,OP VARCHAR2)
RETURN VARCHAR2
IS
CURSOR CURSOR_ITEM IS
SELECT * FROM QS_VIEW_RESULTPACKQSPRC T
WHERE T.QUOTECODE=QSNO;
TYPE TAB_ITEMPRC IS TABLE OF QS_VIEW_RESULTPACKQSPRC%ROWTYPE;
ROWP TAB_ITEMPRC;
CUSTID VARCHAR2(20);
--RESULT
FL_ELC NUMBER(10,4);
FL_MARGIN NUMBER(10,8);
STOR_ELC NUMBER(10,4);
STOR_MARGIN NUMBER(10,8);
--REVERS
FL_ELC1 NUMBER(10,4);
FL_FOB NUMBER(10,4);
STOR_ELC1 NUMBER(10,4);
STOR_FOB NUMBER(10,4);
SQL_STR VARCHAR2(1000);
SQL_STR1 VARCHAR2(1000);
SQL_STR2 VARCHAR2(1000);
SQL_STR3 VARCHAR2(1000);
SQL_STR4 VARCHAR2(1000);
J NUMBER(10,0);
BEGIN
SQL_STR:='';
SELECT CUSTOMERID INTO CUSTID FROM Q_QUOTESHEET WHERE QUOTECODE=QSNO;
CASE
--RESULT calculate
WHEN OP='F' THEN
OPEN CURSOR_ITEM;
FETCH CURSOR_ITEM BULK COLLECT INTO ROWP;
FOR J IN 1..ROWP.COUNT LOOP
FL_ELC :=ROUND(FP_RECAL_PRC_ROW(CUSTID,'FL_ELC','RESULT',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE),4);
FL_MARGIN :=ROUND(FP_RECAL_PRC_ROW(CUSTID,'FL_MARGIN','RESULT',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE),8);
STOR_ELC :=ROUND(FP_RECAL_PRC_ROW(CUSTID,'STOR_ELC','RESULT',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE),4);
STOR_MARGIN :=ROUND(FP_RECAL_PRC_ROW(CUSTID,'STOR_MARGIN','RESULT',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE),8);
IF ROWP(J).DESCPRICE='FL' THEN
SQL_STR :='';
SQL_STR :=SQL_STR||'UPDATE Q_QSHEETITEM_RESULT SET ELC='||FL_ELC||',MARGIN='||FL_MARGIN;
SQL_STR :=SQL_STR||' WHERE DESCPRICE='||'''FL'''||' AND QUOTECODE=:1 AND ITEMNUMBER=:2 AND SHIPPPORT=:3';
EXECUTE IMMEDIATE SQL_STR USING ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT;
-- DBMS_OUTPUT.PUT_LINE(SQL_STR||FL_MARGIN||','||ROWP(J).QUOTECODE||','||ROWP(J).ITEMNUMBER||','||ROWP(J).SHIPPPORT);
ELSE
SQL_STR :='';
SQL_STR :=SQL_STR||'UPDATE Q_QSHEETITEM_RESULT SET ELC='||STOR_ELC||',MARGIN='||STOR_MARGIN;
SQL_STR :=SQL_STR||' WHERE DESCPRICE='||'''STOR'''||' AND QUOTECODE=:1 AND ITEMNUMBER=:2 AND SHIPPPORT=:3';
EXECUTE IMMEDIATE SQL_STR USING ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT;
--DBMS_OUTPUT.PUT_LINE(SQL_STR||STOR_MARGIN||','||ROWP(J).QUOTECODE||','||ROWP(J).ITEMNUMBER||','||ROWP(J).SHIPPPORT);
END IF;
END LOOP;
CLOSE CURSOR_ITEM;
--REVERS calculate
WHEN OP='R' THEN
OPEN CURSOR_ITEM;
FETCH CURSOR_ITEM BULK COLLECT INTO ROWP;
FOR J IN 1..ROWP.COUNT LOOP
FL_ELC1 :=FP_RECAL_PRC_ROW(CUSTID,'FL_ELC','REVERS',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE);
FL_FOB :=FP_RECAL_PRC_ROW(CUSTID,'FL_FOB','REVERS',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE);
STOR_ELC1 :=FP_RECAL_PRC_ROW(CUSTID,'STOR_ELC','REVERS',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE);
STOR_FOB :=FP_RECAL_PRC_ROW(CUSTID,'STOR_FOB','REVERS',ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT,ROWP(J).DESCPRICE);
SQL_STR :='';
SQL_STR :=SQL_STR||'UPDATE Q_QSHEETITEM_RESULT SET ELC='||FL_ELC1||',FOB='||FL_FOB;
SQL_STR :=SQL_STR||' WHERE DESCPRICE='||'''FL'''||' AND QUOTECODE=:P1 AND ITEMNUMBER=:P2 AND SHIPPPORT=:P3';
EXECUTE IMMEDIATE SQL_STR USING ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT;
SQL_STR :='';
SQL_STR :=SQL_STR||'UPDATE Q_QSHEETITEM_RESULT SET ELC='||STOR_ELC1||',FOB='||STOR_FOB;
SQL_STR :=SQL_STR||' WHERE DESCPRICE='||'''STOR'''||' AND QUOTECODE=:P1 AND ITEMNUMBER=:P2 AND SHIPPPORT=:P3';
EXECUTE IMMEDIATE SQL_STR USING ROWP(J).QUOTECODE,ROWP(J).ITEMNUMBER,ROWP(J).SHIPPPORT;
END LOOP;
CLOSE CURSOR_ITEM;
END CASE;
COMMIT;
RETURN('T');
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
RETURN('F');
END FP_RECAL_PRC;
--qs re-cal private function implement list
FUNCTION FP_RECAL_PRC_ROW(C VARCHAR2,T VARCHAR2,OP VARCHAR2,QS VARCHAR2,ITEM VARCHAR2,PORT VARCHAR2,P VARCHAR2)
RETURN NUMBER
IS
SQL_FORMULA VARCHAR2(1000);
SQL_STR VARCHAR2(1000);
PRC_V NUMBER(10,8);
BEGIN
PRC_V:=0.0;
SELECT FORMULA INTO SQL_FORMULA
FROM B_CUSTOMERID_FORMULA WHERE (CUSTOMERID=C AND FORMULAID=T AND PRCASSORT=OP);
SQL_STR:='';
SQL_STR:='SELECT '||SQL_FORMULA||' FROM QS_VIEW_RESULTPACKQSPRC
WHERE QUOTECODE=:P1 AND ITEMNUMBER=:P2 AND SHIPPPORT=:P3 AND DESCPRICE=:P4';
EXECUTE IMMEDIATE SQL_STR INTO PRC_V USING QS,ITEM,PORT,P;
PRC_V:=ROUND(NVL(PRC_V,0),8);
RETURN(PRC_V);
EXCEPTION
WHEN OTHERS THEN RETURN(0.0);
END FP_RECAL_PRC_ROW;
END;