[Home] [Help]
34: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
35: l_check_tmplt_comb VARCHAR2(1);
36: CURSOR c_chk_tmlt_cmbntn IS
37: SELECT 'x'
38: FROM Okl_Leaseapp_Templates LATV,
39: OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
40: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
41: AND LATV.CREDIT_REVIEW_PURPOSE = p_latv_rec.CREDIT_REVIEW_PURPOSE
42: AND LATV.CUST_CREDIT_CLASSIFICATION = p_latv_rec.CUST_CREDIT_CLASSIFICATION
115: CURSOR c_get_lat_data( cp_lav_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
116: SELECT LATV.ID LAT_HDR_ID
117: , LATV.OBJECT_VERSION_NUMBER HDR_OBJ_VER_NO
118: , LAVV.OBJECT_VERSION_NUMBER VER_OBJ_VER_NO
119: FROM OKL_LEASEAPP_TEMPLATES LATV,
120: OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
121: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
122: AND LAVV.ID = cp_lav_id;
123:
121: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
122: AND LAVV.ID = cp_lav_id;
123:
124: -- cursor to check if all the versions of the LAT are active
125: CURSOR c_chk_ver_sts( cp_lat_id OKL_LEASEAPP_TEMPLATES.ID%TYPE) IS
126: SELECT 'X'
127: FROM OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
128: WHERE LAVV.LEASEAPP_TEMPLATE_ID = cp_lat_id
129: AND LAVV.VERSION_STATUS <> G_STATUS_ACTIVE;
264: -- Description : This procedure is a wrapper that creates transaction records for
265: -- : lease application template.
266: --
267: -- Business Rules : this procedure is used to create lease application template
268: -- this procedure inserts records into the OKL_LEASEAPP_TEMPLATES and
269: -- OKL_LEASEAPP_TEMPL_VERSIONS_B table
270: --
271: -- Parameters :
272: -- Version : 1.0
446: -- Description : This procedure is a wrapper thatupdates the transaction records for
447: -- : lease application template.
448: --
449: -- Business Rules : this procedure is used to update lease application template
450: -- this procedure updates records of the OKL_LEASEAPP_TEMPLATES and
451: -- OKL_LEASEAPP_TEMPL_VERSIONS_B table.
452: --
453: -- Parameters :
454: -- Version : 1.0
484: l_max_start_date DATE;
485:
486: --Get the min/max effective dates of versions to synchonize temlate header
487: --effective dates
488: CURSOR c_get_valid_dates( p_lat_id okl_leaseapp_templates.id%TYPE) IS
489: SELECT MIN(LAVV.valid_from) valid_from
490: FROM Okl_Leaseapp_Templates LATV,
491: okl_leaseapp_templ_versions_v LAVV
492: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
486: --Get the min/max effective dates of versions to synchonize temlate header
487: --effective dates
488: CURSOR c_get_valid_dates( p_lat_id okl_leaseapp_templates.id%TYPE) IS
489: SELECT MIN(LAVV.valid_from) valid_from
490: FROM Okl_Leaseapp_Templates LATV,
491: okl_leaseapp_templ_versions_v LAVV
492: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
493: AND LATV.ID = p_lat_id
494: GROUP BY LATV.ID;
494: GROUP BY LATV.ID;
495:
496: -- Get the max start date from Lease App and Vendor Prog
497: -- for associated lease app template
498: CURSOR c_get_max_start_date( p_lat_id okl_leaseapp_templates.id%TYPE) IS
499: SELECT MAX(START_DATE) max_start_date
500: FROM
501: (
502: SELECT chr.START_DATE START_DATE
501: (
502: SELECT chr.START_DATE START_DATE
503: FROM okc_k_headers_b chr,
504: okl_vp_associations vpa,
505: Okl_Leaseapp_Templates lat,
506: okl_leaseapp_templ_versions_b lav
507: WHERE chr.scs_code = 'PROGRAM'
508: AND chr.sts_code = 'ACTIVE'
509: AND chr.id = vpa.chr_id
514: AND lat.ID = p_lat_id
515: UNION
516: SELECT laa.VALID_FROM START_DATE
517: FROM OKL_LEASE_APPLICATIONS_B laa,
518: okl_leaseapp_templates lat,
519: okl_leaseapp_templ_versions_v lav
520: WHERE lat.id = lav.leaseapp_template_id
521: AND laa.LEASEAPP_TEMPLATE_ID = lav.ID
522: AND laa.APPLICATION_STATUS IN('CONV-CL','CONV-K','CR-APPROVED','CR-SUBMITTED',
751: -- Description : This procedure is a wrapper that duplicates and versions records for
752: -- : lease application template.
753: --
754: -- Business Rules : this procedure is used to duplicate and version lease application template
755: -- this procedure inserts records into the OKL_LEASEAPP_TEMPLATES and
756: -- OKL_LEASEAPP_TEMPL_VERSIONS_B table
757: --
758: -- Parameters :
759: -- Version : 1.0
787: l_new_seq_value NUMBER;
788: l_debug_enabled VARCHAR2(10);
789: -- Declare Cursor Definations
790: --asawanka bug 4966317 fix starts
791: CURSOR get_latest_activever_dates(p_lat_id IN okl_leaseapp_templates.id%TYPE) IS
792: /* SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
793: FROM okl_leaseapp_templates lat,
794: okl_leaseapp_templ_versions_v lav
795: WHERE lat.id = lav.leaseapp_template_id
789: -- Declare Cursor Definations
790: --asawanka bug 4966317 fix starts
791: CURSOR get_latest_activever_dates(p_lat_id IN okl_leaseapp_templates.id%TYPE) IS
792: /* SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
793: FROM okl_leaseapp_templates lat,
794: okl_leaseapp_templ_versions_v lav
795: WHERE lat.id = lav.leaseapp_template_id
796: AND lat.id = p_lat_id
797: group by lav.valid_from,lav.valid_to; */
798: --Fixed ssdeshpa Bug # 6487421 Start
799: SELECT LAV.VERSION_NUMBER
800: , LAV.VALID_FROM
801: , LAV.VALID_TO
802: FROM OKL_LEASEAPP_TEMPLATES LAT
803: , OKL_LEASEAPP_TEMPL_VERSIONS_B LAV
804: WHERE LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
805: AND LAT.ID = p_lat_id
806: AND TO_NUMBER(LAV.VERSION_NUMBER) = (SELECT MAX(TO_NUMBER(LAV1.VERSION_NUMBER))
1131: -- : checklist , quote and contract templates for
1132: -- : lease application template.
1133: --
1134: -- Business Rules : this procedure is used to validate and update lease application template
1135: -- this procedure updates records of the OKL_LEASEAPP_TEMPLATES and
1136: -- OKL_LEASEAPP_TEMPL_VERSIONS_B table
1137: --
1138: -- Parameters :
1139: -- Version : 1.0
1170: --Validate Checklist template
1171: CURSOR c_validate_checklist_tmplt(p_lavv_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
1172: SELECT CHECKLIST_NUMBER TEMPLATE_NUMBER
1173: FROM OKL_CHECKLISTS CHK,
1174: OKL_LEASEAPP_TEMPLATES LATV,
1175: OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
1176: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
1177: AND LAVV.CHECKLIST_ID = CHK.ID
1178: AND ( (LAVV.VALID_FROM NOT BETWEEN NVL(CHK.START_DATE,LAVV.VALID_FROM)
1185: --Validate Contract Template
1186: CURSOR c_validate_contract_tmplt(p_lavv_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
1187: SELECT OKH.CONTRACT_NUMBER TEMPLATE_NUMBER
1188: FROM OKC_K_HEADERS_B OKH,
1189: OKL_LEASEAPP_TEMPLATES LATV,
1190: OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV,
1191: OKL_K_HEADERS KHR,
1192: OKC_STATUSES_V STS
1193: WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
1389: l_valid_to DATE;
1390: -- Declare Cursor Definations
1391: -- Get the max start date from Lease App and Vendor Prog
1392: -- for associated lease app template
1393: CURSOR c_get_max_start_date( p_lat_id okl_leaseapp_templates.id%TYPE) IS
1394: SELECT MAX(START_DATE) max_start_date
1395: FROM
1396: (
1397: SELECT chr.START_DATE START_DATE
1396: (
1397: SELECT chr.START_DATE START_DATE
1398: FROM okc_k_headers_b chr,
1399: okl_vp_associations vpa,
1400: Okl_Leaseapp_Templates lat,
1401: okl_leaseapp_templ_versions_b lav
1402: WHERE chr.scs_code = 'PROGRAM'
1403: AND chr.sts_code = 'ACTIVE'
1404: AND chr.id = vpa.chr_id
1409: AND lat.ID = p_lat_id
1410: UNION
1411: SELECT laa.VALID_FROM START_DATE
1412: FROM OKL_LEASE_APPLICATIONS_B laa,
1413: okl_leaseapp_templates lat,
1414: okl_leaseapp_templ_versions_v lav
1415: WHERE lat.id = lav.leaseapp_template_id
1416: AND laa.LEASEAPP_TEMPLATE_ID = lav.ID
1417: AND laa.APPLICATION_STATUS IN('CONV-CL','CONV-K','CR-APPROVED','CR-SUBMITTED',
1419: AND trunc(laa.VALID_FROM) >= trunc(lav.VALID_FROM)
1420: AND lat.ID = p_lat_id
1421: ) MY_START_DATE;
1422: --asawanka bug 4966317 fix starts
1423: CURSOR get_latest_activever_dates(p_lat_id IN okl_leaseapp_templates.id%TYPE) IS
1424: /* SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
1425: FROM okl_leaseapp_templates lat,
1426: okl_leaseapp_templ_versions_v lav
1427: WHERE lat.id = lav.leaseapp_template_id
1421: ) MY_START_DATE;
1422: --asawanka bug 4966317 fix starts
1423: CURSOR get_latest_activever_dates(p_lat_id IN okl_leaseapp_templates.id%TYPE) IS
1424: /* SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
1425: FROM okl_leaseapp_templates lat,
1426: okl_leaseapp_templ_versions_v lav
1427: WHERE lat.id = lav.leaseapp_template_id
1428: AND lat.id = p_lat_id
1429: group by lav.valid_from,lav.valid_to;*/
1430: --Fixed ssdeshpa Bug # 6487421 Start
1431: SELECT LAV.VERSION_NUMBER
1432: , LAV.VALID_FROM
1433: , LAV.VALID_TO
1434: FROM OKL_LEASEAPP_TEMPLATES LAT
1435: , OKL_LEASEAPP_TEMPL_VERSIONS_B LAV
1436: WHERE LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
1437: AND LAT.ID = p_lat_id
1438: AND TO_NUMBER(LAV.VERSION_NUMBER) = (SELECT MAX(TO_NUMBER(LAV1.VERSION_NUMBER))