CREATE OR REPLACE PACKAGE BODY apps.xxey_gl_rev_transfer_pk AS
*********************************************************************************
-- Declare global variables
gc_record_status VARCHAR2 (1);
gc_code_pointer VARCHAR2 (200);
gc_error_code VARCHAR2 (200);
gc_error_message VARCHAR2 (2000);
gn_appl_id NUMBER := fnd_profile.VALUE ('APPLICATION_ID');
gn_set_of_books_id NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
gn_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
gn_request_id NUMBER := fnd_global.conc_request_id;
gn_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
ln_p_count NUMBER;
ln_e_count NUMBER;
ln_v_count NUMBER;
gc_stat_msg VARCHAR2 (2000);
gc_je_source_name VARCHAR2 (30) := 'ORION';
gc_je_category_name VARCHAR2 (30) := 'REV HOLIDAY';
ln_header_id NUMBER;
ln_line_num NUMBER;
ln_a_count NUMBER;
ln_s_count NUMBER;
TYPE conc_requests_tabtype IS TABLE OF fnd_concurrent_requests.request_id%TYPE NOT NULL
INDEX BY BINARY_INTEGER;
-- Cursor to find the processed records
CURSOR get_processd_rev_jurnl_c (
p_source gl_je_headers.je_source%TYPE
, p_cat gl_je_headers.je_category%TYPE
, p_ccid gl_je_lines.code_combination_id%TYPE
, p_jl_date gl_je_lines.effective_date%TYPE
, p_bp_type gl_je_lines.reference_1%TYPE
, p_orig_system_ref_line_id gl_je_lines.reference_2%TYPE
, p_data_source gl_je_lines.reference_3%TYPE
, p_interface_line_attr1 gl_je_lines.reference_4%TYPE) IS
SELECT jel.je_header_id
, jel.je_line_num
FROM apps.gl_je_lines jel
, apps.gl_je_headers jeh
WHERE jel.je_header_id = jeh.je_header_id
AND jeh.je_source = p_source
AND jeh.je_category = p_cat
AND jel.code_combination_id = p_ccid
AND jel.set_of_books_id = gn_set_of_books_id
AND jel.effective_date = p_jl_date
AND jel.reference_1 = p_bp_type
AND jel.reference_2 = p_orig_system_ref_line_id
--AND jel.reference_3 = p_data_source
AND jel.reference_4 = p_interface_line_attr1;
-- Declare global cursors
CURSOR get_stats_c IS
SELECT COUNT (DECODE (record_status, 'P', 'P') ) p_count
, COUNT (DECODE (record_status, 'E', 'E') ) e_count
, COUNT (DECODE (record_status, 'V', 'V') ) v_count
, COUNT (DECODE (record_status, 'A', 'A') ) a_count
, COUNT (DECODE (record_status, 'S', 'S') ) s_count
FROM xxeyar_invoices_int_stage
WHERE request_id = gn_request_id;
--gc_record_status varchar2(1);
PROCEDURE validate_rev_transfer (
errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_start_date IN VARCHAR2
, p_end_date IN VARCHAR2) IS
pp_start_date DATE;
pp_end_date DATE;
lc_segment1 VARCHAR2 (30);
lc_segment3_cr VARCHAR2 (30);
lc_segment3_dr VARCHAR2 (30);
lc_segment4_dr VARCHAR2 (30);
lc_segment5 VARCHAR2 (30);
lc_segment6 VARCHAR2 (30);
lc_segment4_cr VARCHAR2 (30);
lc_currency_code VARCHAR2 (3);
lc_je_source_name VARCHAR2 (30);
lc_je_cat_name VARCHAR2 (30);
lc_reference10 VARCHAR2 (240);
lc_customer VARCHAR2 (150);
ln_cnt NUMBER;
ln_tot_count NUMBER;
ln_reject_count NUMBER;
ln_orion_cnt NUMBER;
ln_amount NUMBER;
ln_functional_curr_amount NUMBER;
lc_dr_combination VARCHAR2 (240);
ln_dr_ccid NUMBER;
lc_cr_combination VARCHAR2 (240);
ln_cr_ccid NUMBER;
--Declare the cursor to hold the Records, where travel date=sysdate
CURSOR get_orion_revenue_c IS
SELECT orig_system_ref_line_id
, interface_line_attr1
, invoice_type
, debit_type
, ACCOUNT
, bp_no
, bp_type
, tax_no
, vat_ident_no
, due_date
, booking_ref_no
, booking_ref_ver
, booking_status
, booking_date
, fix_booking_date
, booking_media
, booking_type
, travel_from
, travel_to
, brand_code
, brochure_code
, main_destination
, group_booking_flag
, group_booking_name
, number_pax
, number_pax_excl_inf
, pax_name
, currency_code
, amount
, functional_curr_amount
, cust_trx_type_name
, conversion_type
, conversion_date
, trx_date
, gl_date
, trx_number
, comments
, sales_person
, location_code
, account_class
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, data_source
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, load_sequence
, request_id
, record_status
FROM xxeyar_invoices_int_stage xinv
WHERE NVL (xinv.record_status, 'N') IN ('N', 'E')
AND NVL (xinv.gl_process_flag, 'N') <> 'Y'
AND load_sequence = (SELECT MAX (a.load_sequence)
FROM xxeyar_invoices_int_stage a
WHERE a.booking_ref_no = xinv.booking_ref_no
AND ( a.travel_from BETWEEN pp_start_date
AND NVL (pp_end_date, SYSDATE)
OR trx_date BETWEEN pp_start_date
AND NVL (pp_end_date, SYSDATE) )
AND NVL (a.record_status, 'N') IN ('N', 'E')
AND NVL (a.gl_process_flag, 'N') <> 'Y')
AND ( travel_from BETWEEN pp_start_date AND NVL (pp_end_date, SYSDATE)
OR trx_date BETWEEN pp_start_date AND NVL (pp_end_date, SYSDATE) );
CURSOR get_book_amount_c (
p_booking_ref_no xxeyar_invoices_int_stage.booking_ref_no%TYPE) IS
SELECT SUM (amount) / 100
FROM xxeyar_invoices_int_stage
WHERE booking_ref_no = p_booking_ref_no
AND NVL (gl_process_flag, 'N') <> 'Y'
AND NVL (record_status, 'N') IN ('N', 'E')
AND ( trx_date BETWEEN pp_start_date AND NVL (pp_end_date, SYSDATE)
OR travel_from BETWEEN pp_start_date AND NVL (pp_end_date, SYSDATE) );
-- Get SOB Deatails
CURSOR get_sob_info_c IS
SELECT hro.business_group_id
, hro.legal_entity_id
, hro.set_of_books_id
, GLB.chart_of_accounts_id
, GLB.currency_code
FROM hr_operating_units hro
, gl_sets_of_books GLB
WHERE hro.set_of_books_id = GLB.set_of_books_id
AND hro.organization_id = gn_org_id;
-- Get Journal Source
CURSOR get_je_source_c IS
SELECT je_source_name
-- INTO lc_je_source_name
FROM gl_je_sources
WHERE LANGUAGE = 'US'
AND user_je_source_name = gc_je_source_name;
-- Get Revenue Category
CURSOR get_je_cat_c IS
SELECT je_category_name
FROM apps.gl_je_categories_tl
WHERE user_je_category_name = gc_je_category_name
AND LANGUAGE = 'US';
-- Get Accounting segment values.
CURSOR get_lookup_value_c (
p_lkp_type fnd_lookup_values.lookup_type%TYPE
, p_lkp_code fnd_lookup_values.lookup_code%TYPE) IS
SELECT description
, tag
FROM fnd_lookup_values
WHERE enabled_flag = 'Y'
AND lookup_type = p_lkp_type
AND lookup_code = p_lkp_code;
CURSOR check_currency_c (
p_currency_code fnd_currencies.currency_code%TYPE) IS
SELECT currency_code
FROM fnd_currencies
WHERE currency_code = p_currency_code
AND enabled_flag = 'Y';
CURSOR check_period_status (
p_travel_date gl_period_statuses.start_date%TYPE) IS
SELECT 1
FROM gl_period_statuses a
, fnd_application b
--where
WHERE a.application_id = b.application_id
AND a.set_of_books_id = gn_set_of_books_id
AND b.application_short_name = 'SQLGL'
--AND application_id = gn_appl_id
AND p_travel_date BETWEEN a.start_date AND a.end_date
AND a.closing_status = 'C';
CURSOR get_gl_date_c IS
SELECT start_date
FROM gl_period_statuses a
, fnd_application b
--where
WHERE a.application_id = b.application_id
AND a.set_of_books_id = gn_set_of_books_id
AND b.application_short_name = 'SQLGL'
AND a.closing_status = 'O';
CURSOR get_customer_c (
p_loc_code ra_customers.attribute1%TYPE) IS
SELECT customer_name
FROM ra_customers
WHERE status = 'A'
AND attribute1 = p_loc_code;
CURSOR get_conversion_rate_c (
p_date DATE
, p_from_curr gl_daily_rates.from_currency%TYPE
, p_to_curr gl_daily_rates.to_currency%TYPE) IS
SELECT conversion_rate
FROM gl_daily_rates
WHERE conversion_type = 'Corporate'
AND conversion_date = p_date
AND from_currency = p_from_curr
AND to_currency = p_to_curr;
CURSOR get_code_combination_c (
p_segment1 gl_code_combinations.segment1%TYPE
, p_segment2 gl_code_combinations.segment1%TYPE
, p_segment3 gl_code_combinations.segment1%TYPE
, p_segment4 gl_code_combinations.segment1%TYPE
, p_segment5 gl_code_combinations.segment1%TYPE
, p_segment6 gl_code_combinations.segment1%TYPE) IS
SELECT code_combination_id
FROM gl_code_combinations kfv
WHERE kfv.enabled_flag = 'Y'
AND kfv.segment1 = p_segment1
AND kfv.segment2 = p_segment2
AND kfv.segment3 = p_segment3
AND kfv.segment4 = p_segment4
AND kfv.segment5 = p_segment5
AND kfv.segment6 = p_segment6;
ln_period_dummy NUMBER;
ld_gl_date DATE;
lc_functional_currency VARCHAR2 (3);
ln_conversion_rate NUMBER;
lc_location_code VARCHAR2 (3);
lcu_get_lookup_value_rec get_lookup_value_c%ROWTYPE;
BEGIN
gc_code_pointer := 'Initializing the variables';
pp_start_date := TO_DATE (SUBSTR (p_start_date, 1, 10), 'YYYY/MM/DD');
pp_end_date := TO_DATE (SUBSTR (p_end_date, 1, 10), 'YYYY/MM/DD');
retcode := '0';
gc_record_status := 'V';
ln_cnt := 0;
ln_tot_count := 0;
ln_reject_count := 0;
ln_orion_cnt := 0;
gc_code_pointer := 'Get the Business Group ID, Set of Books ID, Leagal entity ID';
gn_business_group_id := NULL;
OPEN get_sob_info_c;
FETCH get_sob_info_c
INTO gn_business_group_id
, gn_legal_entity_id
, gn_set_of_books_id
, gn_coa_id
, lc_functional_currency;
CLOSE get_sob_info_c;
IF gn_business_group_id IS NULL THEN
gc_record_status := 'V';
gc_error_code := 'XXEY_MISSING_SOB_DETAILS';
gc_error_message := 'SOB Details are missing in Oracle';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
END IF;
gc_code_pointer := 'Deriving company segment value';
lc_segment1 := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS', 'COMPANY');
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
lc_segment1 := lcu_get_lookup_value_rec.description;
IF lc_segment1 IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Company segment Value Does not exist in Oracle Lookup XXEY_ORION_GL_REVENUE_ACCOUNTS';
END IF;
gc_code_pointer := 'Deriving debit Cost centre segment value';
lc_segment3_dr := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS', 'COST_CENTRE_DR');
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
lc_segment3_dr := lcu_get_lookup_value_rec.description;
IF lc_segment3_dr IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Credit Cost Centre Value Does not exist in Oracle Lookup XXEY_ORION_GL_REVENUE_ACCOUNTS';
END IF;
gc_code_pointer := 'Deriving debit account segment value';
lc_segment4_dr := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS', 'DR_ACCOUNT');
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
lc_segment4_dr := lcu_get_lookup_value_rec.description;
IF lc_segment4_dr IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Dr Account Value for Does not exist in Oracle Lookup XXEY_ORION_GL_REVENUE_ACCOUNTS';
END IF;
gc_code_pointer := 'Deriving Cost object segment value';
lc_segment5 := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS', 'COST_OBJECT');
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
lc_segment5 := lcu_get_lookup_value_rec.description;
IF lc_segment5 IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Cr Account Value for Does not exist in Oracle Lookup XXEY_ORION_GL_REVENUE_ACCOUNTS';
END IF;
gc_code_pointer := 'Deriving Aircraft segment value';
lc_segment6 := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS', 'AIRCRAFT_TYPE');
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
lc_segment6 := lcu_get_lookup_value_rec.description;
IF lc_segment6 IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Air Craft Value Does not exist in Oracle Lookup XXEY_ORION_GL_REVENUE_ACCOUNTS';
END IF;
gc_code_pointer := 'Deriving Journal source name';
lc_je_source_name := NULL;
OPEN get_je_source_c;
FETCH get_je_source_c
INTO lc_je_source_name;
CLOSE get_je_source_c;
IF lc_je_source_name IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message := 'Journal source name '
|| gc_je_source_name
|| ' is not defined in Oracle';
END IF;
gc_code_pointer := 'Deriving Journal source name';
lc_je_cat_name := NULL;
OPEN get_je_cat_c;
FETCH get_je_cat_c
INTO lc_je_cat_name;
CLOSE get_je_cat_c;
IF lc_je_cat_name IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message := 'Journal catogory name '
|| gc_je_category_name
|| 'is not defined in Oracle';
END IF;
IF gc_record_status <> 'E' THEN
<<validate_l>>
FOR lcu_get_orion_revenue_rec IN get_orion_revenue_c
LOOP
BEGIN
gc_record_status := 'V';
gc_error_code := NULL;
gc_error_message := NULL;
gc_code_pointer := 'Deriving location code';
lc_location_code := NULL;
IF lcu_get_orion_revenue_rec.brochure_code IN ('SHOPMER', 'UAEMER') THEN
gc_record_status := 'S';
ELSE
IF lcu_get_orion_revenue_rec.location_code IS NULL
OR lcu_get_orion_revenue_rec.location_code = '931' THEN
lc_location_code := '925';
ELSE
lc_location_code := lcu_get_orion_revenue_rec.location_code;
END IF;
gc_code_pointer := 'Deriving credit Cost centre segment value';
lc_segment3_cr := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_GL_ORION_DEPT_COST_CENTRE'
, NVL (lcu_get_orion_revenue_rec.main_destination, 'XINS') );
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
IF lcu_get_lookup_value_rec.description IS NULL THEN
OPEN get_lookup_value_c ('XXEY_GL_ORION_DEPT_COST_CENTRE', lc_location_code);
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
END IF;
lc_segment3_cr := lcu_get_lookup_value_rec.description;
IF lc_segment3_cr IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Credit Cost Centre Value Does not exist in Oracle Lookup XXEY_GL_ORION_DEPT_COST_CENTRE for location '
|| lc_location_code;
END IF;
gc_code_pointer := 'Deriving Credit account segment value';
lc_segment4_cr := NULL;
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS', 'CR_ACCOUNT');
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
lc_segment4_cr := lcu_get_lookup_value_rec.description;
gc_code_pointer := 'Deriving brochure_code Credit account segment value';
lcu_get_lookup_value_rec := NULL;
OPEN get_lookup_value_c ('XXEY_ORION_GL_REVENUE_ACCOUNTS'
, lcu_get_orion_revenue_rec.brochure_code);
FETCH get_lookup_value_c
INTO lcu_get_lookup_value_rec;
CLOSE get_lookup_value_c;
IF lcu_get_lookup_value_rec.description IS NOT NULL THEN
lc_segment4_cr := lcu_get_lookup_value_rec.description;
END IF;
IF lc_segment4_cr IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Cr Account Value for Does not exist in Oracle Lookup XXEY_GL_ORION_DEPT_COST_CENTRE';
END IF;
gc_code_pointer := 'Deriving currency code';
lc_currency_code := NULL;
OPEN check_currency_c (lcu_get_orion_revenue_rec.currency_code);
FETCH check_currency_c
INTO lc_currency_code;
CLOSE check_currency_c;
IF lc_currency_code IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message := 'Currency code '
|| lcu_get_orion_revenue_rec.currency_code
|| 'either disabled or does not exist in Oracle';
END IF;
gc_code_pointer := 'Check the GL Date';
ln_period_dummy := NULL;
ld_gl_date := lcu_get_orion_revenue_rec.travel_from;
OPEN check_period_status (lcu_get_orion_revenue_rec.travel_from);
FETCH check_period_status
INTO ln_period_dummy;
CLOSE check_period_status;
fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gn_appl_id);
IF ln_period_dummy IS NOT NULL THEN
gc_code_pointer := 'Derive the GL Date';
ld_gl_date := NULL;
-- fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gc_code_pointer);
OPEN get_gl_date_c;
FETCH get_gl_date_c
INTO ld_gl_date;
CLOSE get_gl_date_c;
END IF;
/* gc_code_pointer := 'Derive customer name';
lc_customer := NULL;
OPEN get_customer_c (lc_location_code);
FETCH get_customer_c
INTO lc_customer;
CLOSE get_customer_c;
IF lc_customer IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message := 'Customer is not defined in Oracle for the loaction code '
|| lc_location_code;
END IF; */
gc_code_pointer := 'Validating Credit Code combination';
ln_cr_ccid := NULL;
lc_cr_combination := lc_segment1
|| '.'
|| lc_location_code
|| '.'
|| lc_segment3_cr
|| '.'
|| lc_segment4_cr
|| '.'
|| lc_segment5
|| '.'
|| lc_segment6;
OPEN get_code_combination_c (lc_segment1
, lc_location_code
, lc_segment3_cr
, lc_segment4_cr
, lc_segment5
, lc_segment6);
FETCH get_code_combination_c
INTO ln_cr_ccid;
CLOSE get_code_combination_c;
IF ln_cr_ccid IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message := 'Code combination '
|| lc_cr_combination
|| ' does not exist in Oracle';
END IF;
gc_code_pointer := 'Validating Debit Code combination';
ln_dr_ccid := NULL;
lc_dr_combination := lc_segment1
|| '.'
|| lc_location_code
|| '.'
|| lc_segment3_dr
|| '.'
|| lc_segment4_dr
|| '.'
|| lc_segment5
|| '.'
|| lc_segment6;
OPEN get_code_combination_c (lc_segment1
, lc_location_code
, lc_segment3_dr
, lc_segment4_dr
, lc_segment5
, lc_segment6);
FETCH get_code_combination_c
INTO ln_dr_ccid;
CLOSE get_code_combination_c;
IF ln_dr_ccid IS NULL THEN
gc_record_status := 'E';
--lc_err_flag := 'E';
gc_error_code := 'XXEY_ORION_ACODE_VAL_ERROR';
gc_error_message := 'Code combination '
|| lc_dr_combination
|| ' does not exsist in Oracle';
END IF;
gc_code_pointer := 'Derive journal line description';
lc_reference10 := NULL;
lc_reference10 := TO_NUMBER (lcu_get_orion_revenue_rec.booking_ref_no)
|| '-'
|| lc_customer
|| '-'
|| lcu_get_orion_revenue_rec.number_pax
|| '-'
|| 'pax'
|| lcu_get_orion_revenue_rec.pax_name
|| '-'
|| lcu_get_orion_revenue_rec.main_destination
|| '-'
|| lcu_get_orion_revenue_rec.travel_from;
gc_code_pointer := 'Derive the actual amount';
OPEN get_book_amount_c (lcu_get_orion_revenue_rec.booking_ref_no);
FETCH get_book_amount_c
INTO ln_amount;
CLOSE get_book_amount_c;
-- := lcu_get_orion_revenue_rec.amount / 100;
IF ln_amount = 0 THEN
gc_record_status := 'X';
--gc_error_code := 'XXEY_ORION_X';
gc_error_message :=
'This pakage has been excluded since the amount becomes zero';
ELSIF ln_amount IS NULL THEN
gc_record_status := 'E';
--gc_error_code := 'XXEY_ORION_X';
gc_error_message := 'Pakage amount is null please check the records';
END IF;
gc_code_pointer := 'Derive conversion rate';
ln_conversion_rate := NULL;
IF lc_currency_code <> lc_functional_currency THEN
OPEN get_conversion_rate_c (lcu_get_orion_revenue_rec.travel_from
, lc_currency_code
, lc_functional_currency);
FETCH get_conversion_rate_c
INTO ln_conversion_rate;
CLOSE get_conversion_rate_c;
ELSE
ln_conversion_rate := 1;
END IF;
IF ln_conversion_rate IS NULL THEN
gc_record_status := 'E';
gc_error_code := 'XXEY_ORION_GLREV_VALIDATION_ERROR';
gc_error_message :=
'Conversion rate is not defined in Oracle for the given currency '
|| lc_currency_code;
END IF;
gc_code_pointer := 'Derive functional currency amount';
ln_functional_curr_amount := ln_amount * ln_conversion_rate;
gc_code_pointer := 'Check for already processed journals';
ln_header_id := NULL;
OPEN get_processd_rev_jurnl_c (lc_je_source_name
, lc_je_cat_name
, ln_dr_ccid
, lcu_get_orion_revenue_rec.gl_date
, lcu_get_orion_revenue_rec.bp_type
, lcu_get_orion_revenue_rec.orig_system_ref_line_id
, lcu_get_orion_revenue_rec.data_source
, lcu_get_orion_revenue_rec.interface_line_attr1);
FETCH get_processd_rev_jurnl_c
INTO ln_header_id
, ln_line_num;
CLOSE get_processd_rev_jurnl_c;
IF ln_header_id IS NOT NULL THEN
gc_record_status := 'A';
gc_error_code := 'XXEY_ORION_GLREV_AlREADY_PROCESSED';
gc_error_message := 'Already The Revenue has been Transferred to GL';
END IF;
END IF;
gc_code_pointer := 'Update the staging table with derived results';
UPDATE xxeyar_invoices_int_stage
SET actual_amount = ln_amount
, actual_loc_code = lc_location_code
, conversion_rate = ln_conversion_rate
, conversion_type = 'Corporate'
, functional_currency = lc_functional_currency
, functional_curr_amount = ln_functional_curr_amount
, segment1 = lc_segment1
--, segment2 = lc_segment2
, segment3 = lc_segment3_cr
, attribute1 = lc_segment3_dr
, segment4 = lc_segment4_cr
, attribute2 = lc_segment4_dr
, segment5 = lc_segment5
, segment6 = lc_segment6
, gl_date = ld_gl_date
, attribute3 = lc_je_source_name
, attribute4 = lc_je_cat_name
, attribute5 = lc_dr_combination
, attribute6 = ln_dr_ccid
, attribute7 = lc_cr_combination
, attribute8 = ln_cr_ccid
, attribute10 = lc_reference10
, last_updated_by = gn_user_id
, last_updated_date = SYSDATE
, request_id = gn_request_id
, record_status = gc_record_status
, ERROR_CODE = gc_error_code
, error_message = gc_error_message
WHERE load_sequence = lcu_get_orion_revenue_rec.load_sequence
AND booking_ref_no = lcu_get_orion_revenue_rec.booking_ref_no;
gc_code_pointer := 'Collective Update on the staging table with error details';
UPDATE xxeyar_invoices_int_stage
SET last_updated_by = gn_user_id
, last_updated_date = SYSDATE
, request_id = gn_request_id
, record_status = 'S'
, ERROR_CODE = gc_error_code
, error_message = gc_error_message
WHERE ( travel_from BETWEEN pp_start_date AND NVL (pp_end_date, SYSDATE)
OR trx_date BETWEEN pp_start_date AND NVL (pp_end_date, SYSDATE) )
AND NVL (record_status, 'N') IN ('N', 'E')
AND booking_ref_no = lcu_get_orion_revenue_rec.booking_ref_no
AND load_sequence <> lcu_get_orion_revenue_rec.load_sequence;
EXCEPTION
WHEN OTHERS THEN
retcode := 1;
fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gc_code_pointer);
fnd_file.put_line (fnd_file.LOG, 'Error :' || SQLCODE || SQLERRM);
END;
END LOOP validate_l;
END IF;
gc_code_pointer := 'Collective Update on the staging table with error details';
UPDATE xxeyar_invoices_int_stage
SET last_updated_by = gn_user_id
, last_updated_date = SYSDATE
, request_id = gn_request_id
, record_status = 'E'
, ERROR_CODE = gc_error_code
, error_message = gc_error_message
WHERE ( travel_from BETWEEN pp_start_date AND pp_end_date
OR trx_date BETWEEN pp_start_date AND pp_end_date)
AND record_status = 'N';
COMMIT;
gc_code_pointer := 'Get the last run validation statistics';
OPEN get_stats_c;
FETCH get_stats_c
INTO ln_p_count
, ln_e_count
, ln_v_count
, ln_a_count
, ln_s_count;
CLOSE get_stats_c;
gc_stat_msg := CHR (10)
|| 'EY Orion to GL Revenue Transfer Validation Program'
|| CHR (10)
|| CHR (10)
|| 'Current Run Statistics on the Staging table XXEYAR_INVOICES_INT_STAGE'
|| CHR (10)
|| CHR (10)
|| RPAD ('Total Number Of Records Already Processed', 60)
|| ': '
|| TO_CHAR (ln_a_count)
|| CHR (10)
|| CHR (10)
|| RPAD ('Total Number Of Records Processed', 60)
|| ': '
|| TO_CHAR (ln_e_count + ln_v_count)
|| CHR (10)
|| CHR (10)
|| RPAD ('
Number Of Records Failed', 60)
|| ': '
|| ln_e_count
|| CHR (10)
|| RPAD ('
Number Of Records Sucessfully Validated', 60)
|| ': '
|| ln_v_count
|| CHR (10)
|| RPAD ('
Number Of Records Sucessfully Suspended', 60)
|| ': '
|| ln_s_count;
fnd_file.put_line (fnd_file.output, gc_stat_msg);
IF ln_e_count > 0 THEN
retcode := 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gc_code_pointer);
errbuf := SQLCODE || SQLERRM;
END validate_rev_transfer;
PROCEDURE upload_rev_transfer (
errbuf OUT VARCHAR2
, retcode OUT NUMBER) IS
CURSOR get_valid_gl_rev_c (
p_travel_from xxeyar_invoices_int_stage.travel_from%TYPE) IS
SELECT orig_system_ref_line_id
, interface_line_attr1
, invoice_type
, debit_type
, ACCOUNT
, bp_no
, bp_type
, tax_no
, vat_ident_no
, due_date
, booking_ref_no
, booking_ref_ver
, booking_status
, booking_date
, fix_booking_date
, booking_media
, booking_type
, travel_from
, travel_to
, brand_code
, brochure_code
, main_destination
, group_booking_flag
, group_booking_name
, number_pax
, number_pax_excl_inf
, pax_name
, currency_code
, amount
, actual_amount
, functional_currency
, functional_curr_amount
, cust_trx_type_name
, orig_system_bill_ref
, conversion_type
, conversion_date
, conversion_rate
, trx_date
, gl_date
, trx_number
, comments
, sales_person
, location_code
, actual_loc_code
, account_class
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, data_source
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, load_sequence
FROM xxeyar_invoices_int_stage
WHERE travel_from = p_travel_from
AND record_status = 'V';
CURSOR get_rev_run_date_c IS
SELECT DISTINCT travel_from
, attribute3 je_source_name
FROM xxeyar_invoices_int_stage
WHERE TRUNC (travel_from) <= TRUNC (SYSDATE)
AND record_status = 'V';
-- Cursor to get the running requests
CURSOR get_rung_reqs_c (
p_conc_name fnd_concurrent_programs.concurrent_program_name%TYPE
, p_request_id fnd_concurrent_requests.request_id%TYPE) IS
SELECT cnqr.request_id
FROM fnd_concurrent_requests cnqr
, fnd_concurrent_programs cnp
WHERE cnqr.concurrent_program_id = cnp.concurrent_program_id
AND cnp.concurrent_program_name = p_conc_name
AND cnqr.phase_code <> 'C'
AND cnqr.request_id = p_request_id;
CURSOR get_interfaced_rev_c IS
SELECT a.attribute3 SOURCE
, a.attribute4 cat
, a.orig_system_ref_line_id
, a.interface_line_attr1
, a.actual_amount
, a.attribute10
, a.attribute8 ccid
, a.travel_from
, a.load_sequence
, a.bp_type
, a.data_source
, a.gl_date
FROM xxeyar_invoices_int_stage a
WHERE record_status = 'I'
AND request_id = gn_request_id;
CURSOR check_interfaced_rev_jurnl_c (
p_interface_line_attr1 gl_interface.reference4%TYPE
, p_orig_system_ref_line_id gl_je_lines.attribute3%TYPE
, p_reference10 gl_je_lines.reference_10%TYPE
, p_jl_date gl_je_lines.effective_date%TYPE
, p_ccid gl_interface.set_of_books_id%TYPE) IS
SELECT intr.status
, intr.je_header_id
, intr.je_line_num
FROM gl_interface intr
WHERE intr.code_combination_id = p_ccid
AND intr.set_of_books_id = gn_set_of_books_id
AND intr.reference4 = p_interface_line_attr1
AND intr.accounting_date = p_jl_date
AND intr.user_je_source_name = gc_je_source_name
AND intr.user_je_category_name = gc_je_category_name
AND intr.attribute3 = p_orig_system_ref_line_id;
ln_group_id NUMBER;
ln_interface_run_id NUMBER;
ln_request_id NUMBER;
ln_cnt NUMBER := 0;
lb_flag BOOLEAN;
lc_phase VARCHAR2 (30);
lc_dev_phase VARCHAR2 (30);
lc_dev_status VARCHAR2 (30);
lc_message VARCHAR2 (240);
gv_reference1 VARCHAR2 (30) := 'Holiday Rev Transfer' || SYSDATE;
gv_batch_desc VARCHAR2 (30)
:= 'ORION' || '' || TO_CHAR (SYSDATE, 'RRRRMMDD') || ''
|| TO_CHAR (SYSDATE, 'HHMISS');
ln_rec_count BINARY_INTEGER := 1;
orglr_upld_request_tbl conc_requests_tabtype;
ln_re_count NUMBER;
lc_status gl_interface.status%TYPE;
ln_c_id NUMBER;
ln_c_amount NUMBER;
ln_cf_amount NUMBER;
ln_cr_seg3 VARCHAR2 (100);
ln_cr_seg4 VARCHAR2 (100);
ln_dr_seg4 VARCHAR2 (100);
ln_dr_seg3 VARCHAR2 (100);
lc_gl_process_flag VARCHAR2 (1);
BEGIN
retcode := 0;
gc_code_pointer := 'Clearing interface tables';
orglr_upld_request_tbl.DELETE;
DELETE gl_interface
WHERE set_of_books_id = gn_set_of_books_id
AND user_je_source_name = gc_je_source_name
AND user_je_category_name = gc_je_category_name
AND status <> 'P';
<<upload_grp_l>>
FOR lcu_get_rev_run_date_rec IN get_rev_run_date_c
LOOP
BEGIN
SAVEPOINT xxey_orgl_rev_trs;
gc_code_pointer := 'Get the group id for this run';
SELECT gl_interface_control_s.NEXTVAL
INTO ln_group_id
FROM DUAL;
gc_code_pointer := 'Get the interface run id';
SELECT gl_journal_import_s.NEXTVAL
INTO ln_interface_run_id
FROM DUAL;
gc_code_pointer := 'Populate interface control table';
INSERT INTO gl_interface_control
(je_source_name
, status
, interface_run_id
, GROUP_ID
, set_of_books_id)
VALUES (lcu_get_rev_run_date_rec.je_source_name
, 'S'
, ln_interface_run_id
, ln_group_id
, gn_set_of_books_id);
gc_code_pointer := 'Populate journal interface table';
<<upload_l>>
FOR lcu_get_valid_gl_rev_rec IN get_valid_gl_rev_c (lcu_get_rev_run_date_rec.travel_from)
LOOP
BEGIN
IF lcu_get_valid_gl_rev_rec.actual_amount < 0 THEN
ln_c_amount := ABS (lcu_get_valid_gl_rev_rec.actual_amount);
ln_cf_amount := ABS (lcu_get_valid_gl_rev_rec.functional_curr_amount);
ln_cr_seg3 := lcu_get_valid_gl_rev_rec.attribute1;
ln_cr_seg4 := lcu_get_valid_gl_rev_rec.attribute2;
ln_dr_seg4 := lcu_get_valid_gl_rev_rec.segment4;
ln_dr_seg3 := lcu_get_valid_gl_rev_rec.segment3;
ELSE
ln_c_amount := lcu_get_valid_gl_rev_rec.actual_amount;
ln_cf_amount := lcu_get_valid_gl_rev_rec.functional_curr_amount;
ln_cr_seg3 := lcu_get_valid_gl_rev_rec.segment3;
ln_cr_seg4 := lcu_get_valid_gl_rev_rec.segment4;
ln_dr_seg4 := lcu_get_valid_gl_rev_rec.attribute2;
ln_dr_seg3 := lcu_get_valid_gl_rev_rec.attribute1;
END IF;
gc_code_pointer := 'Populate interface table with debit line';
INSERT INTO gl_interface
(status
, set_of_books_id
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, user_je_category_name
, user_je_source_name
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, entered_dr
, accounted_dr
, entered_cr
, accounted_cr
, reference1
, reference2
, reference4
, reference10
, CONTEXT
, attribute3
, GROUP_ID
, reference21
, reference22
, reference23
, reference24)
VALUES ('NEW'
, gn_set_of_books_id
, lcu_get_valid_gl_rev_rec.gl_date
, lcu_get_valid_gl_rev_rec.currency_code
, SYSDATE
, gn_user_id
, 'A'
, gc_je_category_name
, gc_je_source_name
, lcu_get_valid_gl_rev_rec.segment1
, lcu_get_valid_gl_rev_rec.actual_loc_code
, ln_dr_seg3
, ln_dr_seg4
, lcu_get_valid_gl_rev_rec.segment5
, lcu_get_valid_gl_rev_rec.segment6
, ln_c_amount
, ln_cf_amount
, NULL
, NULL
, gv_reference1
, gv_batch_desc
, 'ORION'
, lcu_get_valid_gl_rev_rec.attribute10
, null--'ORION'
, null--lcu_get_valid_gl_rev_rec.booking_ref_no
, ln_group_id
, lcu_get_valid_gl_rev_rec.bp_type
, lcu_get_valid_gl_rev_rec.orig_system_ref_line_id
, lcu_get_valid_gl_rev_rec.data_source
, lcu_get_valid_gl_rev_rec.interface_line_attr1);
gc_code_pointer := 'Populate interface table with credit line';
INSERT INTO gl_interface
(status
, set_of_books_id
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, user_je_category_name
, user_je_source_name
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, entered_dr
, accounted_dr
, entered_cr
, accounted_cr
, reference1
, reference2
, reference4
, reference10
, CONTEXT
, attribute3
, GROUP_ID
, reference21
, reference22
, reference23
, reference24)
VALUES ('NEW'
, gn_set_of_books_id
, lcu_get_valid_gl_rev_rec.gl_date
, lcu_get_valid_gl_rev_rec.currency_code
, SYSDATE
, gn_user_id
, 'A'
, gc_je_category_name
, gc_je_source_name
, lcu_get_valid_gl_rev_rec.segment1
, lcu_get_valid_gl_rev_rec.actual_loc_code
, ln_cr_seg3
, ln_cr_seg4
, lcu_get_valid_gl_rev_rec.segment5
, lcu_get_valid_gl_rev_rec.segment6
, NULL
, NULL
, ln_c_amount
, ln_cf_amount
, gv_reference1
, gv_batch_desc
, 'ORION'
, lcu_get_valid_gl_rev_rec.attribute10
, null--'ORION'
, null--lcu_get_valid_gl_rev_rec.booking_ref_no
, ln_group_id
, lcu_get_valid_gl_rev_rec.bp_type
, lcu_get_valid_gl_rev_rec.orig_system_ref_line_id
, lcu_get_valid_gl_rev_rec.data_source
, lcu_get_valid_gl_rev_rec.interface_line_attr1);
ln_cnt := ln_cnt + 1;
UPDATE xxeyar_invoices_int_stage
SET record_status = 'I'
, last_updated_by = gn_user_id
, last_updated_date = SYSDATE
, request_id = gn_request_id
, ERROR_CODE = gc_error_code
, error_message = gc_error_message
WHERE load_sequence = lcu_get_valid_gl_rev_rec.load_sequence;
EXCEPTION
WHEN OTHERS THEN
gc_error_message := 'Failed to Insert Records into GL Interface '
|| TO_CHAR (SQLCODE)
|| '-'
|| SQLERRM;
fnd_file.put_line (fnd_file.LOG, gc_error_message);
retcode := 1;
UPDATE xxeyar_invoices_int_stage
SET record_status = 'E'
, last_updated_by = gn_user_id
, last_updated_date = SYSDATE
, request_id = gn_request_id
, ERROR_CODE = gc_error_code
, error_message = gc_error_message
WHERE load_sequence = lcu_get_valid_gl_rev_rec.load_sequence;
END;
END LOOP upload_l;
COMMIT;
gc_code_pointer := 'Submit Journal Import';
IF ln_cnt > 0 THEN
ln_request_id := fnd_request.submit_request ('SQLGL'
, 'GLLEZL'
, ''
, ''
, FALSE
, ln_interface_run_id
, gn_set_of_books_id
, 'N'
, ''
, ''
, 'N'
, 'W'
, CHR (0)
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, '');
COMMIT;
gc_code_pointer := 'Checking the Submit Journal Import';
IF ln_request_id <> 0 THEN
fnd_file.put_line (fnd_file.LOG, 'Submit Journal Import Program is Successfull');
orglr_upld_request_tbl (ln_rec_count) := ln_request_id;
ln_re_count := ln_re_count + 1;
ELSE
retcode := 1;
fnd_file.put_line (fnd_file.LOG
, 'Error While Submitting Standard Journal Import Program.');
ROLLBACK TO xxey_orgl_rev_trs;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
gc_error_message := 'Failed to Insert Records into GL Interface '
|| TO_CHAR (SQLCODE)
|| '-'
|| SQLERRM;
fnd_file.put_line (fnd_file.LOG, gc_error_message);
END;
END LOOP upload_grp_l;
gc_code_pointer := 'Waiting for the journal import programs to complete';
--ln_request_id := -1;
<<wait_loop>>
WHILE ln_request_id <> 0
LOOP
gc_code_pointer := 'Getting the running requests';
ln_request_id := NULL;
<<run_reqs_loop>>
FOR ln_recont IN 1 .. orglr_upld_request_tbl.COUNT
LOOP
OPEN get_rung_reqs_c ('GLLEZL', orglr_upld_request_tbl (ln_recont) );
FETCH get_rung_reqs_c
INTO ln_request_id;
CLOSE get_rung_reqs_c;
END LOOP run_reqs_loop;
gc_code_pointer := 'Checking the running request id';
IF ln_request_id IS NULL THEN
ln_request_id := 0;
END IF;
END LOOP wait_loop;
<<int_check_loop>>
FOR lcu_get_interfaced_rev_rec IN get_interfaced_rev_c
LOOP
gc_record_status := 'P';
gc_error_code := NULL;
gc_error_message := NULL;
gc_code_pointer := 'Checking for the created journals';
ln_header_id := NULL;
lc_gl_process_flag := 'Y';
OPEN get_processd_rev_jurnl_c (lcu_get_interfaced_rev_rec.SOURCE
, lcu_get_interfaced_rev_rec.cat
, lcu_get_interfaced_rev_rec.ccid
, lcu_get_interfaced_rev_rec.travel_from
, lcu_get_interfaced_rev_rec.bp_type
, lcu_get_interfaced_rev_rec.orig_system_ref_line_id
, lcu_get_interfaced_rev_rec.data_source
, lcu_get_interfaced_rev_rec.interface_line_attr1);
FETCH get_processd_rev_jurnl_c
INTO ln_header_id
, ln_line_num;
CLOSE get_processd_rev_jurnl_c;
IF ln_header_id IS NULL THEN
gc_code_pointer := 'Checking for the interfaced journals';
lc_status := NULL;
OPEN check_interfaced_rev_jurnl_c (lcu_get_interfaced_rev_rec.interface_line_attr1
, lcu_get_interfaced_rev_rec.orig_system_ref_line_id
, lcu_get_interfaced_rev_rec.attribute10
, lcu_get_interfaced_rev_rec.gl_date
, lcu_get_interfaced_rev_rec.ccid);
FETCH check_interfaced_rev_jurnl_c
INTO lc_status
, ln_header_id
, ln_line_num;
CLOSE check_interfaced_rev_jurnl_c;
IF lc_status IS NULL
OR lc_status <> 'P' THEN
gc_record_status := 'E';
lc_gl_process_flag := NULL;
gc_error_code := 'XXEY_JOURNAL_CREATION_ERROR';
gc_error_message := lc_status;
ln_header_id := NULL;
ln_line_num := NULL;
END IF;
END IF;
gc_code_pointer := 'Updating staging table with the results';
UPDATE xxeyar_invoices_int_stage
SET record_status = gc_record_status
, gl_process_flag = lc_gl_process_flag
, attribute14 = ln_header_id
, attribute15 = ln_line_num
, last_updated_by = gn_user_id
, last_updated_date = SYSDATE
, request_id = gn_request_id
, ERROR_CODE = gc_error_code
, error_message = gc_error_message
WHERE load_sequence = lcu_get_interfaced_rev_rec.load_sequence;
END LOOP int_check_loop;
gc_code_pointer := 'Get the last run upload statistics';
OPEN get_stats_c;
FETCH get_stats_c
INTO ln_p_count
, ln_e_count
, ln_v_count
, ln_a_count
, ln_s_count;
CLOSE get_stats_c;
gc_stat_msg := CHR (10)
|| 'EY Orion to GL Revenue Transfer Upload Program'
|| CHR (10)
|| CHR (10)
|| 'Current Run Statistics on the Staging table XXEYAR_INVOICES_INT_STAGE'
|| CHR (10)
|| CHR (10)
|| RPAD ('Total Number Of Records Interfaced', 60)
|| ': '
|| TO_CHAR (ln_cnt)
|| CHR (10)
|| CHR (10)
|| RPAD ('Total Number Of Records Processed', 60)
|| ': '
|| TO_CHAR (ln_e_count + ln_p_count)
|| CHR (10)
|| RPAD ('
Number Of Records Failed', 60)
|| ': '
|| ln_e_count
|| CHR (10)
|| RPAD ('
Number Of Records Sucessfully Processed', 60)
|| ': '
|| ln_p_count;
fnd_file.put_line (fnd_file.output, gc_stat_msg);
IF ln_e_count > 0 THEN
retcode := 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gc_code_pointer);
errbuf := SQLCODE || SQLERRM;
END upload_rev_transfer;
PROCEDURE create_gl_codes (
x_errmsg OUT VARCHAR2
, x_errcode OUT VARCHAR2) IS
-- Cursor to get the gl code errors
CURSOR get_recds_gl_code_c IS
SELECT DISTINCT segment1
, actual_loc_code segment2
, segment3
, segment4
, segment5
, segment6
FROM xxeyar_invoices_int_stage
WHERE record_status = 'E'
AND ERROR_CODE = 'XXEY_ORION_ACODE_VAL_ERROR';
-- Cursor to check the segment values
CURSOR chk_seg_value_c (
p_flex_name fnd_flex_value_sets.flex_value_set_name%TYPE
, p_flex_value fnd_flex_values.flex_value%TYPE) IS
SELECT 1
FROM fnd_flex_value_sets vs
, fnd_flex_values vv
WHERE vs.flex_value_set_id = vv.flex_value_set_id
AND vv.enabled_flag = 'Y'
AND NVL (vv.start_date_active, SYSDATE) <= SYSDATE
AND NVL (vv.end_date_active, SYSDATE) >= SYSDATE
AND vv.flex_value = p_flex_value
AND vs.flex_value_set_name = p_flex_name;
CURSOR get_ccaid_c IS
SELECT chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
CURSOR get_ccid_c (
p_cat_segments gl_code_combinations_kfv.concatenated_segments%TYPE) IS
SELECT code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = p_cat_segments;
ln_dummy NUMBER;
ln_coa_id NUMBER;
l_keyval_status BOOLEAN;
lc_cat_segments VARCHAR2 (2000);
ln_ccid NUMBER;
ln_s_count NUMBER := 0;
ln_e_count NUMBER := 0;
BEGIN
x_errcode := '0';
gc_code_pointer := 'Get all the distribution lines for gl code combination creations';
<<gl_code_l>>
FOR lcu_get_recds_gl_code_rec IN get_recds_gl_code_c
LOOP
BEGIN
gc_record_status := 'V';
ln_ccid := NULL;
lc_cat_segments := TRIM (lcu_get_recds_gl_code_rec.segment1)
|| '.'
|| TRIM (lcu_get_recds_gl_code_rec.segment2)
|| '.'
|| TRIM (lcu_get_recds_gl_code_rec.segment3)
|| '.'
|| TRIM (lcu_get_recds_gl_code_rec.segment4)
|| '.'
|| TRIM (lcu_get_recds_gl_code_rec.segment5)
|| '.'
|| TRIM (lcu_get_recds_gl_code_rec.segment6);
gc_code_pointer := 'Check the company segment values';
ln_dummy := NULL;
gc_error_message := NULL;
OPEN chk_seg_value_c ('EY_COMPANY', lcu_get_recds_gl_code_rec.segment1);
FETCH chk_seg_value_c
INTO ln_dummy;
CLOSE chk_seg_value_c;
IF ln_dummy IS NULL THEN
gc_record_status := 'E';
gc_error_message := lcu_get_recds_gl_code_rec.segment1
|| ' is invalid value for the segment1 EY_COMPANY';
END IF;
gc_code_pointer := 'Check the Location segment values';
ln_dummy := NULL;
OPEN chk_seg_value_c ('EY_LOCATION', lcu_get_recds_gl_code_rec.segment2);
FETCH chk_seg_value_c
INTO ln_dummy;
CLOSE chk_seg_value_c;
IF ln_dummy IS NULL THEN
gc_record_status := 'E';
gc_error_message := lcu_get_recds_gl_code_rec.segment2
|| ' is invalid value for the segment2 EY_LOCATION';
END IF;
gc_code_pointer := 'Check the Cost Centre segment values';
ln_dummy := NULL;
OPEN chk_seg_value_c ('EY_COST_CENTRE', lcu_get_recds_gl_code_rec.segment3);
FETCH chk_seg_value_c
INTO ln_dummy;
CLOSE chk_seg_value_c;
IF ln_dummy IS NULL THEN
gc_record_status := 'E';
gc_error_message := lcu_get_recds_gl_code_rec.segment3
|| ' is invalid value for the segment3 EY_COST_CENTRE';
END IF;
gc_code_pointer := 'Check the Account segment values';
ln_dummy := NULL;
OPEN chk_seg_value_c ('EY_ACCOUNT', lcu_get_recds_gl_code_rec.segment4);
FETCH chk_seg_value_c
INTO ln_dummy;
CLOSE chk_seg_value_c;
IF ln_dummy IS NULL THEN
gc_record_status := 'E';
gc_error_message := lcu_get_recds_gl_code_rec.segment4
|| ' is invalid value for the segment4 EY_ACCOUNT';
END IF;
gc_code_pointer := 'Check the Cost Object segment values';
ln_dummy := NULL;
OPEN chk_seg_value_c ('EY_COST_OBJECT', lcu_get_recds_gl_code_rec.segment5);
FETCH chk_seg_value_c
INTO ln_dummy;
CLOSE chk_seg_value_c;
IF ln_dummy IS NULL THEN
gc_record_status := 'E';
gc_error_message := lcu_get_recds_gl_code_rec.segment5
|| ' is invalid value for the segment5 EY_COST_OBJECT';
END IF;
gc_code_pointer := 'Check the Aircraft Code segment values';
ln_dummy := NULL;
OPEN chk_seg_value_c ('EY_AIRCRAFT_CODE', lcu_get_recds_gl_code_rec.segment6);
FETCH chk_seg_value_c
INTO ln_dummy;
CLOSE chk_seg_value_c;
IF ln_dummy IS NULL THEN
gc_record_status := 'E';
gc_error_message := lcu_get_recds_gl_code_rec.segment6
|| ' is invalid value for the segment6 EY_AIRCRAFT_CODE';
END IF;
gc_code_pointer := 'Getting Chart of Accounts id';
OPEN get_ccaid_c;
FETCH get_ccaid_c
INTO ln_coa_id;
CLOSE get_ccaid_c;
IF gc_record_status = 'V' THEN
gc_code_pointer := 'Creating code combinations';
-- create will only work if dynamic inserts on and cross validation rules not broken
l_keyval_status := fnd_flex_keyval.validate_segs ('CREATE_COMBINATION'
, 'SQLGL'
, 'GL#'
, ln_coa_id
, lc_cat_segments
, 'V'
, SYSDATE
, 'ALL'
, NULL
, NULL
, NULL
, NULL
, FALSE
, FALSE
, NULL
, NULL
, NULL);
gc_code_pointer := 'Checking the return status of create code combinations';
IF l_keyval_status THEN
COMMIT;
gc_code_pointer := 'Getting Code Combination id';
OPEN get_ccid_c (lc_cat_segments);
FETCH get_ccid_c
INTO ln_ccid;
CLOSE get_ccid_c;
ELSE
gc_record_status := 'E';
x_errcode := '1';
gc_error_message := fnd_message.get;
END IF;
END IF;
IF gc_record_status = 'V' THEN
ln_s_count := ln_s_count + 1;
gc_error_message := 'Code Combination '
|| lc_cat_segments
|| ' has been created successfully.';
fnd_file.put_line (fnd_file.LOG, gc_error_message);
ELSE
fnd_file.put_line (fnd_file.LOG
, 'Message : Code Combination '
|| lc_cat_segments
|| ' has not been created.');
fnd_file.put_line (fnd_file.LOG, 'Reason : ' || gc_error_message);
ln_e_count := ln_e_count + 1;
END IF;
gc_code_pointer := 'Updating the status with the distribution staging table';
UPDATE xxeyar_invoices_int_stage
SET attribute8 = ln_ccid
, attribute7 = lc_cat_segments
, error_message = gc_error_message
--, attribute12 = 'DSR#GL'
, request_id = gn_request_id
, last_updated_by = gn_user_id
, last_updated_date = SYSDATE
WHERE segment1 = lcu_get_recds_gl_code_rec.segment1
AND segment2 = lcu_get_recds_gl_code_rec.segment2
AND segment3 = lcu_get_recds_gl_code_rec.segment3
AND segment4 = lcu_get_recds_gl_code_rec.segment4
AND segment5 = lcu_get_recds_gl_code_rec.segment5
AND segment6 = lcu_get_recds_gl_code_rec.segment6
AND record_status = 'E';
EXCEPTION
WHEN OTHERS THEN
gc_error_message := SUBSTR (SQLERRM, 1, 2000);
x_errcode := '1';
fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gc_code_pointer);
fnd_file.put_line (fnd_file.LOG, 'Error Messages :' || gc_error_message);
x_errmsg := gc_error_message;
END;
END LOOP gl_code_l;
IF ln_e_count > 0 THEN
fnd_file.put_line
(fnd_file.LOG
, 'Note : If required kindly check CV rule for these combinations and Dynamic Insert.');
x_errcode := '1';
END IF;
fnd_file.put_line (fnd_file.output
, RPAD ('Statistics Of the Current Run', 60)
|| CHR (10)
|| CHR (10)
|| RPAD ('Number of Combinations created Sucessfully', 60)
|| ' - '
|| TO_CHAR (ln_s_count)
|| CHR (10)
|| RPAD ('Number of Combinations failed', 60)
|| ' - '
|| ln_e_count);
EXCEPTION
WHEN OTHERS THEN
gc_error_message := SUBSTR (SQLERRM, 1, 2000);
x_errcode := '2';
fnd_file.put_line (fnd_file.LOG, 'Code Pointer :' || gc_code_pointer);
fnd_file.put_line (fnd_file.LOG, 'Error Messages :' || gc_error_message);
x_errmsg := gc_error_message;
END create_gl_codes;
END xxey_gl_rev_transfer_pk;
/