[Home] [Help]
352: item_not_found_error EXCEPTION;
353: CURSOR okc_tgn_csr (p_cnh_id IN NUMBER)
354: IS
355: SELECT id, tze_id
356: FROM OKC_TIMEVALUES tgn
357: WHERE cnh_id = p_cnh_id
358: AND tve_type = 'TGN';
359:
360: CURSOR okc_tal_csr (p_id IN NUMBER)
359:
360: CURSOR okc_tal_csr (p_id IN NUMBER)
361: IS
362: SELECT id, tve_type, duration, operator, before_after, datetime, tve_id_offset, uom_code, tze_id
363: FROM Okc_Timevalues
364: connect by tve_id_offset = prior id
365: start with tve_id_offset = p_id;
366:
367: l_okc_tal_rec okc_tal_csr%ROWTYPE;
743: l_tze_name OKX_TIMEZONES_V.global_timezone_name%TYPE;
744: CURSOR okc_tgd_csr (p_tve_id IN NUMBER)
745: IS
746: SELECT month, day, nth, day_of_week, hour, minute, second, tze_id
747: FROM OKC_TIMEVALUES
748: WHERE id = p_tve_id
749: and tve_type = 'TGD';
750: l_okc_tgd_rec okc_tgd_csr%ROWTYPE;
751: BEGIN
986: l_chr_id NUMBER;
987: l_cle_id NUMBER;
988: l_tve_id NUMBER;
989: l_tve_id_limited NUMBER;
990: l_tve_type OKC_TIMEVALUES.tve_type%type;
991: l_sql_string varchar2(4000);
992: l_datetime date;
993: x_msg_count NUMBER;
994: x_msg_data VARCHAR2(2000);
1025:
1026: /* Get all the rule types (e.g. NTN) from metadata which are related to timevalues.*/
1027: /* Get all the rule types (e.g. NTN) from metadata which are related to tasks.*/
1028:
1029: l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
1030: l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
1031:
1032: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1033:
1046:
1047: /* Get all the timevalues associated with the rule. Currently there is only one tve_id per rule. May be extended in
1048: future. API is flexible to handle this */
1049:
1050: l_list_of_tve_id := OKC_TIME_UTIL_PUB.get_tve_ids(l_app_id,l_rule_df_name,l_rule_type,'OKC_TIMEVALUES',
1051: l_rul_id);
1052: if l_list_of_tve_id is NULL Then
1053: goto next_row1;
1054: end if;
1053: goto next_row1;
1054: end if;
1055: l_sql_string :=
1056: 'select id, tve_id_limited, tve_type, datetime, tze_id ' ||
1057: 'from okc_timevalues ' ||
1058: 'where ((tve_type in ( ''TGD'',''TAV''))'||
1059: ' or (tve_type = ''CYL'' and interval_yn = ''N'')) ' ||
1060: 'and id in '|| l_list_of_tve_id;
1061: open tve_id_csr for l_sql_string;
1326: l_list_of_tve_id VARCHAR2(4000);
1327: l_row_not_found BOOLEAN := TRUE;
1328: l_tve_id NUMBER;
1329: l_rul_id NUMBER;
1330: l_tve_type OKC_TIMEVALUES.tve_type%type;
1331: l_datetime date;
1332: x_msg_count NUMBER;
1333: x_msg_data VARCHAR2(2000);
1334: l_sql_string varchar2(4000);
1374:
1375: /*get all the rule types (e.g. NTN) from metadata which are related to timevalues.*/
1376: /*get all the rule types (e.g. NTN) from metadata which are related to tasks.*/
1377:
1378: l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
1379: l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
1380:
1381: /* For these rules, get their ids using the contract header (line) id. */
1382:
1418:
1419: /* Get all the timevalues associated with the rule. Currently there is only one tve_id per rule. May be extended in
1420: future. API is flexible to handle this */
1421:
1422: l_list_of_tve_id := OKC_TIME_UTIL_PUB.get_tve_ids(l_app_id,l_rule_df_name,l_rule_type,'OKC_TIMEVALUES',
1423: l_rul_id);
1424: if l_list_of_tve_id is NULL Then
1425: goto next_row;
1426: end if;
1425: goto next_row;
1426: end if;
1427: l_sql_string :=
1428: 'select id, tve_id_limited, tve_type, datetime, tze_id ' ||
1429: 'from okc_timevalues ' ||
1430: 'where ((tve_type in ( ''TGD'',''TAV''))'||
1431: ' or (tve_type = ''CYL'' and interval_yn = ''N'')) ' ||
1432: 'and id in '|| l_list_of_tve_id;
1433: open tve_id_csr for l_sql_string;
1551: l_row_not_found BOOLEAN := TRUE;
1552: l_tve_id NUMBER;
1553: l_rul_id NUMBER;
1554: l_rtv_id NUMBER;
1555: l_tve_type OKC_TIMEVALUES.tve_type%type;
1556: l_sql_string varchar2(4000);
1557: l_rtvv_rec OKC_TIME_PUB.rtvv_rec_type;
1558: x_msg_count NUMBER;
1559: x_msg_data VARCHAR2(2000);
1588:
1589: /*get all the rule types (e.g. NTN) from metadata which are related to timevalues.*/
1590: /*get all the rule types (e.g. NTN) from metadata which are related to tasks.*/
1591:
1592: l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
1593: l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
1594:
1595: /* For these rules, get their ids using the contract header (line) id. */
1596:
1608:
1609: /* Get all the timevalues associated with the rule. Currently there is only one tve_id per rule. May be extended in
1610: future. API is flexible to handle this */
1611:
1612: l_list_of_tve_id := OKC_TIME_UTIL_PUB.get_tve_ids(l_app_id,l_rule_df_name,l_rule_type,'OKC_TIMEVALUES',
1613: l_rul_id);
1614: if l_list_of_tve_id is NULL Then
1615: goto next_row;
1616: end if;
1618: /* Get all resolved timevalues */
1619:
1620: l_sql_string :=
1621: 'select rtv.id rtv_id ' ||
1622: 'from okc_timevalues tve, okc_resolved_timevalues rtv ' ||
1623: 'where rtv.tve_id = tve.id ' ||
1624: 'and rtv.datetime >= :p_end_date ' ||
1625: 'and tve.id in '|| l_list_of_tve_id ;
1626: open tve_id_csr for l_sql_string using p_end_date;
1729: x_return_status OUT NOCOPY VARCHAR2) IS
1730:
1731: CURSOR rtv_csr(p_tve_id IN NUMBER, p_date IN DATE) is
1732: select rtv.id rtv_id
1733: from okc_timevalues tve, okc_resolved_timevalues rtv
1734: where rtv.tve_id = tve.id
1735: and rtv.datetime >= p_date
1736: and tve.id = p_tve_id ;
1737:
1793: x_return_status OUT NOCOPY VARCHAR2) IS
1794:
1795: CURSOR tve_id_csr(p_tve_id IN NUMBER) is
1796: select id, tve_type, datetime, tze_id
1797: from okc_timevalues
1798: where
1799: id = p_tve_id;
1800:
1801: l_tve_rec tve_id_csr%ROWTYPE;