Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE proc1 (
- pCustId IN NUMBER,
- pAcctNo IN NUMBER,
- pSearchCriteria IN DATA_INPUTLIST,
- pRecordSet OUT SYS_REFCURSOR,
- out_error_code OUT NUMBER,
- out_error_message OUT VARCHAR2
- ) AS
- --Variables
- v_SUN_DATE DATE := TO_DATE('01/15/2011', 'mm/dd/yyyy');
- vCount NUMBER := 0;
- v_mtn NUM
- BER;
- v_mtn_eff_date DATE;
- cMtnPricePlanInfo DATA_RESULTLIST;
- BEGIN
- SELECT COUNT(*) INTO vCount FROM TABLE (pSearchCriteria);
- FOR i IN 1..vCount LOOP
- SELECT MTN, TO_DATE(MTN_EFF_DATE, 'mm/dd/yyyy') into v_mtn, v_mtn_eff_date
- FROM TABLE (pSearchCriteria)
- WHERE
- ROWNUM = i;
- SELECT
- A.PPLAN_ID, A.EFF_DATE, A.MTN INTO cMtnPricePlanInfo(i).PPLAN_ID, cMtnPricePlanInfo(i).EFF_DATE, cMtnPricePlanInfo(i).MTN
- FROM CUST_ACCT_LINE_PPLAN A, CUST_ACCT_LINE_PPLAN_HIST B
- WHERE
- A.CUST_ID = pCustId
- AND A.ACCT_NO = pAcctNo
- AND A.MTN = v_mtn
- AND A.MTN_EFF_DATE = v_mtn_eff_date
- AND A.EFF_DATE >=
- (SELECT MAX(EFF_DATE) FROM CUST_ACCT_LINE_PPLAN_HIST C
- WHERE
- C.CUST_ID = pCustId
- AND C.ACCT_NO = pAcctNo
- AND C.MTN = v_mtn
- AND C.MTN_EFF_DATE = v_mtn_eff_date
- AND C.EFF_DATE <= v_SUN_DATE)
- AND A.CUST_ID = B.CUST_ID
- AND A.ACCT_NO = B.ACCT_NO
- AND A.MTN = B.MTN
- AND A.MTN_EFF_DATE = B.MTN_EFF_DATE
- AND A.PPLAN_ID = B.PPLAN_ID;
- END LOOP;
- OPEN pRecordSet FOR
- SELECT * FROM TABLE (cMtnPricePlanInfo);
- END;
- /
- SELECT MTN, TO_DATE(MTN_EFF_DATE, 'mm/dd/yyyy') into v_mtn, v_mtn_eff_date
- FROM TABLE (pSearchCriteria)
- WHERE
- ROWNUM = i;
- v_mtn := pSearchCriteria(i).MTN;
- v_mtn_eff_date := TO_DATE(pSearchCriteria(i).MTN_EFF_DATE, 'mm/dd/yyyy');
Add Comment
Please, Sign In to add comment