[Home] [Help]
37: IS
38: l_SQL_str VARCHAR2(500) := null;
39: l_table_name VARCHAR2(30);
40: BEGIN
41: IF p_clndr_type = 'CLNDR' THEN l_table_name := 'DDR_R_CLNDR_YR';
42: ELSIF p_clndr_type = 'BSNS' THEN l_table_name := 'DDR_R_BSNS_YR';
43: ELSIF p_clndr_type = 'FSCL' THEN l_table_name := 'DDR_R_FSCL_YR';
44: ELSIF p_clndr_type = 'ADVR' THEN l_table_name := 'DDR_R_ADVR_YR';
45: ELSIF p_clndr_type = 'PLNG' THEN l_table_name := 'DDR_R_PLNG_YR';
52: SELECT MAX(yr_nbr)
53: FROM DDR_R_BSNS_YR
54: WHERE clndr_cd = (
55: SELECT clndr_cd
56: FROM DDR_R_CLNDR
57: WHERE org_cd = p_org_code
58: AND clndr_typ = 'BSNS'
59: )
60: )
60: )
61: -- Bug# 6866605 change start
62: AND clndr_cd = (
63: SELECT clndr_cd
64: FROM DDR_R_CLNDR
65: WHERE org_cd = p_org_code
66: AND clndr_typ = 'BSNS'
67: )
68: -- Bug# 6866605 change end
70: l_SQL_str := 'select YR_NBR, YR_STRT_DT, YR_END_DT from ' || l_table_name || ' where YR_NBR =';
71: l_SQL_str := l_SQL_str || ' (select max(YR_NBR) from ' || l_table_name;
72: IF p_clndr_type IN ('BSNS','FSCL','ADVR','PLNG')
73: THEN
74: l_SQL_str := l_SQL_str || ' where CLNDR_CD = (select CLNDR_CD from DDR_R_CLNDR';
75: l_SQL_str := l_SQL_str || ' where ORG_CD = ''' || p_org_code || ''' and CLNDR_TYP = ''' || p_clndr_type ||''')';
76: -- Bug# 6866605 change start
77: l_SQL_str := l_SQL_str || ')';
78: l_SQL_str := l_SQL_str || ' AND clndr_cd = (SELECT clndr_cd FROM DDR_R_CLNDR WHERE org_cd = ''' || p_org_code || ''' AND clndr_typ = ''' || p_clndr_type || ''')';
74: l_SQL_str := l_SQL_str || ' where CLNDR_CD = (select CLNDR_CD from DDR_R_CLNDR';
75: l_SQL_str := l_SQL_str || ' where ORG_CD = ''' || p_org_code || ''' and CLNDR_TYP = ''' || p_clndr_type ||''')';
76: -- Bug# 6866605 change start
77: l_SQL_str := l_SQL_str || ')';
78: l_SQL_str := l_SQL_str || ' AND clndr_cd = (SELECT clndr_cd FROM DDR_R_CLNDR WHERE org_cd = ''' || p_org_code || ''' AND clndr_typ = ''' || p_clndr_type || ''')';
79: ELSE
80: l_SQL_str := l_SQL_str || ')';
81: END IF;
82: -- Bug# 6866605 change end
98: )
99: IS
100: cursor cur_week is
101: select WK_NBR, WK_STRT_DT, WK_END_DT
102: from DDR_R_CLNDR_WK
103: where WK_NBR = (
104: select max(WK_NBR)
105: from DDR_R_CLNDR_WK
106: );
101: select WK_NBR, WK_STRT_DT, WK_END_DT
102: from DDR_R_CLNDR_WK
103: where WK_NBR = (
104: select max(WK_NBR)
105: from DDR_R_CLNDR_WK
106: );
107: BEGIN
108: OPEN cur_week;
109: FETCH cur_week INTO p_last_week,p_last_week_start_dt,p_last_week_end_dt;
123: )
124: IS
125: cursor cur_week is
126: select CLNDR_WK_ID, WK_CD
127: from DDR_R_CLNDR_WK
128: where p_date between TRUNC(WK_STRT_DT)
129: and TRUNC(WK_END_DT) + .99999;
130: BEGIN
131: OPEN cur_week;
266: RETURN VARCHAR2
267: IS
268: cursor cur_clndr is
269: select CLNDR_CD
270: from DDR_R_CLNDR
271: where CLNDR_TYP = p_clndr_type
272: and ORG_CD = p_org_code
273: and MFG_ORG_CD = p_mfg_org_code;
274:
271: where CLNDR_TYP = p_clndr_type
272: and ORG_CD = p_org_code
273: and MFG_ORG_CD = p_mfg_org_code;
274:
275: l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
276: BEGIN
277: OPEN cur_clndr;
278: FETCH cur_clndr INTO l_clndr_cd;
279: IF cur_clndr%NOTFOUND
278: FETCH cur_clndr INTO l_clndr_cd;
279: IF cur_clndr%NOTFOUND
280: THEN
281: l_clndr_cd := p_org_code || '-' || p_clndr_type;
282: insert into DDR_R_CLNDR (
283: CLNDR_ID,
284: MFG_ORG_CD,
285: CLNDR_CD,
286: ORG_CD,
296: LAST_UPDATE_DATE,
297: LAST_UPDATE_LOGIN
298: )
299: values (
300: DDR_R_CLNDR_SEQ.NEXTVAL,
301: p_mfg_org_code,
302: l_clndr_cd,
303: p_org_code,
304: p_clndr_type,
333: RETURN VARCHAR2
334: IS
335: cursor cur_clndr_yr (p_year IN NUMBER) is
336: select 1
337: from DDR_R_CLNDR_YR
338: where YR_NBR = p_year;
339:
340: l_start_year NUMBER;
341: l_end_year NUMBER;
795: l_month_no NUMBER;
796: l_week_no NUMBER;
797:
798: BEGIN
799: /* Check existence of record in DDR_R_CLNDR_YR and get last year details */
800: Get_Last_Year_Details('CLNDR',null,l_last_year,l_last_year_start_date,l_last_year_end_date);
801: IF l_last_year IS NULL /* Last year record does not exist */
802: THEN
803: IF p_start_year IS NULL
815: END IF;
816: -- Bug# 6863276 change end
817: END IF;
818:
819: /* Check existence of record in DDR_R_CLNDR_WK and get last week details */
820: Get_Last_Calendar_Week_Details(l_last_week,l_last_week_start_date,l_last_week_end_date);
821: IF l_last_week IS NULL /* Last week record does not exist */
822: THEN
823: l_last_week_start_date := TRUNC(l_last_year_end_date,'DAY');
843: l_curr_year_start_date := l_curr_year_end_date+1;
844: l_curr_year_end_date := ADD_MONTHS(l_curr_year_start_date,12)-1;
845: l_no_year_days := l_curr_year_end_date - l_curr_year_start_date + 1;
846: -- l_curr_year_id := TO_NUMBER(TO_CHAR(l_curr_year_start_date,g_YR_ID_format));
847: SELECT DDR_R_CLNDR_YR_SEQ.NEXTVAL
848: INTO l_curr_year_id
849: FROM DUAL;
850: l_curr_year_desc := 'CY ' || TO_CHAR(l_curr_year);
851:
848: INTO l_curr_year_id
849: FROM DUAL;
850: l_curr_year_desc := 'CY ' || TO_CHAR(l_curr_year);
851:
852: insert into DDR_R_CLNDR_YR (
853: CLNDR_YR_ID,
854: YR_CD,
855: YR_NBR,
856: YR_DESC,
903: l_qtr_no := l_qtr_no + 1;
904: l_curr_qtr_start_date := l_curr_date;
905: l_curr_qtr_end_date := ADD_MONTHS(l_curr_qtr_start_date,3)-1;
906: -- l_curr_qtr_id := TO_NUMBER(TO_CHAR(l_curr_qtr_start_date,g_QTR_ID_format));
907: SELECT DDR_R_CLNDR_QTR_SEQ.NEXTVAL
908: INTO l_curr_qtr_id
909: FROM DUAL;
910: l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
911:
908: INTO l_curr_qtr_id
909: FROM DUAL;
910: l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
911:
912: insert into DDR_R_CLNDR_QTR (
913: CLNDR_QTR_ID,
914: QTR_CD,
915: QTR_NBR,
916: QTR_DESC,
956: l_month_no := l_month_no + 1;
957: l_curr_month_start_date := l_curr_date;
958: l_curr_month_end_date := ADD_MONTHS(l_curr_month_start_date,1)-1;
959: -- l_curr_month_id := TO_NUMBER(TO_CHAR(l_curr_month_start_date,g_MNTH_ID_format));
960: SELECT DDR_R_CLNDR_MNTH_SEQ.NEXTVAL
961: INTO l_curr_month_id
962: FROM DUAL;
963: l_curr_month := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_month_no),2,'0'));
964:
961: INTO l_curr_month_id
962: FROM DUAL;
963: l_curr_month := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_month_no),2,'0'));
964:
965: insert into DDR_R_CLNDR_MNTH (
966: CLNDR_MNTH_ID,
967: MNTH_CD,
968: MNTH_NBR,
969: MNTH_DESC,
1009: l_week_no := l_week_no + 1;
1010: l_curr_week_start_date := l_curr_date;
1011: l_curr_week_end_date := l_curr_week_start_date + 7 - 1;
1012: -- l_curr_week_id := TO_NUMBER(TO_CHAR(l_curr_week_start_date,g_WK_ID_format));
1013: SELECT DDR_R_CLNDR_WK_SEQ.NEXTVAL
1014: INTO l_curr_week_id
1015: FROM DUAL;
1016: l_curr_week := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_week_no),2,'0'));
1017:
1014: INTO l_curr_week_id
1015: FROM DUAL;
1016: l_curr_week := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_week_no),2,'0'));
1017:
1018: insert into DDR_R_CLNDR_WK (
1019: CLNDR_WK_ID,
1020: WK_CD,
1021: WK_NBR,
1022: WK_DESC,
1137: l_last_year_end_date DATE;
1138:
1139: l_org_type DDR_R_ORG.ORG_TYP%TYPE;
1140: l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
1141: l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
1142: l_missing_year VARCHAR2(100);
1143:
1144: l_spl_year_flag BOOLEAN := FALSE;
1145: l_no_of_years NUMBER;
1224: /* Populate Quarter and Month array */
1225: Populate_Month_Arrays(l_month_array,l_qtr_array,l_spl_year_array,p_five_week_month_list,p_special_year_list,p_extra_week_month,l_no_of_years,l_last_year_end_date+1);
1226: l_extra_week_month := REPLACE(UPPER(p_extra_week_month),' ','');
1227:
1228: /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
1229: l_missing_year := Check_Calendar_Year_Exists('BSNS',l_last_year_end_date+1,l_no_of_years,l_spl_year_array.COUNT);
1230: IF l_missing_year IS NOT NULL
1231: THEN
1232: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
1232: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
1233: END IF;
1234:
1235: /*
1236: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='BSNS' and ORG_CD=p_org_cd;
1237: If no such record exists
1238: then
1239: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='BSNS';
1240: end if;
1235: /*
1236: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='BSNS' and ORG_CD=p_org_cd;
1237: If no such record exists
1238: then
1239: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='BSNS';
1240: end if;
1241: */
1242: l_clndr_cd := Get_Calendar('BSNS',p_org_cd,l_mfg_org_cd);
1243:
1597: l_last_year_end_date DATE;
1598:
1599: l_org_type DDR_R_ORG.ORG_TYP%TYPE;
1600: l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
1601: l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
1602: l_missing_year VARCHAR2(100);
1603:
1604: l_no_of_years NUMBER;
1605: l_no_year_days NUMBER;
1665: END IF;
1666:
1667: l_no_of_years := nvl(p_no_of_years,1);
1668:
1669: /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
1670: l_missing_year := Check_Calendar_Year_Exists('FSCL',l_last_year_end_date+1,l_no_of_years,null);
1671: IF l_missing_year IS NOT NULL
1672: THEN
1673: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
1673: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
1674: END IF;
1675:
1676: /*
1677: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='FSCL' and ORG_CD=p_org_cd;
1678: If no such record exists
1679: then
1680: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='FSCL';
1681: end if;
1676: /*
1677: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='FSCL' and ORG_CD=p_org_cd;
1678: If no such record exists
1679: then
1680: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='FSCL';
1681: end if;
1682: */
1683: l_clndr_cd := Get_Calendar('FSCL',p_org_cd,l_mfg_org_cd);
1684:
1946: l_last_year_end_date DATE;
1947:
1948: l_org_type DDR_R_ORG.ORG_TYP%TYPE;
1949: l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
1950: l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
1951: l_missing_year VARCHAR2(100);
1952:
1953: l_spl_year_flag BOOLEAN := FALSE;
1954: l_no_of_years NUMBER;
2032: /* Populate ADVR different arrays like Quarter, Period, Special Year etc. */
2033: Populate_Period_Arrays(l_period_array,l_qtr_array,l_spl_year_array,p_period_dist_list,p_week_dist_list,p_special_year_list,p_extra_week_period,l_no_of_years,l_last_year_end_date+1);
2034: l_extra_week_period := REPLACE(UPPER(p_extra_week_period),' ','');
2035:
2036: /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
2037: l_missing_year := Check_Calendar_Year_Exists('ADVR',l_last_year_end_date+1,l_no_of_years,l_spl_year_array.COUNT);
2038: IF l_missing_year IS NOT NULL
2039: THEN
2040: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
2040: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
2041: END IF;
2042:
2043: /*
2044: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='ADVR' and ORG_CD=p_org_cd;
2045: If no such record exists
2046: then
2047: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='ADVR';
2048: end if;
2043: /*
2044: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='ADVR' and ORG_CD=p_org_cd;
2045: If no such record exists
2046: then
2047: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='ADVR';
2048: end if;
2049: */
2050: l_clndr_cd := Get_Calendar('ADVR',p_org_cd,l_mfg_org_cd);
2051:
2410: l_last_year_end_date DATE;
2411:
2412: l_org_type DDR_R_ORG.ORG_TYP%TYPE;
2413: l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
2414: l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
2415: l_missing_year VARCHAR2(100);
2416:
2417: l_spl_year_flag BOOLEAN := FALSE;
2418: l_no_of_years NUMBER;
2496: /* Populate PLNG different arrays like Quarter, Period, Special Year etc. */
2497: Populate_Period_Arrays(l_period_array,l_qtr_array,l_spl_year_array,p_period_dist_list,p_week_dist_list,p_special_year_list,p_extra_week_period,l_no_of_years,l_last_year_end_date+1);
2498: l_extra_week_period := REPLACE(UPPER(p_extra_week_period),' ','');
2499:
2500: /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
2501: l_missing_year := Check_Calendar_Year_Exists('PLNG',l_last_year_end_date+1,l_no_of_years,l_spl_year_array.COUNT);
2502: IF l_missing_year IS NOT NULL
2503: THEN
2504: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
2504: Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
2505: END IF;
2506:
2507: /*
2508: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='PLNG' and ORG_CD=p_org_cd;
2509: If no such record exists
2510: then
2511: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='PLNG';
2512: end if;
2507: /*
2508: Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='PLNG' and ORG_CD=p_org_cd;
2509: If no such record exists
2510: then
2511: Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='PLNG';
2512: end if;
2513: */
2514: l_clndr_cd := Get_Calendar('PLNG',p_org_cd,l_mfg_org_cd);
2515: