[Home] [Help]
26: -- cursor to make the check
27: --
28: cursor chk_type is
29: select 1
30: from ota_suppliable_resources
31: where supplied_resource_id = p_supplied_resource_id
32: and resource_type = p_type;
33: --
34: begin
91: -- cursor to check numbers of delegates per unit
92: --
93: cursor chk_number is
94: select 1
95: from ota_suppliable_resources sr,
96: ota_events e,
97: ota_offerings off
98: where sr.supplied_resource_id = p_supplied_resource_id
99: and e.event_id = l_event_id
215: from ota_events e
216: where business_group_id = p_business_group_id
217: and exists
218: (select 1
219: from ota_suppliable_resources sr,
220: ota_resource_bookings rb
221: where rb.event_id = e.event_id
222: and sr.business_group_id = p_business_group_id
223: and sr.supplied_resource_id = rb.supplied_resource_id
235: -- cursor to perform calculations
236: --
237: cursor get_rescost is
238: select sr.cost
239: from ota_suppliable_resources sr,
240: ota_events e,
241: ota_resource_bookings rb
242: where rb.event_id = p_event_id
243: and rb.status = 'C'
285: -- cursor to check person id
286: --
287: --cursor chk_per is
288: --select person_id
289: --from ota_suppliable_resources
290: --where supplied_resource_id = p_supplied_resource_id;
291: --
292: begin
293: hr_utility.set_location('Entering:'||l_proc,5);
330: l_exists number;
331: --
332: cursor chk_type is
333: select 1
334: from ota_suppliable_resources sr,
335: hr_lookups l
336: where sr.supplied_resource_id = p_supplied_resource_id
337: and sr.resource_type = l.lookup_code
338: and l.lookup_type in ('VENUE','TRAINER');
549: -- cursor to check date ranges
550: --
551: cursor chk_dates_tsr is
552: select 1
553: from ota_suppliable_resources
554: where supplied_resource_id = p_supplied_resource_id
555: and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)
556: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
557: >= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);
559:
560: /* commented for bug6078493
561: cursor chk_dates_tsr is
562: select 1
563: from ota_suppliable_resources
564: where supplied_resource_id = p_supplied_resource_id
565: and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)
566: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
567: >= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);
568: */
569: /*
570: cursor chk_dates_tsr is
571: select 1
572: from ota_suppliable_resources
573: where supplied_resource_id = p_supplied_resource_id
574: and start_date <= p_req_from
575: and nvl(end_date,nvl(p_req_to,hr_api.g_eot)) >= nvl(p_req_to,hr_api.g_eot);
576: */
614: --
615: cursor chk_bgroup is
616: select 1
617: from ota_events e,
618: ota_suppliable_resources sr
619: where sr.supplied_resource_id = p_supplied_resource_id
620: and e.event_id = p_event_id
621: and sr.business_group_id = e.business_group_id;
622: --
903: l_return_value varchar2(1) := 'N';
904:
905: cursor get_resource_type is
906: select resource_type
907: from ota_suppliable_resources
908: where supplied_resource_id = p_supplied_resource_id;
909:
910:
911: -- For entire duration flag null or N
1103: l_return_value varchar2(1) := 'N';
1104:
1105: cursor get_resource_type is
1106: select resource_type
1107: from ota_suppliable_resources
1108: where supplied_resource_id = p_supplied_resource_id;
1109:
1110:
1111: -- For entire duration flag null or N
1321: l_resource_type varchar2(30);
1322:
1323: cursor get_resource_type is
1324: select resource_type
1325: from ota_suppliable_resources
1326: where supplied_resource_id = p_supplied_resource_id;
1327:
1328:
1329: -- For entire duration flag null or N
1631: ) IS
1632: --
1633: cursor get_resource_type is
1634: select resource_type
1635: from ota_suppliable_resources
1636: where supplied_resource_id = p_supplied_resource_id;
1637: --
1638: l_resource_type varchar2(30);
1639: l_proc varchar2(72) := g_package||'check_trainer_venue_book';
1681: -- cursor to perform check on consumable stock levels
1682: --
1683: cursor chk_slevels is
1684: select sign(sr.stock - p_quantity)
1685: from ota_suppliable_resources sr
1686: where sr.supplied_resource_id = p_supplied_resource_id
1687: and sr.consumable_flag = 'Y';
1688: --
1689: begin
1715: --
1716: l_proc varchar2(72) := g_package||'deduct_consumable_stock';
1717: l_new_stock number;
1718: --
1719: -- cursor to calculate third party update to OTA_SUPPLIABLE_RESOURCES
1720: --
1721: cursor calc_newstock is
1722: select tsr.stock - p_quantity
1723: from ota_suppliable_resources tsr
1719: -- cursor to calculate third party update to OTA_SUPPLIABLE_RESOURCES
1720: --
1721: cursor calc_newstock is
1722: select tsr.stock - p_quantity
1723: from ota_suppliable_resources tsr
1724: where tsr.supplied_resource_id = p_supplied_resource_id
1725: and tsr.consumable_flag = 'Y';
1726: --
1727: begin
1732: close calc_newstock;
1733: --
1734: -- perform update
1735: --
1736: update ota_suppliable_resources
1737: set stock = l_new_stock
1738: where supplied_resource_id = p_supplied_resource_id;
1739: --
1740: hr_utility.set_location('Leaving:'||l_proc,10);
2060:
2061: cursor c_resource
2062: IS
2063: SELECT resource_type, trainer_id
2064: FROM ota_suppliable_resources
2065: WHERE supplied_resource_id = p_supplied_resource_id ;
2066:
2067: Cursor
2068: C_Trainer_comp (p_event_id in number)