eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_CA_GEN_002 dependencies on IGS_CA_DA_INST

Line 39: -- This module accepts a IGS_CA_DA_INST date value which has been derived

35: (reverse chronological order - newest change first)
36: ***************************************************************/
37:
38: BEGIN -- calp_clc_daio_cnstrt
39: -- This module accepts a IGS_CA_DA_INST date value which has been derived
40: -- from a IGS_CA_DA_OFST, attempts to resolve any dt_alias_offset_constrts
41: -- which exist for the IGS_CA_DA_OFST and then returns the modified date
42: -- value. Refer to S_DT_OFFSET_CONSTRAINT_TYPE table for list of valid
43: -- constraint types.

Line 64: v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;

60: v_mod_count PLS_INTEGER;
61: v_constraint_count PLS_INTEGER;
62: v_loop_count PLS_INTEGER;
63: v_message_name VARCHAR2(30) ;
64: v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
65: ---------------------------------------------------------
66: -- Function: calpl_holiday_resolve
67: -- This function try to resolve the constraint when the
68: -- s_dt_alias_offset_type = 'HOLIDAY'

Line 99: FROM IGS_CA_DA_INST dai,

95: -- The FK between the 2 tables is a composite one of cal_type and sequence_number.
96:
97: CURSOR c_m_alias_val1 IS
98: SELECT TRUNC(dai.absolute_val)
99: FROM IGS_CA_DA_INST dai,
100: IGS_CA_INST ci,
101: IGS_CA_TYPE ct,
102: IGS_CA_STAT cs
103: WHERE dai.CAL_TYPE = ci.CAL_TYPE AND

Line 113: FROM IGS_CA_DA_INST dai,

109: ORDER BY dai.absolute_val ASC;
110:
111: CURSOR c_m_alias_val2 IS
112: SELECT TRUNC(dai.absolute_val)
113: FROM IGS_CA_DA_INST dai,
114: IGS_CA_INST ci,
115: IGS_CA_TYPE ct,
116: IGS_CA_STAT cs
117: WHERE dai.CAL_TYPE = ci.CAL_TYPE AND

Line 126: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS

122: cs.s_cal_status = cst_active
123: ORDER BY dai.absolute_val DESC;
124:
125: CURSOR c_holiday (
126: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
127: SELECT 'x'
128: FROM IGS_CA_TYPE ct
129: WHERE ct.S_CAL_CAT = cst_holiday AND
130: EXISTS (SELECT 'x'

Line 137: FROM IGS_CA_DA_INST dai

133: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
134: ci.CAL_STATUS = cs.CAL_STATUS AND
135: cs.s_cal_status = cst_active AND
136: EXISTS (SELECT 'x'
137: FROM IGS_CA_DA_INST dai
138: WHERE dai.CAL_TYPE = ct.CAL_TYPE AND
139: TRUNC(dai.absolute_val) = cp_alias_val));
140: BEGIN
141:

Line 253: FROM IGS_CA_DA_INST dai1,

249: -- Replaced it with 2 cursors, which use and ORDER BY clause instead of a function.
250:
251: CURSOR c_m_alias_val2a IS
252: SELECT TRUNC(dai2.absolute_val)
253: FROM IGS_CA_DA_INST dai1,
254: IGS_CA_DA_INST dai2,
255: IGS_CA_DA_INST_PAIR daip,
256: IGS_CA_INST ci,
257: IGS_CA_TYPE ct,

Line 254: IGS_CA_DA_INST dai2,

250:
251: CURSOR c_m_alias_val2a IS
252: SELECT TRUNC(dai2.absolute_val)
253: FROM IGS_CA_DA_INST dai1,
254: IGS_CA_DA_INST dai2,
255: IGS_CA_DA_INST_PAIR daip,
256: IGS_CA_INST ci,
257: IGS_CA_TYPE ct,
258: IGS_CA_STAT cs

Line 255: IGS_CA_DA_INST_PAIR daip,

251: CURSOR c_m_alias_val2a IS
252: SELECT TRUNC(dai2.absolute_val)
253: FROM IGS_CA_DA_INST dai1,
254: IGS_CA_DA_INST dai2,
255: IGS_CA_DA_INST_PAIR daip,
256: IGS_CA_INST ci,
257: IGS_CA_TYPE ct,
258: IGS_CA_STAT cs
259: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND

Line 276: FROM IGS_CA_DA_INST dai1,

272: ORDER BY dai2.absolute_val DESC;
273:
274: CURSOR c_m_alias_val2b IS
275: SELECT TRUNC(dai1.absolute_val)
276: FROM IGS_CA_DA_INST dai1,
277: IGS_CA_DA_INST dai2,
278: IGS_CA_DA_INST_PAIR daip,
279: IGS_CA_INST ci,
280: IGS_CA_TYPE ct,

Line 277: IGS_CA_DA_INST dai2,

273:
274: CURSOR c_m_alias_val2b IS
275: SELECT TRUNC(dai1.absolute_val)
276: FROM IGS_CA_DA_INST dai1,
277: IGS_CA_DA_INST dai2,
278: IGS_CA_DA_INST_PAIR daip,
279: IGS_CA_INST ci,
280: IGS_CA_TYPE ct,
281: IGS_CA_STAT cs

Line 278: IGS_CA_DA_INST_PAIR daip,

274: CURSOR c_m_alias_val2b IS
275: SELECT TRUNC(dai1.absolute_val)
276: FROM IGS_CA_DA_INST dai1,
277: IGS_CA_DA_INST dai2,
278: IGS_CA_DA_INST_PAIR daip,
279: IGS_CA_INST ci,
280: IGS_CA_TYPE ct,
281: IGS_CA_STAT cs
282: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND

Line 298: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS

294: dai2.ci_sequence_number = daip.related_ci_sequence_number
295: ORDER BY dai1.absolute_val ASC;
296:
297: CURSOR c_instbreak (
298: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
299: SELECT 'x'
300: FROM IGS_CA_TYPE ct
301: WHERE ct.S_CAL_CAT = cst_holiday AND
302: EXISTS (SELECT 'x'

Line 309: FROM IGS_CA_DA_INST dai1,

305: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
306: ci.CAL_STATUS = cs.CAL_STATUS AND
307: cs.s_cal_status = cst_active AND
308: EXISTS (SELECT 'x'
309: FROM IGS_CA_DA_INST dai1,
310: IGS_CA_DA_INST dai2,
311: IGS_CA_DA_INST_PAIR daip
312: WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
313: dai1.DT_ALIAS = daip.DT_ALIAS AND

Line 310: IGS_CA_DA_INST dai2,

306: ci.CAL_STATUS = cs.CAL_STATUS AND
307: cs.s_cal_status = cst_active AND
308: EXISTS (SELECT 'x'
309: FROM IGS_CA_DA_INST dai1,
310: IGS_CA_DA_INST dai2,
311: IGS_CA_DA_INST_PAIR daip
312: WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
313: dai1.DT_ALIAS = daip.DT_ALIAS AND
314: dai1.sequence_number = daip.dai_sequence_number AND

Line 311: IGS_CA_DA_INST_PAIR daip

307: cs.s_cal_status = cst_active AND
308: EXISTS (SELECT 'x'
309: FROM IGS_CA_DA_INST dai1,
310: IGS_CA_DA_INST dai2,
311: IGS_CA_DA_INST_PAIR daip
312: WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
313: dai1.DT_ALIAS = daip.DT_ALIAS AND
314: dai1.sequence_number = daip.dai_sequence_number AND
315: dai1.CAL_TYPE = daip.CAL_TYPE AND

Line 409: p_alias_val IN OUT NOCOPY IGS_CA_DA_INST.absolute_val%TYPE,

405: ---------------------------------------------------------
406: PROCEDURE calpl_constraint_resolve (
407: p_constraint_count IN OUT NOCOPY PLS_INTEGER,
408: p_mod_count IN OUT NOCOPY PLS_INTEGER,
409: p_alias_val IN OUT NOCOPY IGS_CA_DA_INST.absolute_val%TYPE,
410: p_message_name OUT NOCOPY VARCHAR2 )
411: AS
412: BEGIN -- This local procedure is try to resolve the clashed constraint record.
413: DECLARE

Line 420: FROM IGS_CA_DA_INST_OFCNT daoc

416: CURSOR c_daoc IS
417: SELECT daoc.S_DT_OFFSET_CONSTRAINT_TYPE,
418: daoc.constraint_condition,
419: daoc.constraint_resolution
420: FROM IGS_CA_DA_INST_OFCNT daoc
421: WHERE daoc.DT_ALIAS = p_dt_alias AND
422: daoc.dai_sequence_number = p_sequence_number AND
423: daoc.CAL_TYPE = p_cal_type AND
424: daoc.ci_sequence_number = p_ci_sequence_number;

Line 572: -- This module accepts a IGS_CA_DA_INST date value which has been derived

568: p_message_name OUT NOCOPY varchar2 )
569: RETURN DATE AS
570: gv_other_detail VARCHAR2(255);
571: BEGIN -- calp_clc_dao_cnstrt
572: -- This module accepts a IGS_CA_DA_INST date value which has been derived
573: -- from a IGS_CA_DA_OFST, attempts to resolve any dt_alias_offset_constrts
574: -- which exist for the IGS_CA_DA_OFST and then returns the modified date
575: -- value. Refer to S_DT_OFFSET_CONSTRAINT_TYPE table for list of valid
576: -- constraint types.

Line 595: v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;

591: v_mod_count NUMBER(5);
592: v_constraint_count NUMBER(5);
593: v_loop_count NUMBER(5);
594: v_message_name varchar2(30);
595: v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
596: ---------------------------------------------------------
597: -- Function: calpl_holiday_resolve
598: -- This function try to resolve the constraint when the
599: -- s_dt_alias_offset_type = 'HOLIDAY'

Line 620: FROM IGS_CA_DA_INST dai,

616: v_max_alias_val DATE DEFAULT NULL;
617: v_min_alias_val DATE DEFAULT NULL;
618: CURSOR c_m_alias_val IS
619: SELECT TRUNC(max(dai.absolute_val)), TRUNC(min(dai.absolute_val))
620: FROM IGS_CA_DA_INST dai,
621: IGS_CA_INST ci,
622: IGS_CA_TYPE ct,
623: IGS_CA_STAT cs
624: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND

Line 630: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS

626: cs.s_cal_status = ci.CAL_STATUS AND
627: cs.s_cal_status = cst_active AND
628: dai.CAL_TYPE = ci.CAL_TYPE;
629: CURSOR c_holiday (
630: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
631: SELECT 'x'
632: FROM IGS_CA_TYPE ct
633: WHERE ct.S_CAL_CAT = cst_holiday AND
634: EXISTS (SELECT 'x'

Line 641: FROM IGS_CA_DA_INST dai

637: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
638: ci.CAL_STATUS = cs.CAL_STATUS AND
639: cs.s_cal_status = cst_active AND
640: EXISTS (SELECT 'x'
641: FROM IGS_CA_DA_INST dai
642: WHERE dai.CAL_TYPE = ct.CAL_TYPE AND
643: TRUNC(dai.absolute_val)= cp_alias_val));
644: BEGIN
645: OPEN c_m_alias_val;

Line 741: FROM IGS_CA_DA_INST dai1,

737: v_max_alias_val DATE DEFAULT NULL;
738: v_min_alias_val DATE DEFAULT NULL;
739: CURSOR c_m_alias_val2 IS
740: SELECT TRUNC(MAX(dai2.absolute_val)), TRUNC(MIN(dai1.absolute_val))
741: FROM IGS_CA_DA_INST dai1,
742: IGS_CA_DA_INST dai2,
743: IGS_CA_DA_INST_PAIR daip,
744: IGS_CA_INST ci,
745: IGS_CA_TYPE ct,

Line 742: IGS_CA_DA_INST dai2,

738: v_min_alias_val DATE DEFAULT NULL;
739: CURSOR c_m_alias_val2 IS
740: SELECT TRUNC(MAX(dai2.absolute_val)), TRUNC(MIN(dai1.absolute_val))
741: FROM IGS_CA_DA_INST dai1,
742: IGS_CA_DA_INST dai2,
743: IGS_CA_DA_INST_PAIR daip,
744: IGS_CA_INST ci,
745: IGS_CA_TYPE ct,
746: IGS_CA_STAT cs

Line 743: IGS_CA_DA_INST_PAIR daip,

739: CURSOR c_m_alias_val2 IS
740: SELECT TRUNC(MAX(dai2.absolute_val)), TRUNC(MIN(dai1.absolute_val))
741: FROM IGS_CA_DA_INST dai1,
742: IGS_CA_DA_INST dai2,
743: IGS_CA_DA_INST_PAIR daip,
744: IGS_CA_INST ci,
745: IGS_CA_TYPE ct,
746: IGS_CA_STAT cs
747: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND

Line 761: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS

757: dai2.sequence_number = daip.related_dai_sequence_number AND
758: dai2.CAL_TYPE = daip.related_cal_type AND
759: dai2.ci_sequence_number = daip.related_ci_sequence_number;
760: CURSOR c_instbreak (
761: cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
762: SELECT 'x'
763: FROM IGS_CA_TYPE ct
764: WHERE ct.S_CAL_CAT = cst_holiday AND
765: EXISTS (SELECT 'x'

Line 772: FROM IGS_CA_DA_INST dai1,

768: WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
769: ci.CAL_STATUS = cs.CAL_STATUS AND
770: cs.s_cal_status = cst_active AND
771: EXISTS (SELECT 'x'
772: FROM IGS_CA_DA_INST dai1,
773: IGS_CA_DA_INST dai2,
774: IGS_CA_DA_INST_PAIR daip
775: WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
776: dai1.DT_ALIAS = daip.DT_ALIAS AND

Line 773: IGS_CA_DA_INST dai2,

769: ci.CAL_STATUS = cs.CAL_STATUS AND
770: cs.s_cal_status = cst_active AND
771: EXISTS (SELECT 'x'
772: FROM IGS_CA_DA_INST dai1,
773: IGS_CA_DA_INST dai2,
774: IGS_CA_DA_INST_PAIR daip
775: WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
776: dai1.DT_ALIAS = daip.DT_ALIAS AND
777: dai1.sequence_number = daip.dai_sequence_number AND

Line 774: IGS_CA_DA_INST_PAIR daip

770: cs.s_cal_status = cst_active AND
771: EXISTS (SELECT 'x'
772: FROM IGS_CA_DA_INST dai1,
773: IGS_CA_DA_INST dai2,
774: IGS_CA_DA_INST_PAIR daip
775: WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
776: dai1.DT_ALIAS = daip.DT_ALIAS AND
777: dai1.sequence_number = daip.dai_sequence_number AND
778: dai1.CAL_TYPE = daip.CAL_TYPE AND

Line 865: p_alias_val IN OUT NOCOPY IGS_CA_DA_INST.absolute_val%TYPE,

861: ---------------------------------------------------------
862: PROCEDURE calpl_constraint_resolve (
863: p_constraint_count IN OUT NOCOPY NUMBER,
864: p_mod_count IN OUT NOCOPY NUMBER,
865: p_alias_val IN OUT NOCOPY IGS_CA_DA_INST.absolute_val%TYPE,
866: p_message_name OUT NOCOPY varchar2)
867: AS
868: BEGIN -- This local procedure is try to resolve the clashed constraint record.
869: DECLARE

Line 1028: v_dt_alias_instance_rec IGS_CA_DA_INST_V%ROWTYPE;

1024: RETURN DATE AS
1025: v_other_detail VARCHAR(255);
1026: v_alias_value DATE;
1027: v_dt_alias_offset_rec IGS_CA_DA_OFST%ROWTYPE;
1028: v_dt_alias_instance_rec IGS_CA_DA_INST_V%ROWTYPE;
1029: v_date_offset DATE := NULL;
1030: CURSOR c_dt_alias_offset
1031: IS
1032: SELECT *

Line 1038: FROM IGS_CA_DA_INST_V

1034: WHERE DT_ALIAS = p_dt_alias;
1035: CURSOR c_dt_alias_instance
1036: IS
1037: SELECT *
1038: FROM IGS_CA_DA_INST_V
1039: WHERE DT_ALIAS = v_dt_alias_offset_rec.offset_dt_alias and
1040: CAL_TYPE = p_cal_type and
1041: ci_sequence_number = p_ci_sequence_number
1042: ORDER BY alias_val ASC;

Line 1106: -- Calculate a IGS_CA_DA_INST.alias_value from a

1102: p_message_name OUT NOCOPY VARCHAR2 )
1103: RETURN DATE AS
1104: gv_other_detail VARCHAR2(255);
1105: BEGIN -- calp_clc_dt_from_dai
1106: -- Calculate a IGS_CA_DA_INST.alias_value from a
1107: -- IGS_CA_DA_INST_OFST.
1108: DECLARE
1109: CURSOR c_daio IS
1110: SELECT daio.DT_ALIAS,

Line 1107: -- IGS_CA_DA_INST_OFST.

1103: RETURN DATE AS
1104: gv_other_detail VARCHAR2(255);
1105: BEGIN -- calp_clc_dt_from_dai
1106: -- Calculate a IGS_CA_DA_INST.alias_value from a
1107: -- IGS_CA_DA_INST_OFST.
1108: DECLARE
1109: CURSOR c_daio IS
1110: SELECT daio.DT_ALIAS,
1111: daio.dai_sequence_number,

Line 1122: FROM IGS_CA_DA_INST_OFST daio

1118: daio.day_offset,
1119: daio.week_offset,
1120: daio.month_offset,
1121: daio.year_offset
1122: FROM IGS_CA_DA_INST_OFST daio
1123: WHERE daio.DT_ALIAS = p_dt_alias AND
1124: daio.dai_sequence_number = p_dai_sequence_number AND
1125: daio.CAL_TYPE = p_cal_type AND
1126: daio.ci_sequence_number = p_ci_sequence_number;

Line 1134: FROM IGS_CA_DA_INST dai

1130: dai.sequence_number,
1131: dai.CAL_TYPE,
1132: dai.ci_sequence_number,
1133: dai.absolute_val
1134: FROM IGS_CA_DA_INST dai
1135: WHERE dai.DT_ALIAS = v_c_daio_rec.offset_dt_alias AND
1136: dai.sequence_number = v_c_daio_rec.offset_dai_sequence_number AND
1137: dai.CAL_TYPE = v_c_daio_rec.offset_cal_type AND
1138: dai.ci_sequence_number = v_c_daio_rec.offset_ci_sequence_number;

Line 1144: FROM IGS_CA_DA_INST_OFCNT daioc

1140: SELECT daioc.DT_ALIAS,
1141: daioc.dai_sequence_number,
1142: daioc.CAL_TYPE,
1143: daioc.ci_sequence_number
1144: FROM IGS_CA_DA_INST_OFCNT daioc
1145: WHERE daioc.DT_ALIAS = p_dt_alias AND
1146: daioc.dai_sequence_number = p_dai_sequence_number AND
1147: daioc.CAL_TYPE = p_cal_type AND
1148: daioc.ci_sequence_number = p_ci_sequence_number;

Line 1152: dt_alias_inst_offset_c_row IGS_CA_DA_INST_OFCNT%ROWTYPE;

1148: daioc.ci_sequence_number = p_ci_sequence_number;
1149: v_c_dai_rec c_dai%ROWTYPE;
1150: v_c_daioc_rec c_daioc%ROWTYPE;
1151: v_alias_value DATE DEFAULT NULL;
1152: dt_alias_inst_offset_c_row IGS_CA_DA_INST_OFCNT%ROWTYPE;
1153: v_other_detail VARCHAR(255);
1154: BEGIN
1155: p_message_name :=NULL;
1156: -- Find IGS_CA_DA_INST_OFST

Line 1156: -- Find IGS_CA_DA_INST_OFST

1152: dt_alias_inst_offset_c_row IGS_CA_DA_INST_OFCNT%ROWTYPE;
1153: v_other_detail VARCHAR(255);
1154: BEGIN
1155: p_message_name :=NULL;
1156: -- Find IGS_CA_DA_INST_OFST
1157: OPEN c_daio;
1158: FETCH c_daio INTO v_c_daio_rec;
1159: IF (c_daio%NOTFOUND) THEN
1160: CLOSE c_daio;

Line 1166: -- Find IGS_CA_DA_INST

1162: v_alias_value := NULL;
1163: RETURN v_alias_value;
1164: END IF;
1165: CLOSE c_daio;
1166: -- Find IGS_CA_DA_INST
1167: OPEN c_dai;
1168: FETCH c_dai INTO v_c_dai_rec;
1169: IF (c_dai%NOTFOUND) THEN
1170: CLOSE c_dai;