145: -- Check Update Allowed.
146: --=========================================================================
147:
148: FUNCTION check_update_allowed
149: ( p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
150: p_module_type In cn_rulesets.module_type%TYPE,
151: p_loading_status IN VARCHAR2,
152: x_loading_status OUT NOCOPY VARCHAR2,
153: x_org_id IN cn_rulesets.org_id%TYPE
146: --=========================================================================
147:
148: FUNCTION check_update_allowed
149: ( p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
150: p_module_type In cn_rulesets.module_type%TYPE,
151: p_loading_status IN VARCHAR2,
152: x_loading_status OUT NOCOPY VARCHAR2,
153: x_org_id IN cn_rulesets.org_id%TYPE
154: ) RETURN VARCHAR2 IS
149: ( p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
150: p_module_type In cn_rulesets.module_type%TYPE,
151: p_loading_status IN VARCHAR2,
152: x_loading_status OUT NOCOPY VARCHAR2,
153: x_org_id IN cn_rulesets.org_id%TYPE
154: ) RETURN VARCHAR2 IS
155:
156: l_api_name CONSTANT VARCHAR2(30) := 'check_update_allowed';
157:
165: AND org_id = l_env_org_id;
166:
167: CURSOR get_module_type IS
168: SELECT module_type
169: FROM cn_rulesets
170: WHERE ruleset_id = p_ruleset_id;
171:
172: l_module_type cn_rulesets.module_type%TYPE;
173: l_total_record NUMBER;
168: SELECT module_type
169: FROM cn_rulesets
170: WHERE ruleset_id = p_ruleset_id;
171:
172: l_module_type cn_rulesets.module_type%TYPE;
173: l_total_record NUMBER;
174:
175: BEGIN
176:
228: -- Desc : Validate the start and end dates for the ruleset
229: --
230: --=========================================================================
231: FUNCTION check_ruleset_dates
232: (p_start_date IN cn_rulesets.start_date%TYPE,
233: p_end_date IN cn_rulesets.end_date%TYPE,
234: p_module_type IN cn_rulesets.module_type%TYPE,
235: p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
236: p_org_id IN cn_rulesets.org_id%TYPE,
229: --
230: --=========================================================================
231: FUNCTION check_ruleset_dates
232: (p_start_date IN cn_rulesets.start_date%TYPE,
233: p_end_date IN cn_rulesets.end_date%TYPE,
234: p_module_type IN cn_rulesets.module_type%TYPE,
235: p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
236: p_org_id IN cn_rulesets.org_id%TYPE,
237: p_loading_status IN VARCHAR2,
230: --=========================================================================
231: FUNCTION check_ruleset_dates
232: (p_start_date IN cn_rulesets.start_date%TYPE,
233: p_end_date IN cn_rulesets.end_date%TYPE,
234: p_module_type IN cn_rulesets.module_type%TYPE,
235: p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
236: p_org_id IN cn_rulesets.org_id%TYPE,
237: p_loading_status IN VARCHAR2,
238: x_loading_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
231: FUNCTION check_ruleset_dates
232: (p_start_date IN cn_rulesets.start_date%TYPE,
233: p_end_date IN cn_rulesets.end_date%TYPE,
234: p_module_type IN cn_rulesets.module_type%TYPE,
235: p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
236: p_org_id IN cn_rulesets.org_id%TYPE,
237: p_loading_status IN VARCHAR2,
238: x_loading_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
239:
232: (p_start_date IN cn_rulesets.start_date%TYPE,
233: p_end_date IN cn_rulesets.end_date%TYPE,
234: p_module_type IN cn_rulesets.module_type%TYPE,
235: p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
236: p_org_id IN cn_rulesets.org_id%TYPE,
237: p_loading_status IN VARCHAR2,
238: x_loading_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
239:
240: l_api_name CONSTANT VARCHAR2(30) := 'check_ruleset_dates';
240: l_api_name CONSTANT VARCHAR2(30) := 'check_ruleset_dates';
241:
242: CURSOR overlap_check1 (p_date DATE) IS
243: SELECT count(*) cnt
244: FROM cn_rulesets cnr
245: WHERE p_date BETWEEN cnr.start_date AND cnr.end_date
246: AND nvl(module_type,'X') = nvl(p_module_type,'X')
247: AND ruleset_id <> p_ruleset_id
248: AND org_id=p_org_id;
251: CURSOR overlap_check2
252: (p_start_date DATE,
253: p_end_date DATE) IS
254: SELECT count(*) cnt
255: FROM cn_rulesets cnr
256: WHERE cnr.start_date BETWEEN p_start_date AND p_end_date
257: AND cnr.end_date BETWEEN p_start_date AND p_end_date
258: AND nvl(module_type,'X') = nvl(p_module_type,'X')
259: AND ruleset_id <> p_ruleset_id and
409: l_error_parameter VARCHAR2(30);
410: l_rowid VARCHAR2(4000);
411: l_sequence_number NUMBER;
412: l_count NUMBER;
413: l_ruleset_id cn_rulesets.ruleset_id%TYPE;
414: l_org_id cn_rulesets.org_id%TYPE;
415:
416: BEGIN
417:
410: l_rowid VARCHAR2(4000);
411: l_sequence_number NUMBER;
412: l_count NUMBER;
413: l_ruleset_id cn_rulesets.ruleset_id%TYPE;
414: l_org_id cn_rulesets.org_id%TYPE;
415:
416: BEGIN
417:
418: -- Standard Start of API savepoint
478: END IF;
479:
480: --Now check if the ruleset exists.
481: --If it does, then raise error
482: --else use cn_rulesets_s.nextval
483: SELECT count(1)
484: INTO l_count
485: FROM cn_rulesets
486: WHERE name = p_ruleset_rec.ruleset_name
481: --If it does, then raise error
482: --else use cn_rulesets_s.nextval
483: SELECT count(1)
484: INTO l_count
485: FROM cn_rulesets
486: WHERE name = p_ruleset_rec.ruleset_name
487: AND module_type = p_ruleset_rec.module_type
488: AND start_date = p_ruleset_rec.start_date
489: AND end_date = p_ruleset_rec.end_date and
502: RAISE FND_API.G_EXC_ERROR;
503:
504: END IF;
505:
506: SELECT Decode(p_ruleset_rec.ruleset_id, NULL, cn_rulesets_s.NEXTVAL,
507: p_ruleset_rec.ruleset_id)
508: INTO l_ruleset_id
509: FROM dual;
510:
655:
656:
657: CURSOR l_ovn_csr IS
658: SELECT nvl(object_version_number,1)
659: FROM cn_rulesets
660: WHERE ruleset_id = p_old_ruleset_rec.ruleset_id AND
661: ORG_ID=p_old_ruleset_rec.org_id;
662:
663: l_env_org_id NUMBER;
682:
683:
684: CURSOR get_ruleset_data ( l_ruleset_id NUMBER,l_org_id Number) IS
685: SELECT *
686: FROM cn_rulesets
687: WHERE ruleset_id = l_ruleset_id and
688: org_id=l_org_id;
689:
690: l_get_ruleset_data_rec get_ruleset_data%ROWTYPE;
729: into p_old_ruleset_rec.ruleset_name,
730: p_old_ruleset_rec.start_date,
731: p_old_ruleset_rec.end_date,
732: p_old_ruleset_rec.module_type
733: from cn_rulesets
734: where ruleset_id = p_ruleset_rec.ruleset_id and
735: org_id=p_ruleset_rec.org_id;
736:
737: -- API body
785:
786:
787: --Now check if the ruleset exists.
788: --If it does, then raise error
789: --else use cn_rulesets_s.nextval
790: SELECT count(1)
791: INTO l_count
792: FROM cn_rulesets
793: WHERE ruleset_id = p_old_ruleset_rec.ruleset_id and
788: --If it does, then raise error
789: --else use cn_rulesets_s.nextval
790: SELECT count(1)
791: INTO l_count
792: FROM cn_rulesets
793: WHERE ruleset_id = p_old_ruleset_rec.ruleset_id and
794: org_id=p_old_ruleset_rec.org_id;
795:
796: IF l_count = 0
929: THEN
930: RAISE fnd_api.g_exc_error;
931: END IF;
932:
933: cn_rulesets_pkg.sync_ruleset(p_ruleset_rec.ruleset_id,l_ruleset_status,l_env_org_id);
934:
935: -- Kumar
936: -- changed from GENERATED to INSTINPG ( Install in Process )
937: -- Date : 11/07/2001