常见问题1000例Oracle(十)
and ad.accountid = ac.accountid
and ac.orgkey = g.cif_id
and ad.ADDRESSCATEGORY = 'Mailing'
AND a.entity_type = 'ACCNT'
AND a.start_date <= to_date(lv_asondate, 'mm-dd-yyyy')
AND a.end_date >= to_date(lv_asondate, 'mm-dd-yyyy')
AND a.del_flg != 'Y'
AND a.entity_cre_flg = 'Y'
AND a.bank_id = '01'
AND add_months(a.peg_review_date, -1) =
to_date(lv_asondate, 'mm-dd-yyyy')
AND a.INT_TBL_CODE_SRL_NUM =
(SELECT MAX(INT_TBL_CODE_SRL_NUM)
FROM tbaadm.ITC b
WHERE b.entity_id = a.entity_id
AND b.entity_type = 'ACCNT'
AND b.del_flg != 'Y'
AND b.entity_cre_flg = 'Y'
AND b.bank_id = '01');
BEGIN
IF (NOT CLOANS_PRT01345_cur01%ISOPEN) THEN
OPEN CLOANS_PRT01345_cur01;
END IF;
IF (CLOANS_PRT01345_cur01%ISOPEN) THEN
LOOP
FETCH CLOANS_PRT01345_cur01
INTO ENTITY_ID,
INT_TBL_CODE_SRL_NUM,
INT_TBL_CODE,
PEG_REVIEW_DATE,
FORACID,
CIF_ID,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
COUNTRY,
ZIP;
IF (CLOANS_PRT01345_cur01%NOTFOUND) THEN
CLOSE CLOANS_PRT01345_cur01;
RETURN;
END IF;
PIPE ROW(CLOANS_RPT01345_TYPE(
FORACID,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
COUNTRY,
ZIP,
PEG_REVIEW_DATE
)
);
END LOOP;
END IF;
END CLOANS_RPT01345_FUNC;
-------------------------------------------
-- test the Function
------------------------------------------
--SELECT * FROM TABLE(CLOANS_RPT01345_FUNC('11-30-2099'))where rownum<50