[Home] [Help]
31: IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
32: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
33: END IF;
34:
35: --Update the okc_rep_contract_usages table
36: INSERT INTO okc_rep_contract_usages ( sequence_id,
37: contract_type,
38: contract_id,
39: contract_version,
32: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
33: END IF;
34:
35: --Update the okc_rep_contract_usages table
36: INSERT INTO okc_rep_contract_usages ( sequence_id,
37: contract_type,
38: contract_id,
39: contract_version,
40: business_document_type,
56: created_by,
57: last_updated_by,
58: last_update_date
59: )
60: VALUES (okc_rep_contract_usages_s.NEXTVAL,
61: p_contract_rec.contract_type,
62: p_contract_rec.contract_id,
63: p_contract_rec.contract_version_num,
64: p_document_rec.business_document_type,
123: IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125: END IF;
126:
127: --Delete from the okc_rep_contract_usages table
128: DELETE FROM okc_rep_contract_usages
129: WHERE contract_type = p_contract_rec.contract_type
130: AND contract_id = p_contract_rec.contract_id
131: AND contract_version = p_contract_rec.contract_version_num;
124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125: END IF;
126:
127: --Delete from the okc_rep_contract_usages table
128: DELETE FROM okc_rep_contract_usages
129: WHERE contract_type = p_contract_rec.contract_type
130: AND contract_id = p_contract_rec.contract_id
131: AND contract_version = p_contract_rec.contract_version_num;
132:
1276: x_return_status := FND_API.G_RET_STS_SUCCESS;
1277:
1278: --Find the contracts to be deleted w.r.t the document
1279:
1280: l_query := 'SELECT contract_id FROM okc_rep_contract_usages
1281: WHERE business_document_type = ''' || p_document_rec.business_document_type ||
1282: ''' AND business_document_id = ' || p_document_rec.business_document_id;
1283:
1284: IF p_document_rec.business_document_version IS NOT NULL THEN
1627: /*
1628: Creates a new version of the contract contract in draft status.
1629: Updates to the contract should be done from UI.
1630:
1631: The relation of parent document which is obtained in p_document_rec is stored in the okc_rep_contract_usages table
1632: */
1633:
1634: Procedure create_new_contract_version(p_api_version IN NUMBER,
1635: p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
1672: END IF;
1673:
1674: x_return_status := FND_API.G_RET_STS_SUCCESS;
1675:
1676: l_query := 'SELECT contract_id FROM okc_rep_contract_usages
1677: WHERE business_document_type = ''' || p_document_rec.business_document_type ||
1678: ''' AND business_document_id = ' || p_document_rec.business_document_id;
1679:
1680: IF p_document_rec.business_document_version IS NOT NULL THEN
1793: l_api_version CONSTANT NUMBER := 1.0;
1794:
1795: CURSOR c_contract_exist_csr IS
1796: SELECT orcu.contract_type, orcu.contract_id, orcu.contract_version
1797: FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
1798: WHERE orcu.business_document_type = p_document_rec.business_document_type
1799: AND orcu.business_document_id = p_document_rec.business_document_id
1800: --AND orcu.business_document_version = p_document_rec.business_document_version
1801: AND orcu.contract_type = Nvl(x_contract_type,orcu.contract_type)
1866: WHERE contract_id = p_contract_id
1867: AND contract_type = p_contract_type;
1868:
1869: CURSOR c_get_contract_id_csr IS
1870: SELECT contract_type, contract_id FROM okc_rep_contract_usages
1871: WHERE business_document_id = p_document_rec.business_document_id
1872: AND business_document_type = p_document_rec.business_document_type;
1873:
1874: contract_rec contract_csr%ROWTYPE;
2256:
2257: CURSOR c_contract_exist_csr IS
2258: SELECT orcu.contract_type, orcu.contract_id, orcu.contract_version, orca.contract_status_code ,
2259: orca.sbcr_coordination_type
2260: FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
2261: WHERE orcu.business_document_type = p_document_rec.business_document_type
2262: AND orcu.business_document_id = p_document_rec.business_document_id
2263: --AND orcu.business_document_version = p_document_rec.business_document_version
2264: AND orcu.contract_type = Nvl(x_contract_rec.contract_type,orcu.contract_type)
2351: x_return_status := FND_API.G_RET_STS_SUCCESS;
2352:
2353: --Find the contracts to be deleted w.r.t the document
2354:
2355: l_query := 'SELECT contract_id, contract_version FROM okc_rep_contract_usages
2356: WHERE business_document_type = ''' || p_document_rec.business_document_type ||
2357: ''' AND business_document_id = ' || p_document_rec.business_document_id;
2358:
2359: IF p_document_rec.business_document_version IS NOT NULL THEN
2712: END IF;
2713:
2714: --Find the contracts to be deleted w.r.t the document
2715:
2716: l_query := 'SELECT contract_id, contract_version FROM okc_rep_contract_usages
2717: WHERE business_document_type = ''' || p_document_rec.business_document_type ||
2718: ''' AND business_document_id = ' || p_document_rec.business_document_id;
2719:
2720: IF p_document_rec.business_document_version IS NOT NULL THEN
2835: END cancel_contract;
2836:
2837:
2838: /*
2839: * Procedure to migrate date from okc_rep_contract_rels table to okc_rep_contract_usages table.
2840: * This migration is applicable for 12.2.2.
2841: * 1. For one row in okc_rep_contract_rels table, all the target related contracts are brought and these are inserted
2842: * against each version of source contract.
2843: * 2. If the relationship(combination of contract_id, contract_type, contract_version_num, business_document_id, business_document_type,
2840: * This migration is applicable for 12.2.2.
2841: * 1. For one row in okc_rep_contract_rels table, all the target related contracts are brought and these are inserted
2842: * against each version of source contract.
2843: * 2. If the relationship(combination of contract_id, contract_type, contract_version_num, business_document_id, business_document_type,
2844: business_document_version, relationship_role_id) already exists in the new table (okc_rep_contract_usages), then this procedure doesn't insert it again in the new table
2845: * 3. Marks the rows in okc_rep_contract_usagaes with source_code as 'MIGRATION' and populates corresponding requires_id, program_id.
2846: *
2847: * Parameters :
2848: * p_purge_and_rerun if 'Yes', deletes all rows in okc_rep_contract_usages with source_code as 'MIGRATION and migrates the date
2844: business_document_version, relationship_role_id) already exists in the new table (okc_rep_contract_usages), then this procedure doesn't insert it again in the new table
2845: * 3. Marks the rows in okc_rep_contract_usagaes with source_code as 'MIGRATION' and populates corresponding requires_id, program_id.
2846: *
2847: * Parameters :
2848: * p_purge_and_rerun if 'Yes', deletes all rows in okc_rep_contract_usages with source_code as 'MIGRATION and migrates the date
2849: * if 'No', starts migrating the data, if the row already exists doesn't insert again.
2850: * default value : 'No' (if no value is entered by the user, then it takes as 'No')
2851: *
2852: * p_simulate if 'Yes', starts migrating the data, logs all successful migrated rows and errored rows. But dont' commmit the data.
2915: okc_rep_contracts_all tgt
2916: WHERE src.contract_id = rels.contract_id
2917: AND tgt.contract_id = rels.related_contract_id
2918: --to avoid reruns of this procedure or on abnormal run of this program
2919: AND NOT EXISTS (SELECT 1 FROM okc_rep_contract_usages new_rels
2920: WHERE new_rels.contract_id = src.contract_id
2921: AND new_rels.contract_type = src.contract_type
2922: AND new_rels.contract_version = src.contract_version_num
2923: AND new_rels.business_document_id = tgt.contract_id
2944: okc_rep_contracts_all tgt
2945: WHERE src_vers.contract_id = rels.contract_id
2946: AND tgt.contract_id = rels.related_contract_id
2947: --to avoid reruns of this procedure or on abnormal run of this program
2948: AND NOT EXISTS (SELECT 1 FROM okc_rep_contract_usages new_rels
2949: WHERE new_rels.contract_id = src_vers.contract_id
2950: AND new_rels.contract_type = src_vers.contract_type
2951: AND new_rels.contract_version = src_vers.contract_version_num
2952: AND new_rels.business_document_id = tgt.contract_id
2982: FROM okc_rep_contract_rels rels, okc_rep_contracts_all con
2983: WHERE rels.contract_id = con.contract_id;
2984:
2985: SELECT count(1) INTO l_already_migrated
2986: FROM okc_rep_contract_usages usages
2987: WHERE source_code = 'MIGRATION';
2988: EXCEPTION
2989: WHEN OTHERS THEN
2990: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occured: ' || sqlerrm);
2994: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Expected no. of rows to be migrated: ' || l_source_rows);
2995: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows which are already migrated (rows with source_code as MIGRATION): ' || l_already_migrated);
2996:
2997: IF nvl(p_purge_and_rerun, 'No') = 'Yes' THEN
2998: DELETE FROM okc_rep_contract_usages
2999: WHERE source_code = 'MIGRATION';
3000:
3001: IF SQL%FOUND THEN
3002: FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of rows purged in okc_rep_contract_usages table: ' || sql%rowcount);
2998: DELETE FROM okc_rep_contract_usages
2999: WHERE source_code = 'MIGRATION';
3000:
3001: IF SQL%FOUND THEN
3002: FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of rows purged in okc_rep_contract_usages table: ' || sql%rowcount);
3003: END IF;
3004: END IF;
3005:
3006: OPEN c_migrate_rels;
3012: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Fetched ' || l_src_contract_ids.count || 'rows.');
3013:
3014: BEGIN
3015: FORALL i IN 1 .. l_src_contract_ids.count SAVE EXCEPTIONS
3016: INSERT INTO okc_rep_contract_usages
3017: (sequence_id,
3018: contract_type,
3019: contract_id,
3020: contract_version,
3035: last_updated_by,
3036: last_update_date,
3037: last_update_login)
3038: VALUES
3039: (okc_rep_contract_usages_s.NEXTVAL,
3040: l_src_contract_types(i),
3041: l_src_contract_ids(i),
3042: l_src_contract_ver_nums(i),
3043: l_tgt_contract_types(i),
3083:
3084: IF nvl(p_simulate, 'Yes') = 'No' THEN
3085: --After migration commiting the data, if simulate is 'No'
3086: COMMIT;
3087: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done with the migrating data from okc_rep_conract_rels to okc_rep_contract_usages. COMMITTED MIGRATED DATA');
3088: END IF;
3089:
3090: IF nvl(p_simulate, 'Yes') = 'Yes' THEN -- if simulate = 'Yes', then there is not need to commit data.
3091: ROLLBACK;