CREATE OR REPLACE PROCEDURE SP_JY_INSERT_CALITEM
(
    P_PYRQ00    IN  CHAR    ,   --
    P_YBH000    IN  CHAR        --
)
AS
    V_COUNT0    NUMBER;
    V_YQDH00    CHAR(2);
    V_TXM000    JY_YW0000.TXM000%TYPE;
    V_JYYWID    NUMBER;
    V_ZLXMID    NUMBER;
    V_COUNT1    NUMBER;
    V_COUNT2    NUMBER;
    V_COUNT3    NUMBER;
    V_COUNT4    NUMBER;
    V_COUNT5    NUMBER;
    V_COUNT6    NUMBER;
    E_EXCEPT    EXCEPTION;
    CURSOR CUR_JY_BGXMZD IS
        SELECT A.BGXMID,B.XMID01,B.XMID02,B.XMID03,B.XMID04,B.XMID05,B.JSGS00
        FROM JY_BGXMZD A,JY_GSJSZD B
        WHERE A.YQDH00=V_YQDH00 AND A.JGLX00='C' AND A.BGXMID=B.BGXMID AND
        NOT EXISTS(SELECT 1 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000 AND BGXMID=A.BGXMID)
        ORDER BY LENGTH(B.JSGS00);
BEGIN
    SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000 WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000 AND JGZT00 IN ('4','5');
    IF V_COUNT0 = 0 THEN
        RETURN;
    END IF;
    V_YQDH00 := SUBSTR(P_YBH000,1,2);

    update jy_jyjg00_temp set jyjg00='' where bgxmid in (
        SELECT A.BGXMID
        FROM JY_BGXMZD A,JY_GSJSZD B
        WHERE A.YQDH00=V_YQDH00 AND A.JGLX00='C' AND A.BGXMID=B.BGXMID AND
         EXISTS(SELECT 1 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00
        AND YBH000=P_YBH000 AND BGXMID=A.BGXMID)) and PYRQ00=P_PYRQ00 AND YBH000=P_YBH000;

    FOR ROW IN CUR_JY_BGXMZD LOOP
        V_COUNT1 := 1;
        V_COUNT2 := 1;
        V_COUNT3 := 1;
        V_COUNT4 := 1;
        V_COUNT5 := 1;
        IF NVL(ROW.XMID01,0) <> 0 THEN
            SELECT COUNT(*) INTO V_COUNT1 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
            AND BGXMID=ROW.XMID01;
        END IF;
        IF NVL(ROW.XMID02,0) <> 0 THEN
            SELECT COUNT(*) INTO V_COUNT2 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
            AND BGXMID=ROW.XMID02;
        END IF;
        IF NVL(ROW.XMID03,0) <> 0 THEN
            SELECT COUNT(*) INTO V_COUNT3 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
            AND BGXMID=ROW.XMID03;
        END IF;
        IF NVL(ROW.XMID04,0) <> 0 THEN
            SELECT COUNT(*) INTO V_COUNT4 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
            AND BGXMID=ROW.XMID04;
        END IF;
        IF NVL(ROW.XMID05,0) <> 0 THEN
            SELECT COUNT(*) INTO V_COUNT5 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
            AND BGXMID=ROW.XMID05;
        END IF;

        IF NVL(ROW.BGXMID,0) <> 0 THEN
           begin
            SELECT COUNT(*) INTO V_COUNT6 FROM JY_JYJG00_TEMP WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
            AND BGXMID=ROW.BGXMID;
           exception
           when others then
              V_COUNT6:=0;
           end;
        END IF;

        IF (V_COUNT1>0) AND (V_COUNT2>0) AND (V_COUNT3>0) AND (V_COUNT4>0) AND (V_COUNT5>0)  and (V_COUNT6=0) THEN
            SELECT JYYWID,ZLXMID,TXM000 INTO V_JYYWID,V_ZLXMID,V_TXM000 FROM JY_JYJG00_TEMP
            WHERE PYRQ00=P_PYRQ00 AND YBH000=P_YBH000
              AND (BGXMID=ROW.XMID01 OR BGXMID=ROW.XMID02 OR BGXMID=ROW.XMID03 OR BGXMID=ROW.XMID04 OR BGXMID=ROW.XMID05)
              AND ROWNUM=1;
            INSERT INTO JY_JYJG00_TEMP(JYYWID,YBH000,PYRQ00,YQJCRQ,ZLXMID,BGXMID,YQDH00,TXM000)
            VALUES(V_JYYWID,P_YBH000,P_PYRQ00,P_PYRQ00,V_ZLXMID,ROW.BGXMID,V_YQDH00,V_TXM000);
        END IF;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*'));
END;