144: l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
145: l_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type := null;
146: l_object_version_number BINARY_INTEGER;
147: l_tfl_ovn BINARY_INTEGER;
148: l_finance_line_id OTA_FINANCE_LINES.finance_line_id%type:= null;
149: l_booking_type VARCHAR2(4000);
150: l_error_crypt VARCHAR2(4000);
151: --
152: l_mode VARCHAR2(200);
156: -- -------------------
157: -- Finance API Vars
158: -- -------------------
159: l_auto_create_finance VARCHAR2(40);
160: fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
161: fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
162: fapi_result VARCHAR2(40);
163: fapi_from VARCHAR2(5);
164: fapi_to VARCHAR2(5);
157: -- Finance API Vars
158: -- -------------------
159: l_auto_create_finance VARCHAR2(40);
160: fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
161: fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
162: fapi_result VARCHAR2(40);
163: fapi_from VARCHAR2(5);
164: fapi_to VARCHAR2(5);
165:
162: fapi_result VARCHAR2(40);
163: fapi_from VARCHAR2(5);
164: fapi_to VARCHAR2(5);
165:
166: result_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
167: result_create_finance_line VARCHAR2(5) := 'Y';
168: result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
169:
170: l_logged_in_user NUMBER;
164: fapi_to VARCHAR2(5);
165:
166: result_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
167: result_create_finance_line VARCHAR2(5) := 'Y';
168: result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
169:
170: l_logged_in_user NUMBER;
171: l_user NUMBER;
172: l_automatic_transfer_gl VARCHAR2(40);
573:
574:
575: IF l_automatic_transfer_gl = 'Y' AND l_finance_line_id IS NOT NULL AND l_offering_id is null THEN
576:
577: UPDATE ota_finance_lines SET transfer_status = 'AT'
578: WHERE finance_line_id = l_finance_line_id;
579:
580:
581:
691:
692: -- ------------------------
693: -- Finance_cur Variables
694: -- ------------------------
695: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
696: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
697: l_transfer_status ota_finance_lines.transfer_status%TYPE;
698: lf_booking_id ota_finance_lines.booking_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
692: -- ------------------------
693: -- Finance_cur Variables
694: -- ------------------------
695: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
696: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
697: l_transfer_status ota_finance_lines.transfer_status%TYPE;
698: lf_booking_id ota_finance_lines.booking_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
700: l_sequence_number ota_finance_lines.sequence_number%TYPE;
693: -- Finance_cur Variables
694: -- ------------------------
695: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
696: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
697: l_transfer_status ota_finance_lines.transfer_status%TYPE;
698: lf_booking_id ota_finance_lines.booking_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
700: l_sequence_number ota_finance_lines.sequence_number%TYPE;
701: l_raised_date date;
694: -- ------------------------
695: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
696: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
697: l_transfer_status ota_finance_lines.transfer_status%TYPE;
698: lf_booking_id ota_finance_lines.booking_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
700: l_sequence_number ota_finance_lines.sequence_number%TYPE;
701: l_raised_date date;
702: l_finance_count number(10);
695: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
696: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
697: l_transfer_status ota_finance_lines.transfer_status%TYPE;
698: lf_booking_id ota_finance_lines.booking_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
700: l_sequence_number ota_finance_lines.sequence_number%TYPE;
701: l_raised_date date;
702: l_finance_count number(10);
703: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
696: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
697: l_transfer_status ota_finance_lines.transfer_status%TYPE;
698: lf_booking_id ota_finance_lines.booking_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
700: l_sequence_number ota_finance_lines.sequence_number%TYPE;
701: l_raised_date date;
702: l_finance_count number(10);
703: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
704: l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
699: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
700: l_sequence_number ota_finance_lines.sequence_number%TYPE;
701: l_raised_date date;
702: l_finance_count number(10);
703: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
704: l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
705: -- ------------------------
706: -- header_cur Variables
707: -- ------------------------
723: l_delegate_contact_id ota_delegate_bookings.delegate_contact_id%TYPE;
724: l_tmp_booking_id ota_delegate_bookings.booking_id%TYPE;
725: l_cancel_boolean BOOLEAN;
726:
727: CURSOR finance (p_booking_id ota_finance_lines.booking_id%TYPE) IS
728: SELECT FLN.finance_line_id finance_line_id,
729: FLN.finance_header_id finance_header_id,
730: FLN.transfer_status transfer_status,
731: FLN.booking_id booking_id,
731: FLN.booking_id booking_id,
732: FLN.object_version_number object_version_number,
733: FLN.sequence_number sequence_number,
734: FLN.Cancelled_flag cancelled_flag
735: FROM OTA_FINANCE_LINES FLN
736: WHERE FLN.booking_id = p_booking_id;
737:
738: CURSOR finance_count (p_finance_header_id ota_finance_lines.finance_header_id%TYPE) IS
739: SELECT count(*)
734: FLN.Cancelled_flag cancelled_flag
735: FROM OTA_FINANCE_LINES FLN
736: WHERE FLN.booking_id = p_booking_id;
737:
738: CURSOR finance_count (p_finance_header_id ota_finance_lines.finance_header_id%TYPE) IS
739: SELECT count(*)
740: FROM OTA_FINANCE_LINES FLN
741: WHERE FLN.finance_header_id = p_finance_header_id;
742:
736: WHERE FLN.booking_id = p_booking_id;
737:
738: CURSOR finance_count (p_finance_header_id ota_finance_lines.finance_header_id%TYPE) IS
739: SELECT count(*)
740: FROM OTA_FINANCE_LINES FLN
741: WHERE FLN.finance_header_id = p_finance_header_id;
742:
743: CURSOR header (p_booking_id ota_finance_lines.booking_id%TYPE) IS
744: SELECT FLH.finance_header_id finance_header_id,
739: SELECT count(*)
740: FROM OTA_FINANCE_LINES FLN
741: WHERE FLN.finance_header_id = p_finance_header_id;
742:
743: CURSOR header (p_booking_id ota_finance_lines.booking_id%TYPE) IS
744: SELECT FLH.finance_header_id finance_header_id,
745: FLH.cancelled_flag cancelled_flag,
746: FLH.transfer_status transfer_status,
747: FLH.object_version_number object_version_number
745: FLH.cancelled_flag cancelled_flag,
746: FLH.transfer_status transfer_status,
747: FLH.object_version_number object_version_number
748: FROM OTA_FINANCE_HEADERS FLH,
749: OTA_FINANCE_LINES FLN
750: WHERE FLH.finance_header_id = FLN.finance_header_id
751: AND FLN.booking_id = p_booking_id;
752:
753: CURSOR event_csr (p_event_id ota_events.event_id%TYPE) IS
1819:
1820: -- ------------------------
1821: -- Finance_csr Variables
1822: -- ------------------------
1823: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
1824: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1825: l_transfer_status ota_finance_lines.transfer_status%TYPE;
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1820: -- ------------------------
1821: -- Finance_csr Variables
1822: -- ------------------------
1823: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
1824: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1825: l_transfer_status ota_finance_lines.transfer_status%TYPE;
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1828: l_sequence_number ota_finance_lines.sequence_number%TYPE;
1821: -- Finance_csr Variables
1822: -- ------------------------
1823: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
1824: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1825: l_transfer_status ota_finance_lines.transfer_status%TYPE;
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1828: l_sequence_number ota_finance_lines.sequence_number%TYPE;
1829: l_finance_count number(10);
1822: -- ------------------------
1823: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
1824: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1825: l_transfer_status ota_finance_lines.transfer_status%TYPE;
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1828: l_sequence_number ota_finance_lines.sequence_number%TYPE;
1829: l_finance_count number(10);
1830: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
1823: l_finance_line_id ota_finance_lines.finance_line_id%TYPE;
1824: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1825: l_transfer_status ota_finance_lines.transfer_status%TYPE;
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1828: l_sequence_number ota_finance_lines.sequence_number%TYPE;
1829: l_finance_count number(10);
1830: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
1831: l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
1824: l_finance_header_id ota_finance_lines.finance_header_id%TYPE;
1825: l_transfer_status ota_finance_lines.transfer_status%TYPE;
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1828: l_sequence_number ota_finance_lines.sequence_number%TYPE;
1829: l_finance_count number(10);
1830: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
1831: l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
1832:
1826: lf_booking_id ota_finance_lines.booking_id%TYPE;
1827: lf_object_version_number ota_finance_lines.object_version_number%TYPE;
1828: l_sequence_number ota_finance_lines.sequence_number%TYPE;
1829: l_finance_count number(10);
1830: l_cancelled_flag ota_finance_lines.cancelled_flag%type;
1831: l_cancel_header_id ota_finance_headers.finance_header_id%TYPE;
1832:
1833: -- ------------------------
1834: -- header_csr Variables
1872: SELECT b.date_booking_placed, b.content_player_status, b.object_version_number
1873: FROM ota_delegate_bookings b
1874: WHERE b.booking_id = p_booking_id;
1875:
1876: CURSOR finance_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
1877: IS
1878: SELECT fln.finance_line_id finance_line_id,
1879: fln.finance_header_id finance_header_id,
1880: fln.transfer_status transfer_status,
1881: fln.booking_id booking_id,
1882: fln.object_version_number object_version_number,
1883: fln.sequence_number sequence_number,
1884: fln.Cancelled_flag cancelled_flag
1885: FROM ota_finance_lines fln
1886: WHERE fln.booking_id = p_booking_id;
1887:
1888: CURSOR finance_count_csr (p_finance_header_id ota_finance_lines.finance_header_id%TYPE)
1889: IS
1884: fln.Cancelled_flag cancelled_flag
1885: FROM ota_finance_lines fln
1886: WHERE fln.booking_id = p_booking_id;
1887:
1888: CURSOR finance_count_csr (p_finance_header_id ota_finance_lines.finance_header_id%TYPE)
1889: IS
1890: SELECT count(finance_line_id)
1891: FROM ota_finance_lines fln
1892: WHERE fln.finance_header_id = p_finance_header_id;
1887:
1888: CURSOR finance_count_csr (p_finance_header_id ota_finance_lines.finance_header_id%TYPE)
1889: IS
1890: SELECT count(finance_line_id)
1891: FROM ota_finance_lines fln
1892: WHERE fln.finance_header_id = p_finance_header_id;
1893:
1894: CURSOR header_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
1895: IS
1890: SELECT count(finance_line_id)
1891: FROM ota_finance_lines fln
1892: WHERE fln.finance_header_id = p_finance_header_id;
1893:
1894: CURSOR header_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
1895: IS
1896: SELECT flh.finance_header_id finance_header_id,
1897: flh.cancelled_flag cancelled_flag,
1898: flh.transfer_status transfer_status,
1897: flh.cancelled_flag cancelled_flag,
1898: flh.transfer_status transfer_status,
1899: flh.object_version_number object_version_number
1900: FROM ota_finance_headers flh,
1901: ota_finance_lines fln
1902: WHERE flh.finance_header_id = fln.finance_header_id
1903: AND fln.booking_id = p_booking_id;
1904:
1905: CURSOR C_USER(p_owner_id NUMBER) IS