##############################################################################################################
## 프로시져의 작성예
CREATE OR REPLACE PROCEDURE COLD.P_SALE_BP_P01
( c_BONCD IN CHAR,
c_BUCD IN CHAR,
c_REGDAY IN CHAR
)
AS
-- 셋트상품인것을 확인해서 등록구분의 셋팅및 해제를 실시한다.
-- 출고등록된 것중에서 셋트상품인것은 REGGB 셋팅이 안되어 있으므로
-- 매출원장에서 개별상품을 조회한후 셋트상품에 REGGB에 해당하는 값을 넣어준다.
c_REGGB CHAR(1) ;
c_PRODCD CHAR(8) ;
c_SUBDEALCD CHAR(5) ;
c_ORDERNUM CHAR(5) ;
c_STOCD CHAR(2) ;
c_CNT NUMBER(3) ;
-- 해당일자의 셋트상품 매출내역을 확인한다.
-- 승인구분이 Y 인거
-- 등록구분에 상관없이 조회를 한다.
CURSOR C1_SALE_BP IS
SELECT PRODCD,SUBDEALCD,REGDAY,ORDERNUM,STOCD,COUNT(REGGB) AS CNT
FROM (
SELECT PRODCD,SUBDEALCD,REGDAY,ORDERNUM,STOCD,REGGB FROM SALE_BP
WHERE BONCD = c_BONCD
AND BUCD = c_BUCD
AND REGDAY = c_REGDAY
AND PRODCD != SUBPRODCD
AND AUTHGB ='Y'
AND PRODCD IN (SELECT PRODCD FROM PRODCD_BP WHERE BONCD =c_BONCD AND PRODGB='2')
GROUP BY PRODCD,SUBDEALCD,REGDAY,ORDERNUM,STOCD,REGGB
)
GROUP BY PRODCD,SUBDEALCD,REGDAY,ORDERNUM,STOCD ;
BEGIN
-- 1) 등록구분이 모두 'Y' 일 경우에만 해당 셋트 상품에 등록구분을 'Y'로 한다.
-- /*-----------------------------------------------------------------------*/
FOR C1 IN C1_SALE_BP LOOP
c_PRODCD := C1.PRODCD ;
c_SUBDEALCD := C1.SUBDEALCD ;
c_ORDERNUM := C1.ORDERNUM ;
c_STOCD := C1.STOCD ;
c_CNT := C1.CNT ;
IF c_CNT = 1 THEN
-- REGGB 찾는 쿼리
BEGIN
SELECT REGGB INTO c_REGGB
FROM SALE_BP
WHERE BONCD = c_BONCD
AND BUCD = c_BUCD
AND REGDAY = c_REGDAY
AND SUBDEALCD = C1.SUBDEALCD
AND PRODCD = C1.PRODCD
AND PRODCD!= SUBPRODCD
AND ORDERNUM = C1.ORDERNUM
AND STOCD = C1.STOCD
AND AUTHGB ='Y'
GROUP BY PRODCD,SUBDEALCD,REGDAY,ORDERNUM,STOCD,REGGB;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-11009, 'SETPROD_REGGB_SETTING_P Error'||c_REGGB||c_BONCD ||c_REGDAY);
END;
BEGIN
UPDATE SALE_BP
SET REGGB = c_REGGB
WHERE BONCD = c_BONCD
AND BUCD = c_BUCD
AND REGDAY= c_REGDAY
AND SUBDEALCD = C1.SUBDEALCD
AND PRODCD = C1.PRODCD
AND SUBPRODCD = C1.PRODCD
AND ORDERNUM = C1.ORDERNUM
AND STOCD = C1.STOCD ;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-21999, 'SETPROD_REGGB_SETTING_P Error'||c_REGGB);
END ;
END IF ;
END LOOP;
COMMIT ;
END;
##############################################################################################################
## PHP 로직 상에서 프로시져 호출하기
$query = "BEGIN
Setprod_Reggb_Setting_P('$key_bonCd','$key_buCd','$key_date') ;
END ;
" ;
$stmt = SQLParse($conn,$query);
OCIExecute($stmt,OCI_DEFAULT);
SQLFreeStatement($conn,$stmt);
|
|
|
