41: ,change_request_number
42: ,creq.chr_id orig_agr_chr_id
43: ,chr.scs_code agreement_category
44: FROM okl_vp_change_requests creq
45: ,okc_k_headers_b chr
46: WHERE creq.id = cp_change_request_id
47: AND creq.chr_id = chr.id;
48:
49: -- cursor to find out the agreement id that was created for the change request. this agreement id
56: cv_get_cr_details c_get_cr_details_csr%ROWTYPE;
57: lv_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
58: x_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
59:
60: lv_creq_chr_id okc_k_headers_b.id%TYPE;
61: lv_orig_chr_id okc_k_headers_b.id%TYPE;
62:
63: l_api_version CONSTANT NUMBER DEFAULT 1.0;
64: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_CHANGE_REQUEST';
57: lv_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
58: x_vcrv_rec okl_vcr_pvt.vcrv_rec_type;
59:
60: lv_creq_chr_id okc_k_headers_b.id%TYPE;
61: lv_orig_chr_id okc_k_headers_b.id%TYPE;
62:
63: l_api_version CONSTANT NUMBER DEFAULT 1.0;
64: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_CHANGE_REQUEST';
65: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_CHANGE_REQUEST';
420: ,p_init_msg_list IN VARCHAR2
421: ,x_return_status OUT NOCOPY VARCHAR2
422: ,x_msg_count OUT NOCOPY NUMBER
423: ,x_msg_data OUT NOCOPY VARCHAR2
424: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
425: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
426: ) IS
427: CURSOR c_hdr_attribs_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
428: SELECT chr.short_description
421: ,x_return_status OUT NOCOPY VARCHAR2
422: ,x_msg_count OUT NOCOPY NUMBER
423: ,x_msg_data OUT NOCOPY VARCHAR2
424: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
425: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
426: ) IS
427: CURSOR c_hdr_attribs_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
428: SELECT chr.short_description
429: ,chr.comments
423: ,x_msg_data OUT NOCOPY VARCHAR2
424: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
425: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
426: ) IS
427: CURSOR c_hdr_attribs_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
428: SELECT chr.short_description
429: ,chr.comments
430: ,chr.description
431: ,chr.end_date
601: ,p_init_msg_list IN VARCHAR2
602: ,x_return_status OUT NOCOPY VARCHAR2
603: ,x_msg_count OUT NOCOPY NUMBER
604: ,x_msg_data OUT NOCOPY VARCHAR2
605: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
606: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
607: ) IS
608: -- cursor that finds new non primary parties on the change request
609: CURSOR new_parties_csr(cp_creq_chr_id okc_k_headers_b.id%TYPE
602: ,x_return_status OUT NOCOPY VARCHAR2
603: ,x_msg_count OUT NOCOPY NUMBER
604: ,x_msg_data OUT NOCOPY VARCHAR2
605: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
606: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
607: ) IS
608: -- cursor that finds new non primary parties on the change request
609: CURSOR new_parties_csr(cp_creq_chr_id okc_k_headers_b.id%TYPE
610: ,cp_orig_chr_id okc_k_headers_b.id%TYPE) IS
605: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
606: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
607: ) IS
608: -- cursor that finds new non primary parties on the change request
609: CURSOR new_parties_csr(cp_creq_chr_id okc_k_headers_b.id%TYPE
610: ,cp_orig_chr_id okc_k_headers_b.id%TYPE) IS
611: SELECT cpl.id,
612: cpl.object1_id1,
613: cpl.object1_id2,
606: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
607: ) IS
608: -- cursor that finds new non primary parties on the change request
609: CURSOR new_parties_csr(cp_creq_chr_id okc_k_headers_b.id%TYPE
610: ,cp_orig_chr_id okc_k_headers_b.id%TYPE) IS
611: SELECT cpl.id,
612: cpl.object1_id1,
613: cpl.object1_id2,
614: cpl.jtot_object1_code,
625: AND orig.rle_code = cpl.rle_code
626: AND orig.chr_id = cp_orig_chr_id
627: AND orig.jtot_object1_code = 'OKX_PARTY'
628: );
629: CURSOR c_get_parties_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
630: SELECT id
631: ,chr_id
632: ,rle_code
633: ,dnz_chr_id
644: FROM okc_k_party_roles_v
645: WHERE dnz_chr_id = cp_chr_id
646: AND jtot_object1_code = 'OKX_PARTY'; -- this indicates non primary parties
647:
648: CURSOR c_get_party_info_csr(cp_chr_id okc_k_headers_b.id%TYPE
649: ,cp_party_id hz_parties.party_id%TYPE) IS
650: SELECT cpl.id
651: ,cpl.chr_id
652: ,cpl.rle_code
913: ,p_init_msg_list IN VARCHAR2
914: ,x_return_status OUT NOCOPY VARCHAR2
915: ,x_msg_count OUT NOCOPY NUMBER
916: ,x_msg_data OUT NOCOPY VARCHAR2
917: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
918: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
919: ) IS
920: l_api_version CONSTANT NUMBER DEFAULT 1.0;
921: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_PARTY_CONTACTS';
914: ,x_return_status OUT NOCOPY VARCHAR2
915: ,x_msg_count OUT NOCOPY NUMBER
916: ,x_msg_data OUT NOCOPY VARCHAR2
917: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
918: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
919: ) IS
920: l_api_version CONSTANT NUMBER DEFAULT 1.0;
921: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_PARTY_CONTACTS';
922: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_PARTY_CONTACTS';
921: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_PARTY_CONTACTS';
922: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_PARTY_CONTACTS';
923: l_debug_enabled VARCHAR2(10);
924:
925: CURSOR c_get_diff_contact_csr(cp_chr_id_1 okc_k_headers_b.id%TYPE
926: ,cp_chr_id_2 okc_k_headers_b.id%TYPE)IS
927: SELECT role.object1_id1 role_party_id
928: ,role.object1_id2 role_object1_id2
929: ,role.rle_code
922: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_PARTY_CONTACTS';
923: l_debug_enabled VARCHAR2(10);
924:
925: CURSOR c_get_diff_contact_csr(cp_chr_id_1 okc_k_headers_b.id%TYPE
926: ,cp_chr_id_2 okc_k_headers_b.id%TYPE)IS
927: SELECT role.object1_id1 role_party_id
928: ,role.object1_id2 role_object1_id2
929: ,role.rle_code
930: ,role.jtot_object1_code role_object
953: AND contact_new.cro_code = contact.cro_code
954: AND contact_new.jtot_object1_code = contact.jtot_object1_code
955: AND role_new.chr_id = cp_chr_id_2
956: );
957: CURSOR c_get_cpl_csr(cp_dnz_chr_id okc_k_headers_b.id%TYPE
958: ,cp_rle_code okc_k_party_roles_b.rle_code%TYPE
959: ,cp_object1_id1 okc_k_party_roles_b.object1_id1%TYPE
960: ,cp_object1_id2 okc_k_party_roles_b.object1_id2%TYPE
961: ,cp_jtot_object1_code okc_k_party_roles_b.jtot_object1_code%TYPE
1111: ,p_init_msg_list IN VARCHAR2
1112: ,x_return_status OUT NOCOPY VARCHAR2
1113: ,x_msg_count OUT NOCOPY NUMBER
1114: ,x_msg_data OUT NOCOPY VARCHAR2
1115: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1116: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1117: ) IS
1118: -- cursor that fetches the new articles on the agreement (original or change request)
1119: CURSOR c_get_articles_csr (cp_chr_id okc_k_headers_b.id%TYPE)IS
1112: ,x_return_status OUT NOCOPY VARCHAR2
1113: ,x_msg_count OUT NOCOPY NUMBER
1114: ,x_msg_data OUT NOCOPY VARCHAR2
1115: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1116: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1117: ) IS
1118: -- cursor that fetches the new articles on the agreement (original or change request)
1119: CURSOR c_get_articles_csr (cp_chr_id okc_k_headers_b.id%TYPE)IS
1120: SELECT change_req.id
1115: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1116: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1117: ) IS
1118: -- cursor that fetches the new articles on the agreement (original or change request)
1119: CURSOR c_get_articles_csr (cp_chr_id okc_k_headers_b.id%TYPE)IS
1120: SELECT change_req.id
1121: ,change_req.chr_id
1122: ,change_req.sav_sae_id
1123: ,change_req.sav_sav_release
1306: ,p_init_msg_list IN VARCHAR2
1307: ,x_return_status OUT NOCOPY VARCHAR2
1308: ,x_msg_count OUT NOCOPY NUMBER
1309: ,x_msg_data OUT NOCOPY VARCHAR2
1310: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1311: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1312: ) IS
1313: l_api_version CONSTANT NUMBER DEFAULT 1.0;
1314: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_VENDOR_BILLING';
1307: ,x_return_status OUT NOCOPY VARCHAR2
1308: ,x_msg_count OUT NOCOPY NUMBER
1309: ,x_msg_data OUT NOCOPY VARCHAR2
1310: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1311: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1312: ) IS
1313: l_api_version CONSTANT NUMBER DEFAULT 1.0;
1314: l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SYNC_VENDOR_BILLING';
1315: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_VENDOR_BILLING';
1315: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_VENDOR_BILLING';
1316: l_debug_enabled VARCHAR2(10);
1317:
1318: -- cursor to fetch the rule group role definitions id for the source Vendor
1319: CURSOR c_get_rrd_id_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1320: SELECT rrd.id
1321: FROM okc_k_headers_b chr
1322: ,okc_subclass_roles sre
1323: ,okc_role_sources rse
1317:
1318: -- cursor to fetch the rule group role definitions id for the source Vendor
1319: CURSOR c_get_rrd_id_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1320: SELECT rrd.id
1321: FROM okc_k_headers_b chr
1322: ,okc_subclass_roles sre
1323: ,okc_role_sources rse
1324: ,okc_subclass_rg_defs srd
1325: ,okc_rg_role_defs rrd
1334: AND rrd.sre_id = sre.id;
1335: lv_orig_rrd_id okc_rg_role_defs.id%TYPE;
1336:
1337: -- cursor that fetches party role id of Lease Vendor
1338: CURSOR c_get_cpl_id_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1339: SELECT id
1340: ,object1_id1
1341: FROM okc_k_party_roles_b
1342: WHERE dnz_chr_id = cp_chr_id
1348: lv_orig_chr_object1 okc_k_party_roles_b.object1_id1%TYPE;
1349: lv_creq_chr_object1 okc_k_party_roles_b.object1_id1%TYPE;
1350:
1351: -- cursor to fetch customer information from the billing page
1352: CURSOR c_get_cust_info_csr(cp_chr_id okc_k_headers_b.id%TYPE, cp_cpl_id okc_k_party_roles_b.id%TYPE) IS
1353: SELECT chrb.id chr_id,
1354: rgpb.id rgp_id,
1355: cplv.id cpl_id,
1356: rulb.id cust_rule_id,
1363: hzcsu.location bill_to_site_use_name,
1364: cplv.cognomen cognomen,
1365: cplv.alias alias,
1366: chrb.authoring_org_id authoring_org_id
1367: FROM okc_k_headers_b chrb,
1368: okc_k_party_roles_v cplv,
1369: hz_cust_accounts hzc,
1370: hz_cust_site_uses_all hzcsu,
1371: okc_rg_party_roles rgrp,
1389:
1390: -- cursor to fetch other billing details from the billing page
1391: -- Updated the sql for performance issue - bug#5484903 - sql id: 20567146
1392: -- varangan - 26-9-06
1393: CURSOR c_get_cust_bill_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
1394: SELECT chr.id chr_id
1395: ,rgp_lavenb.id lavenb_rgp_id
1396: ,rul_lainpr.id lainpr_rul_id
1397: ,rul_lainpr.rule_information1 rul_lainpr_ri1
1405: ,rul_labacc.object1_id2 rul_labacc_o1id2
1406: ,rul_lapmth.id lapmth_rul_id
1407: ,rul_lapmth.object1_id1 rul_lapmth_o1id1
1408: ,rul_lapmth.object1_id2 rul_lapmth_o1id2
1409: FROM okc_k_headers_b chr
1410: ,okl_k_headers khr
1411: ,okc_rule_groups_b rgp_lavenb
1412: ,okc_rules_b rul_lainpr
1413: ,okc_rules_b rul_lainvd
1755: ,p_init_msg_list IN VARCHAR2
1756: ,x_return_status OUT NOCOPY VARCHAR2
1757: ,x_msg_count OUT NOCOPY NUMBER
1758: ,x_msg_data OUT NOCOPY VARCHAR2
1759: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1760: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1761: ,p_change_request_id IN okl_vp_change_requests.id%TYPE
1762: ) IS
1763:
1756: ,x_return_status OUT NOCOPY VARCHAR2
1757: ,x_msg_count OUT NOCOPY NUMBER
1758: ,x_msg_data OUT NOCOPY VARCHAR2
1759: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
1760: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
1761: ,p_change_request_id IN okl_vp_change_requests.id%TYPE
1762: ) IS
1763:
1764: -- cursor to get all the associations for the given change request
1761: ,p_change_request_id IN okl_vp_change_requests.id%TYPE
1762: ) IS
1763:
1764: -- cursor to get all the associations for the given change request
1765: CURSOR c_get_creq_assoc(cp_chr_id okc_k_headers_b.id%TYPE
1766: ,cp_change_request_id okl_vp_change_requests.id%TYPE
1767: ,cp_assoc_object_id okl_vp_associations.assoc_object_id%TYPE
1768: ,cp_assoc_object_code okl_vp_associations.assoc_object_type_code%TYPE
1769: ,cp_assoc_object_version okl_vp_associations.assoc_object_version%TYPE
1784: cv_creq_assoc_rec c_get_creq_assoc%ROWTYPE;
1785:
1786: -- get the associations on the original agreement. this cursor is valid
1787: -- for both ASSOCIATION change requests and AGREEMENT change requests
1788: CURSOR c_get_orig_assoc(cp_chr_id okc_k_headers_b.id%TYPE)IS
1789: SELECT chr_id
1790: ,start_date
1791: ,end_date
1792: ,description
1798: WHERE crs_id IS NULL
1799: AND chr_id = cp_chr_id;
1800:
1801: CURSOR c_new_creq_assoc_csr(cp_change_request_id okl_vp_change_requests.id%TYPE
1802: ,cp_chr_id okc_k_headers_b.id%TYPE) IS
1803: SELECT creq.chr_id
1804: ,creq.crs_id
1805: ,creq.start_date
1806: ,creq.end_date
2000: ,p_init_msg_list IN VARCHAR2
2001: ,x_return_status OUT NOCOPY VARCHAR2
2002: ,x_msg_count OUT NOCOPY NUMBER
2003: ,x_msg_data OUT NOCOPY VARCHAR2
2004: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2005: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2006: ,p_change_request_id IN okl_vp_change_requests.id%TYPE
2007: ) IS
2008:
2001: ,x_return_status OUT NOCOPY VARCHAR2
2002: ,x_msg_count OUT NOCOPY NUMBER
2003: ,x_msg_data OUT NOCOPY VARCHAR2
2004: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2005: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2006: ,p_change_request_id IN okl_vp_change_requests.id%TYPE
2007: ) IS
2008:
2009: -- cursor to get all the associations for the given change request of type AGREEMENT
2006: ,p_change_request_id IN okl_vp_change_requests.id%TYPE
2007: ) IS
2008:
2009: -- cursor to get all the associations for the given change request of type AGREEMENT
2010: CURSOR c_get_creq_assoc(cp_chr_id okc_k_headers_b.id%TYPE
2011: ,cp_assoc_object_id okl_vp_associations.assoc_object_id%TYPE
2012: ,cp_assoc_object_code okl_vp_associations.assoc_object_type_code%TYPE
2013: ,cp_assoc_object_version okl_vp_associations.assoc_object_version%TYPE
2014: )IS
2027: cv_creq_assoc_rec c_get_creq_assoc%ROWTYPE;
2028:
2029: -- get the associations on the original agreement. this cursor is valid
2030: -- for both ASSOCIATION change requests and AGREEMENT change requests
2031: CURSOR c_get_orig_assoc(cp_chr_id okc_k_headers_b.id%TYPE)IS
2032: SELECT chr_id
2033: ,start_date
2034: ,end_date
2035: ,description
2041: WHERE crs_id IS NULL
2042: AND chr_id = cp_chr_id;
2043:
2044: CURSOR c_new_creq_assoc_csr(cp_chr_id_orig okl_vp_change_requests.id%TYPE
2045: ,cp_chr_id_creq okc_k_headers_b.id%TYPE) IS
2046: SELECT creq.chr_id
2047: ,creq.crs_id
2048: ,creq.start_date
2049: ,creq.end_date
2238: ,p_init_msg_list IN VARCHAR2
2239: ,x_return_status OUT NOCOPY VARCHAR2
2240: ,x_msg_count OUT NOCOPY NUMBER
2241: ,x_msg_data OUT NOCOPY VARCHAR2
2242: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2243: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2244: )IS
2245: CURSOR c_get_agrmt_dates_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
2246: SELECT start_date
2239: ,x_return_status OUT NOCOPY VARCHAR2
2240: ,x_msg_count OUT NOCOPY NUMBER
2241: ,x_msg_data OUT NOCOPY VARCHAR2
2242: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2243: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2244: )IS
2245: CURSOR c_get_agrmt_dates_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
2246: SELECT start_date
2247: ,end_date
2241: ,x_msg_data OUT NOCOPY VARCHAR2
2242: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2243: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2244: )IS
2245: CURSOR c_get_agrmt_dates_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
2246: SELECT start_date
2247: ,end_date
2248: FROM okc_k_headers_b
2249: WHERE id = cp_chr_id;
2244: )IS
2245: CURSOR c_get_agrmt_dates_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
2246: SELECT start_date
2247: ,end_date
2248: FROM okc_k_headers_b
2249: WHERE id = cp_chr_id;
2250: cv_get_agrmnt_dates c_get_agrmt_dates_csr%ROWTYPE;
2251:
2252: lx_ech_rec ech_rec_type;
2426: ,p_init_msg_list IN VARCHAR2
2427: ,x_return_status OUT NOCOPY VARCHAR2
2428: ,x_msg_count OUT NOCOPY NUMBER
2429: ,x_msg_data OUT NOCOPY VARCHAR2
2430: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2431: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2432: ) IS
2433:
2434: -- cursor that finds out missing rule group records from the change request
2427: ,x_return_status OUT NOCOPY VARCHAR2
2428: ,x_msg_count OUT NOCOPY NUMBER
2429: ,x_msg_data OUT NOCOPY VARCHAR2
2430: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2431: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2432: ) IS
2433:
2434: -- cursor that finds out missing rule group records from the change request
2435: -- so that these records can be deleted from the original program agreement too
2432: ) IS
2433:
2434: -- cursor that finds out missing rule group records from the change request
2435: -- so that these records can be deleted from the original program agreement too
2436: CURSOR c_get_miss_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2437: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2438: SELECT orig.id
2439: ,orig.dnz_chr_id
2440: ,orig.rgd_code
2433:
2434: -- cursor that finds out missing rule group records from the change request
2435: -- so that these records can be deleted from the original program agreement too
2436: CURSOR c_get_miss_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2437: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2438: SELECT orig.id
2439: ,orig.dnz_chr_id
2440: ,orig.rgd_code
2441: FROM okc_rule_groups_v orig
2451: AND creq.cle_id IS NULL);
2452:
2453: -- cursor that finds common rule group records on the change request as well as the originating
2454: -- program agreement
2455: CURSOR c_get_comm_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2456: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2457: SELECT creq.id
2458: ,creq.dnz_chr_id
2459: ,creq.rgd_code
2452:
2453: -- cursor that finds common rule group records on the change request as well as the originating
2454: -- program agreement
2455: CURSOR c_get_comm_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2456: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2457: SELECT creq.id
2458: ,creq.dnz_chr_id
2459: ,creq.rgd_code
2460: FROM okc_rule_groups_v creq
2469: AND orig.dnz_chr_id = cp_orig_chr_id
2470: AND orig.cle_id IS NULL);
2471:
2472: CURSOR c_rl_exist_csr (cp_rul_info_cat okc_rules_b.rule_information_category%TYPE,
2473: cp_chr_id okc_k_headers_b.id%TYPE,
2474: cp_rgd_code okc_rule_groups_b.rgd_code%TYPE) IS
2475: SELECT rul.id
2476: ,rul.rgp_id
2477: ,rul.object_version_number
2484: AND rgp.rgd_code = cp_rgd_code;
2485:
2486: -- cursor that finds out new rule group records from the change request
2487: -- so that these records can be added to the original program agreement
2488: CURSOR c_get_new_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2489: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2490: SELECT creq.id
2491: ,creq.dnz_chr_id
2492: ,creq.rgd_code
2485:
2486: -- cursor that finds out new rule group records from the change request
2487: -- so that these records can be added to the original program agreement
2488: CURSOR c_get_new_rl_csr(cp_orig_chr_id okc_k_headers_b.id%TYPE
2489: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2490: SELECT creq.id
2491: ,creq.dnz_chr_id
2492: ,creq.rgd_code
2493: ,creq.rgp_type
2502: AND orig.rgd_code = creq.rgd_code
2503: AND orig.dnz_chr_id = cp_orig_chr_id
2504: AND orig.cle_id IS NULL);
2505:
2506: CURSOR c_get_residual_grp(cp_chr_id okc_k_headers_b.id%TYPE) IS
2507: SELECT id
2508: FROM okc_rule_groups_b
2509: WHERE dnz_chr_id = cp_chr_id
2510: AND rgd_code = 'VGLRS';
2537: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_VP_SYNC_CR_PVT.SYNC_TERMS';
2538: l_debug_enabled VARCHAR2(10);
2539:
2540: FUNCTION get_original_cpl_id(p_new_cpl_id okc_k_party_roles_b.id%TYPE
2541: ,p_orig_chr_id okc_k_headers_b.id%TYPE
2542: ,p_creq_chr_id okc_k_headers_b.id%TYPE
2543: ) RETURN NUMBER IS
2544: CURSOR c_get_cpl_id_csr (cp_cpl_id okc_k_party_roles_b.id%TYPE
2545: ,cp_orig_chr_id okc_k_headers_b.id%TYPE
2538: l_debug_enabled VARCHAR2(10);
2539:
2540: FUNCTION get_original_cpl_id(p_new_cpl_id okc_k_party_roles_b.id%TYPE
2541: ,p_orig_chr_id okc_k_headers_b.id%TYPE
2542: ,p_creq_chr_id okc_k_headers_b.id%TYPE
2543: ) RETURN NUMBER IS
2544: CURSOR c_get_cpl_id_csr (cp_cpl_id okc_k_party_roles_b.id%TYPE
2545: ,cp_orig_chr_id okc_k_headers_b.id%TYPE
2546: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2541: ,p_orig_chr_id okc_k_headers_b.id%TYPE
2542: ,p_creq_chr_id okc_k_headers_b.id%TYPE
2543: ) RETURN NUMBER IS
2544: CURSOR c_get_cpl_id_csr (cp_cpl_id okc_k_party_roles_b.id%TYPE
2545: ,cp_orig_chr_id okc_k_headers_b.id%TYPE
2546: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2547: SELECT cpl.id
2548: FROM okc_k_party_roles_b cpl
2549: ,okc_k_party_roles_b cpl1
2542: ,p_creq_chr_id okc_k_headers_b.id%TYPE
2543: ) RETURN NUMBER IS
2544: CURSOR c_get_cpl_id_csr (cp_cpl_id okc_k_party_roles_b.id%TYPE
2545: ,cp_orig_chr_id okc_k_headers_b.id%TYPE
2546: ,cp_creq_chr_id okc_k_headers_b.id%TYPE) IS
2547: SELECT cpl.id
2548: FROM okc_k_party_roles_b cpl
2549: ,okc_k_party_roles_b cpl1
2550: WHERE cpl.chr_id = cp_orig_chr_id
2945: ,p_init_msg_list IN VARCHAR2
2946: ,x_return_status OUT NOCOPY VARCHAR2
2947: ,x_msg_count OUT NOCOPY NUMBER
2948: ,x_msg_data OUT NOCOPY VARCHAR2
2949: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2950: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2951: ) IS
2952: CURSOR c_get_disb_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
2953: SELECT pay.pay_site_id
2946: ,x_return_status OUT NOCOPY VARCHAR2
2947: ,x_msg_count OUT NOCOPY NUMBER
2948: ,x_msg_data OUT NOCOPY VARCHAR2
2949: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2950: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2951: ) IS
2952: CURSOR c_get_disb_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
2953: SELECT pay.pay_site_id
2954: ,pay.payment_term_id
2948: ,x_msg_data OUT NOCOPY VARCHAR2
2949: ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
2950: ,p_creq_chr_id IN okc_k_headers_b.id%TYPE
2951: ) IS
2952: CURSOR c_get_disb_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
2953: SELECT pay.pay_site_id
2954: ,pay.payment_term_id
2955: ,pay.payment_method_code
2956: ,pay.pay_group_code
2966: WHERE pay.cpl_id = role.id
2967: AND role.cle_id IS NULL
2968: AND role.dnz_chr_id = cp_chr_id;
2969:
2970: CURSOR c_get_orig_disb_csr(cp_chr_id okc_k_headers_b.id%TYPE
2971: ,cp_rle_code okc_k_party_roles_b.rle_code%TYPE
2972: ,cp_object1_id1 okc_k_party_roles_b.object1_id1%TYPE
2973: ,cp_object1_id2 okc_k_party_roles_b.object1_id2%TYPE
2974: ,cp_jtot_code okc_k_party_roles_b.jtot_object1_code%TYPE)IS
2986: AND role.id = payment.cpl_id;
2987: --udhenuko Bug 5201243 Commenting as the vendor and party need not be same
2988: --AND payment.vendor_id = cp_object1_id1;
2989:
2990: CURSOR c_get_party_role_csr(cp_chr_id okc_k_headers_b.id%TYPE
2991: ,cp_rle_code okc_k_party_roles_b.rle_code%TYPE
2992: ,cp_object1_id1 okc_k_party_roles_b.object1_id1%TYPE
2993: ,cp_object1_id2 okc_k_party_roles_b.object1_id2%TYPE
2994: ,cp_jtot_code okc_k_party_roles_b.jtot_object1_code%TYPE)IS
3085: END IF;
3086: ELSE
3087: CLOSE c_get_orig_disb_csr;
3088: -- we need to create a Disbursement Setup Record for the Program Agreement
3089: -- we need to determine the id of okc_k_headers_b for the party in context. this value will be used to populate the cpl_id
3090: -- in okl_party_payment_dtls
3091: OPEN c_get_party_role_csr(cp_chr_id => p_orig_chr_id
3092: ,cp_rle_code => creq_disb_rec.rle_code
3093: ,cp_object1_id1 => creq_disb_rec.object1_id1