Review and Submit Accounting Configuration: USHARIP1 LEDGER

Complete Accounting Configuration

Complete Accounting Configuration: Load Charts of Accounts Values

Complete Accounting Configuration: Create Balances Cubes

PUBLISH CHART OF ACCOUNT DIMENSION MEMBERS AND HIERARCHIES TO BALANCES CUBES

--------->> UPDATE CHART OF ACCOUNTS DIMENSION MEMBERS AND HIERARCHIES

_____________________________________________________________________________________

General Ledger Trial Balance Report

------------------------------------------------------------------------------------------------------------------------------------------

>> main() 08-JUL-2019 11:52:39

>> gltini() 08-JUL-2019 11:52:39

<< gltini() 08-JUL-2019 11:52:39

User name = SRIHARI RAO.GURRAM

Session ID = 8D2B0F78777D162DE05384A7480A438E

Request ID = 2042600

Ledger Short Name = 300000031286408

Access Set ID = 300000031370947

Chart of Accounts ID = 99121

Ledger ID = 300000031286408

Target Currency = USD

Period Name = Jun-2020

Balance Type = A

Balancing Segment = 100

>> glulsr() 08-JUL-2019 11:52:39

<< glulsr() 08-JUL-2019 11:52:39

>> gltmai_ledger_main() 08-JUL-2019 11:52:39

>> get_ldg_info() 08-JUL-2019 11:52:39

<< get_ldg_info() 08-JUL-2019 11:52:39

Functional currency = INR

Calendar name = USHARIP1

Period type = MONTH0967469821

CTA ccid = 300000052785895

Retained earnings ccid = 300000031286477

Period Average Rate Type = 300000032320882

Period End Rate Type = 300000032320882

Revenue / Expense Translation Rule = PTD

Owner's Equity Translation Rule = PTD

>> get_seg_info() 08-JUL-2019 11:52:39

INSERT INTO GL_MULTI_BAL_SEG_GT (

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

CHART_OF_ACCOUNTS_ID, LEDGER_ID, TEMPLATE_ACCOUNT_CCID,

TEMPLATE_ACCOUNT_CODE)

SELECT DISTINCT CC.SEGMENT1,

'-1',

'-1',

99121,

300000031286408,

300000052785895,

'CUM_TRANS_ADJ'

FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL, GL_LEDGERS LED

WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND LED.LEDGER_ID = 300000031286408

AND GBAL.LEDGER_ID = LED.LEDGER_ID

AND CC.CHART_OF_ACCOUNTS_ID = 99121

AND GBAL.ACTUAL_FLAG = 'A'

AND GBAL.PERIOD_NAME = 'Jun-2020'

AND GBAL.CURRENCY_CODE = 'INR'

AND GBAL.TEMPLATE_ID IS NULL

AND CC.SEGMENT1 = '100'ORDER BY 1

SHRD0079: Number of records inserted into the GL_MULTI_BAL_SEG_GT table: 1.

INSERT INTO GL_TRANSLATION_BSV_GT (

CHART_OF_ACCOUNTS_ID, LEDGER_ID,

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

 TEMPLATE_CTA_CCID, BALANCING_CTA_CCID, TEMPLATE_RE_CCID)

SELECT CHART_OF_ACCOUNTS_ID, LEDGER_ID,

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

TEMPLATE_ACCOUNT_CCID, BALANCING_ACCOUNT_CCID, 99

FROM GL_MULTI_BAL_SEG_GT

SHRD0079: Number of records inserted into the GL_TRANSLATION_BSV_GT table: 1.

UPDATE GL_MULTI_BAL_SEG_GT

SET TEMPLATE_ACCOUNT_CCID = 300000031286477,

BALANCING_ACCOUNT_CCID = NULL,

TEMPLATE_ACCOUNT_CODE = 'RETAIN_EARN'

SHRD0118: Updated 1 records in table: GL_MULTI_BAL_SEG_GT

UPDATE GL_TRANSLATION_BSV_GT TBSV

SET (TEMPLATE_RE_CCID, BALANCING_RE_CCID) =

(SELECT TEMPLATE_ACCOUNT_CCID, BALANCING_ACCOUNT_CCID

FROM GL_MULTI_BAL_SEG_GT MBS

WHERE MBS.CHART_OF_ACCOUNTS_ID=TBSV.CHART_OF_ACCOUNTS_ID

AND MBS.LEDGER_ID=TBSV.LEDGER_ID

AND MBS.BAL_SEG_VALUE_1=TBSV.BAL_SEG_VALUE_1

AND MBS.BAL_SEG_VALUE_2=TBSV.BAL_SEG_VALUE_2

AND MBS.BAL_SEG_VALUE_3=TBSV.BAL_SEG_VALUE_3)

SHRD0118: Updated 1 records in table: GL_TRANSLATION_BSV_GT

<< get_seg_info() 08-JUL-2019 11:52:41

>> gludlk() 08-JUL-2019 11:52:41

<< gludlk() 08-JUL-2019 11:52:41

>> gltscs() 08-JUL-2019 11:52:41

UPDATE GL_TRANSLATION_BSV_GT TBSV

SET (EARLIEST_EVER_PERIOD_NAME, EARLIEST_EVER_PERIOD_NUM, EARLIEST_EVER_PERIOD_YEAR,

EARLIEST_NEVER_PERIOD_NAME, EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR,

FIRST_TIME_TRANSLATION_FLAG) =

(SELECT :period_name, :period_num, :period_year,

:period_name, :period_num, :period_year,

'Y' FROM DUAL)

WHERE FIRST_TIME_TRANSLATION_FLAG IS NULL

AND NOT EXISTS (SELECT 1 FROM GL_TRANSLATION_TRACKING GTT

WHERE GTT.LEDGER_ID = TBSV.LEDGER_ID

AND GTT.BAL_SEG_VALUE = TBSV.BAL_SEG_VALUE_1

AND GTT.LEDGER_ID = :ledger_id

AND GTT.TARGET_CURRENCY = :target_currency

AND GTT.AVERAGE_TRANSLATION_FLAG = 'N'

AND GTT.ACTUAL_FLAG = 'A'

Binds used : period_name - Jun-2020 : period_num - 3 : period_year - 2020 : ledger_id -

300000031286408 : target_currency - USD
SHRD0118: Updated 1 records in table: GL_TRANSLATION_BSV_GT

TTRN0004: Translating from accounting period Jun-2020 to accounting period Jun-2020.

<< gltscs() 08-JUL-2019 11:52:41

BSV - 100

DECLARE

conrate NUMBER;

denrate NUMBER;

numrate NUMBER;

BEGIN

gl_currency_api.get_closest_triangulation_rate(

x_from_currency => :currency_code,

x_to_currency => :target_currency,

x_conversion_date => to_date(:end_date,'DD-MON-YYYY'),

x_conversion_type => :eop_type,

x_max_roll_days => :max_rollback,

x_denominator => denrate,

x_numerator => numrate,

x_rate => conrate);

:eop_rate := substr(to_char(conrate,'999999999999999.999999999999999'),1,41);

:eop_denominator := substr(to_char(denrate,'999999999999999.999999999999999'),1,41);

:eop_numerator := substr(to_char(numrate,'999999999999999.999999999999999'),1,41);

EXCEPTION

WHEN gl_currency_api.NO_RATE THEN

 :eop_rate := '-99';

:eop_denominator := '1';

:eop_numerator := '1';

END;

DECLARE

conrate NUMBER;

denrate NUMBER;

numrate NUMBER;

BEGIN

gl_currency_api.get_closest_triangulation_rate(

x_from_currency => :currency_code,

x_to_currency => :target_currency,

x_conversion_date => to_date(:end_date,'DD-MON-YYYY'),

x_conversion_type => :avg_type,

x_max_roll_days => :max_rollback,

x_denominator => denrate,

x_numerator => numrate,

x_rate => conrate);

:avg_rate := substr(to_char(conrate,'999999999999999.999999999999999'),1,41);

:avg_denominator := substr(to_char(denrate,'999999999999999.999999999999999'),1,41);

:avg_numerator := substr(to_char(numrate,'999999999999999.999999999999999'),1,41);

EXCEPTION

WHEN gl_currency_api.NO_RATE THEN

:avg_rate := '-99';

:avg_denominator := '1';

:avg_numerator := '1';

END;
INSERT INTO GL_XLATE_RATES_GT

(ledger_id, period_name, to_currency_code, actual_flag,

eop_rate, eop_rate_numerator, eop_rate_denominator,

avg_rate, avg_rate_numerator, avg_rate_denominator)

SELECT

:ldg_id, :period_name, :target_currency, :actual_flag,

to_number(:eop_rate,'999999999999999.999999999999999'),

to_number(:eop_numerator,'999999999999999.999999999999999'),

to_number(:eop_denominator,'999999999999999.999999999999999'),

to_number(:avg_rate,'999999999999999.999999999999999'),

to_number(:avg_numerator,'999999999999999.999999999999999'),

to_number(:avg_denominator,'999999999999999.999999999999999')

FROM DUAL

PER2: Period - Jun-2020...

For get_rate% binds used : currency_code - INR : target_currency - USD : end_date - 30-06-2019 :
eop_type - 300000032320882 : avg_type - 300000032320882 : max_rollback - 29

TTRN0080: No conversion rate was found for accounting period Jun-2020 and conversion rate type
300000032320882.

<x gltmai_ledger_main() 08-JUL-2019 11:52:41

<x main() 08-JUL-2019 11:52:41

GLTTRN - process exiting with failure.

+---------------------------------------------------------------------------+

UTIL-CURRENT TIME (DATE=08-JUL-2019) (TIME=11:52:41)

Item Creation Error:-

When we take the Class as “Root Item Class”…….The Following Error is thrown…..
>>>>>>>>>>>>>>>> Daily Rate Import() 2019-07-17 02:31:57

Debug - TRUE

User GUID - 89E189CFDF6F378CE050480AB8A11A60

User Name - SRIHARI RAO.GURRAM

FND_GLOBAL.session_id - 8DD7F6F1635F31FCE05386A7480A738D

Beginning of gather stats operation on GL_DAILY_RATES table 2019-07-17 02:31:57

End of gather stats operation on GL_DAILY_RATES table 2019-07-17 02:31:57

Beginning of gather stats operation on GL_DAILY_RATES_INTERFACE table 2019-07-17 02:31:57

End of gather stats operation on GL_DAILY_RATES_INTERFACE table 2019-07-17 02:31:57

0 Self record(s) found, excluding them from processing, these will not show up in current import
exception report 2019-07-17 02:31:57

0 record(s) had basic rates validation done 2019-07-17 02:31:57

0 error record(s) with failed basic validation 2019-07-17 02:31:57

0 error record(s) with Duplicate rates 2019-07-17 02:31:57

0 error record(s) with Treasury rates when mode flag N for existing rates 2019-07-17 02:31:57

0 error record(s) with Treasury rates when pivot currency is not USD 2019-07-17 02:31:57

0 total error record(s) from this import rates program run 2019-07-17 02:31:57

Finished validating GL_DAILY_RATES_INTERFACE record(s) 2019-07-17 02:31:57

No Validation Failure.

Searching SYSTEM rates that USER cannot delete 2019-07-17 02:31:57

No SYSTEM rate found which USER cannot delete.

Searching SYSTEM rates that USER cannot override 2019-07-17 02:31:57

No SYSTEM rate found which USER cannot override.

0 record(s) updated (where NULL) - inverse_conversion_rate = 1 / conversion_rate 2019-07-17 02:31:57

0 record(s) updated for checking "translation rate change" program needs to be launched or not 2019-
07-17 02:31:57
Deleting rates meant for insert / delete and for which the Translation Rate Change program need not to
be launched 2019-07-17 02:31:57

0 record(s) deleted 2019-07-17 02:31:57

Insert all rates.

0 record(s) inserted 2019-07-17 02:31:57

save_autorun_errors procedure started

save_autorun_errors procedure completed 2019-07-17 02:31:57

No need for cross rate calculation.

0 record(s) of successfully imported record deleted 2019-07-17 02:31:57

Beginning of gather stats operation on GL_DAILY_RATES table 2019-07-17 02:31:57

End of gather stats operation on GL_DAILY_RATES table 2019-07-17 02:31:57

<< Daily Rate Import() 2019-07-17 02:31:57

Product interface and error table data will automatically be purged (default setting is within 30 days of
the initial load) by the Purge Interface Table scheduled process.

Prior to purge execution, the interface and error data will first be extracted and uploaded to the
particular Universal Content Management (UCM) server account for the individual

product import process that is used.If you need to reference the purged interface and error data,
then you will need to access the UCM File with the following naming convention:

ImportBulkData_<ImportJobName>_<LoadRequestId>.zip
TRANSALATION SUCCESS REPORT --- ( SCHEDULED PROCESS REPORT )

>> main() 23-JUL-2019 03:23:16

>> gltini() 23-JUL-2019 03:23:16

<< gltini() 23-JUL-2019 03:23:16

User name = SRIHARI RAO.GURRAM

Session ID = 8E51B68ACE0E0518E05386A7480A48C6

Request ID = 2188661

Ledger Short Name = 300000031286408

Access Set ID = 300000031370947

Chart of Accounts ID = 99121

Ledger ID = 300000031286408

Target Currency = USD

Period Name = Jul-2020

Balance Type = A

Balancing Segment = 100

>> glulsr() 23-JUL-2019 03:23:16

<< glulsr() 23-JUL-2019 03:23:16

>> gltmai_ledger_main() 23-JUL-2019 03:23:16

>> get_ldg_info() 23-JUL-2019 03:23:16

<< get_ldg_info() 23-JUL-2019 03:23:16

Functional currency = INR

Calendar name = USHARIP1

Period type = MONTH0967469821

CTA ccid = 300000052785895

Retained earnings ccid = 300000031286477

Period Average Rate Type = 300000052298237

Period End Rate Type = 300000052298237

Revenue / Expense Translation Rule = PTD

Owner's Equity Translation Rule = PTD

>> get_seg_info() 23-JUL-2019 03:23:16

INSERT INTO GL_MULTI_BAL_SEG_GT (

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

CHART_OF_ACCOUNTS_ID, LEDGER_ID, TEMPLATE_ACCOUNT_CCID,

TEMPLATE_ACCOUNT_CODE)

SELECT DISTINCT CC.SEGMENT1,

'-1',

'-1',

99121,

300000031286408,
 300000052785895,

'CUM_TRANS_ADJ'

FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL, GL_LEDGERS LED

WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND LED.LEDGER_ID = 300000031286408

AND GBAL.LEDGER_ID = LED.LEDGER_ID

AND CC.CHART_OF_ACCOUNTS_ID = 99121

AND GBAL.ACTUAL_FLAG = 'A'

AND GBAL.PERIOD_NAME = 'Jul-2020'

AND GBAL.CURRENCY_CODE = 'INR'

AND GBAL.TEMPLATE_ID IS NULL

AND CC.SEGMENT1 = '100'ORDER BY 1

SHRD0079: Number of records inserted into the GL_MULTI_BAL_SEG_GT table: 1.

INSERT INTO GL_TRANSLATION_BSV_GT (

CHART_OF_ACCOUNTS_ID, LEDGER_ID,

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

TEMPLATE_CTA_CCID, BALANCING_CTA_CCID, TEMPLATE_RE_CCID)

SELECT CHART_OF_ACCOUNTS_ID, LEDGER_ID,

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

TEMPLATE_ACCOUNT_CCID, BALANCING_ACCOUNT_CCID, 99

FROM GL_MULTI_BAL_SEG_GT

SHRD0079: Number of records inserted into the GL_TRANSLATION_BSV_GT table: 1.

UPDATE GL_MULTI_BAL_SEG_GT
SET TEMPLATE_ACCOUNT_CCID = 300000031286477,

BALANCING_ACCOUNT_CCID = NULL,

TEMPLATE_ACCOUNT_CODE = 'RETAIN_EARN'

SHRD0118: Updated 1 records in table: GL_MULTI_BAL_SEG_GT

UPDATE GL_TRANSLATION_BSV_GT TBSV

SET (TEMPLATE_RE_CCID, BALANCING_RE_CCID) =

(SELECT TEMPLATE_ACCOUNT_CCID, BALANCING_ACCOUNT_CCID

FROM GL_MULTI_BAL_SEG_GT MBS

WHERE MBS.CHART_OF_ACCOUNTS_ID=TBSV.CHART_OF_ACCOUNTS_ID

AND MBS.LEDGER_ID=TBSV.LEDGER_ID

AND MBS.BAL_SEG_VALUE_1=TBSV.BAL_SEG_VALUE_1

AND MBS.BAL_SEG_VALUE_2=TBSV.BAL_SEG_VALUE_2

AND MBS.BAL_SEG_VALUE_3=TBSV.BAL_SEG_VALUE_3)

SHRD0118: Updated 1 records in table: GL_TRANSLATION_BSV_GT

<< get_seg_info() 23-JUL-2019 03:23:17

>> gludlk() 23-JUL-2019 03:23:17

<< gludlk() 23-JUL-2019 03:23:17

>> gltscs() 23-JUL-2019 03:23:17

UPDATE GL_TRANSLATION_BSV_GT TBSV

SET (EARLIEST_EVER_PERIOD_NAME, EARLIEST_EVER_PERIOD_NUM, EARLIEST_EVER_PERIOD_YEAR,

EARLIEST_NEVER_PERIOD_NAME, EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR,

FIRST_TIME_TRANSLATION_FLAG) =

(SELECT :period_name, :period_num, :period_year,

:period_name, :period_num, :period_year,

'Y' FROM DUAL)

WHERE FIRST_TIME_TRANSLATION_FLAG IS NULL

AND NOT EXISTS (SELECT 1 FROM GL_TRANSLATION_TRACKING GTT

WHERE GTT.LEDGER_ID = TBSV.LEDGER_ID

AND GTT.BAL_SEG_VALUE = TBSV.BAL_SEG_VALUE_1

AND GTT.LEDGER_ID = :ledger_id

AND GTT.TARGET_CURRENCY = :target_currency

AND GTT.AVERAGE_TRANSLATION_FLAG = 'N'

AND GTT.ACTUAL_FLAG = 'A'

Binds used : period_name - Jul-2020 : period_num - 4 : period_year - 2020 : ledger_id -

300000031286408 : target_currency - USD

SHRD0118: Updated 1 records in table: GL_TRANSLATION_BSV_GT

TTRN0004: Translating from accounting period Jul-2020 to accounting period Jul-2020.

<< gltscs() 23-JUL-2019 03:23:17

BSV - 100
DECLARE

conrate NUMBER;

denrate NUMBER;

numrate NUMBER;

BEGIN

gl_currency_api.get_closest_triangulation_rate(

x_from_currency => :currency_code,

x_to_currency => :target_currency,

x_conversion_date => to_date(:end_date,'DD-MON-YYYY'),

x_conversion_type => :eop_type,

x_max_roll_days => :max_rollback,

x_denominator => denrate,

x_numerator => numrate,

x_rate => conrate);

:eop_rate := substr(to_char(conrate,'999999999999999.999999999999999'),1,41);

:eop_denominator := substr(to_char(denrate,'999999999999999.999999999999999'),1,41);

:eop_numerator := substr(to_char(numrate,'999999999999999.999999999999999'),1,41);

EXCEPTION

WHEN gl_currency_api.NO_RATE THEN

:eop_rate := '-99';

:eop_denominator := '1';

:eop_numerator := '1';

END;

DECLARE

conrate NUMBER;

denrate NUMBER;

numrate NUMBER;
BEGIN

gl_currency_api.get_closest_triangulation_rate(

x_from_currency => :currency_code,

x_to_currency => :target_currency,

x_conversion_date => to_date(:end_date,'DD-MON-YYYY'),

x_conversion_type => :avg_type,

x_max_roll_days => :max_rollback,

x_denominator => denrate,

x_numerator => numrate,

x_rate => conrate);

:avg_rate := substr(to_char(conrate,'999999999999999.999999999999999'),1,41);

:avg_denominator := substr(to_char(denrate,'999999999999999.999999999999999'),1,41);

:avg_numerator := substr(to_char(numrate,'999999999999999.999999999999999'),1,41);

EXCEPTION

WHEN gl_currency_api.NO_RATE THEN

:avg_rate := '-99';

:avg_denominator := '1';

:avg_numerator := '1';

END;

INSERT INTO GL_XLATE_RATES_GT

(ledger_id, period_name, to_currency_code, actual_flag,

eop_rate, eop_rate_numerator, eop_rate_denominator,

avg_rate, avg_rate_numerator, avg_rate_denominator)

SELECT

:ldg_id, :period_name, :target_currency, :actual_flag,

to_number(:eop_rate,'999999999999999.999999999999999'),

to_number(:eop_numerator,'999999999999999.999999999999999'),
 to_number(:eop_denominator,'999999999999999.999999999999999'),

to_number(:avg_rate,'999999999999999.999999999999999'),

to_number(:avg_numerator,'999999999999999.999999999999999'),

to_number(:avg_denominator,'999999999999999.999999999999999')

FROM DUAL

PER2: Period - Jul-2020...

For get_rate% binds used : currency_code - INR : target_currency - USD : end_date - 31-07-2019 :
eop_type - 300000052298237 : avg_type - 300000052298237 : max_rollback - 30

eop_rate - .014285714285714 : eop_denominator - 1.000000000000000 :

eop_numerator - .014285714285714

avg_rate - .014285714285714 : avg_denominator - 1.000000000000000 :

avg_numerator - .014285714285714

SHRD0079: Number of records inserted into the GL_XLATE_RATES_GT table: 1.

TTRN0081: The accounting period Jul-2020, period average rate .014285714285714 and period
end rate .014285714285714 were used for translation.

>> gltshr() 23-JUL-2019 03:23:17

SQL to get range of periods to translate ...

SELECT DISTINCT

PCUR.PERIOD_NAME, PCUR.PERIOD_NUM, PCUR.PERIOD_YEAR,

PPREV.PERIOD_NAME, PPREV.PERIOD_NUM, PPREV.PERIOD_YEAR

FROM GL_PERIOD_STATUSES PPREV,

GL_PERIOD_STATUSES PCUR,
 GL_PERIOD_TYPES PTYP,

GL_LEDGERS LDG

WHERE LDG.LEDGER_ID = :ldg_id

AND PTYP.PERIOD_TYPE = LDG.ACCOUNTED_PERIOD_TYPE

AND PCUR.LEDGER_ID = LDG.LEDGER_ID

AND PPREV.LEDGER_ID = PCUR.LEDGER_ID

AND PCUR.APPLICATION_ID = 101

AND PPREV.APPLICATION_ID = 101

AND PCUR.PERIOD_TYPE = PTYP.PERIOD_TYPE

AND PPREV.PERIOD_TYPE = PCUR.PERIOD_TYPE

AND PCUR.effective_period_num <= (:latest_to_xlate_year * 10000 + :latest_to_xlate_num)

AND PPREV.effective_period_num+0 >=

(SELECT min(HR.PERIOD_YEAR * 10000 + HR.PERIOD_NUM)

FROM GL_HISTORICAL_RATES HR

WHERE HR.LEDGER_ID = :ldg_id

AND HR.TARGET_CURRENCY = :target_curr

AND HR.USAGE_CODE = 'S'

AND (HR.PERIOD_YEAR * 10000 + HR.PERIOD_NUM) >=

(SELECT nvl(min(HR1.PERIOD_YEAR * 10000 + HR1.PERIOD_NUM),0)

FROM GL_HISTORICAL_RATES HR1,

GL_PERIODS PRD1,

GL_PERIOD_TYPES PTYP1,

GL_LEDGERS LDG1

WHERE HR1.LEDGER_ID = :ldg_id

AND LDG1.LEDGER_ID = HR1.LEDGER_ID

AND PRD1.PERIOD_SET_NAME = LDG1.PERIOD_SET_NAME

AND PRD1.PERIOD_TYPE = LDG1.ACCOUNTED_PERIOD_TYPE

AND PRD1.PERIOD_NAME = HR1.PERIOD_NAME

AND PTYP1.PERIOD_TYPE = PRD1.PERIOD_TYPE

 AND HR1.TARGET_CURRENCY = :target_curr

AND HR1.USAGE_CODE = 'S'

AND (HR1.PERIOD_YEAR * 10000 + HR1.PERIOD_NUM) >=

decode(:earliest_xlate_num,

1, ((:earliest_xlate_year - 1) * 10000 + PTYP1.NUMBER_PER_FISCAL_YEAR),

((:earliest_xlate_year * 10000 + :earliest_xlate_num) -1))))

AND PPREV.effective_period_num =

decode(PCUR.period_num,

1, (PCUR.PERIOD_YEAR-1) * 10000 + PTYP.NUMBER_PER_FISCAL_YEAR,

PCUR.effective_period_num - 1)

ORDER BY PPREV.PERIOD_YEAR ASC, PPREV.PERIOD_NUM ASC

SQL to Update GL_HISTORICAL_RATES - historical rate - rate type P ...

UPDATE gl_historical_rates ughr

SET (ughr.translated_rate, ughr.translated_amount,

ughr.rate_type, ughr.auto_roll_forward_flag,

last_update_date, last_updated_by) =

(SELECT glhr.translated_rate, glhr.translated_amount,

'P', 'Y',

systimestamp, :x_created_name

FROM GL_HISTORICAL_RATES glhr

WHERE glhr.ledger_id = ughr.ledger_id

AND glhr.code_combination_id = ughr.code_combination_id

AND glhr.usage_code = 'S'

AND glhr.period_name = :prev_period_name

AND glhr.target_currency = :target_curr)

WHERE ughr.rowid IN

(SELECT ghr2.rowid
 FROM GL_HISTORICAL_RATES ghr,

GL_HISTORICAL_RATES ghr2

WHERE ghr.ledger_id = :ldg_id

AND ghr.RATE_TYPE IN ('H','P', decode(:rule, 'YTD', 'C', NULL))

AND ghr.account_type IN ('A','L','O')

AND ghr.usage_code = 'S'

AND ghr2.usage_code = ghr.usage_code

AND ghr2.ledger_id = ghr.ledger_id

AND ghr.period_name = :prev_period_name

AND ghr2.period_name = :current_period_name

AND ghr.code_combination_id = ghr2.code_combination_id

AND ghr.target_currency = :target_curr

AND ghr2.target_currency = ghr.target_currency

AND ghr2.rate_type = 'P'

AND ghr.auto_roll_forward_flag = 'Y'

AND ( Decode(ghr.translated_rate, NULL, 'X', ghr.translated_rate) <>

Decode(ghr2.translated_rate, NULL, 'X', ghr2.translated_rate)

OR Decode(ghr.translated_amount, NULL, 'X', ghr.translated_amount) <>

Decode(ghr2.translated_amount, NULL, 'X', ghr2.translated_amount)))

SQL to Insert GL_HISTORICAL_RATES - historical rate - rate type P ...

INSERT INTO GL_HISTORICAL_RATES (

LEDGER_ID, PERIOD_NAME, PERIOD_NUM, PERIOD_YEAR,

ACCOUNT_TYPE, CODE_COMBINATION_ID, TARGET_CURRENCY,

UPDATE_FLAG, RATE_TYPE, USAGE_CODE, AUTO_ROLL_FORWARD_FLAG,

TRANSLATED_RATE, TRANSLATED_AMOUNT,

LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY)

SELECT H1.LEDGER_ID, :current_period_name, :current_period_num, :current_period_year,

 H1.ACCOUNT_TYPE, H1.CODE_COMBINATION_ID, H1.TARGET_CURRENCY,

'N', 'P', 'S', 'Y',

H1.TRANSLATED_RATE, H1.TRANSLATED_AMOUNT,

systimestamp, systimestamp, :x_created_name, :x_created_name

FROM GL_HISTORICAL_RATES H1, GL_HISTORICAL_RATES H2

WHERE H1.LEDGER_ID = :ldg_id

AND H1.RATE_TYPE in ('H','P', decode(:rule, 'YTD', 'C', NULL))

AND H1.ACCOUNT_TYPE in ('A', 'L', 'O')

AND H1.USAGE_CODE = 'S'

AND H2.USAGE_CODE (+) = 'S'

AND H2.LEDGER_ID (+) = H1.LEDGER_ID

AND H1.PERIOD_NAME = :prev_period_name

AND H2.PERIOD_NAME (+) = :current_period_name

AND H1.CODE_COMBINATION_ID = H2.CODE_COMBINATION_ID (+)

AND H1.TARGET_CURRENCY = :target_curr

AND H2.TARGET_CURRENCY (+) = H1.TARGET_CURRENCY

AND H1.AUTO_ROLL_FORWARD_FLAG = 'Y'

AND H2.UPDATE_FLAG IS NULL

stmt - Get range of periods to translate ... bind var :

ldg_id - 300000031286408 : target_curr - USD : earliest_xlate_year - 2020 : earliest_xlate_num - 4 :

latest_to_xlate_year - 2020 : latest_to_xlate_num - 4

SQL to Update GL_HISTORICAL_RATES - translation rate - rate type E ...

UPDATE gl_historical_rates glhr

SET (glhr.translated_rate, glhr.translated_amount,

glhr.last_update_date, glhr.last_updated_by) =
 (SELECT Decode(:rule, 'YTD', gltr1.EOP_RATE, gltr1.AVG_RATE),

NULL, systimestamp, :x_created_name

FROM GL_XLATE_RATES_GT gltr1

WHERE gltr1.ledger_id = glhr.ledger_id

AND gltr1.to_currency_code = glhr.target_currency

AND gltr1.period_name = glhr.period_name

AND gltr1.actual_flag = :balance_type)

WHERE glhr.rowid

IN (SELECT ghr.rowid

FROM GL_HISTORICAL_RATES ghr,

GL_XLATE_RATES_GT gtr

WHERE ghr.ledger_id = :ldg_id

AND (ghr.PERIOD_YEAR * 10000 + ghr.PERIOD_NUM) >=

(SELECT min(HR.PERIOD_YEAR * 10000 + HR.PERIOD_NUM)

FROM GL_HISTORICAL_RATES HR

WHERE HR.LEDGER_ID = :ldg_id

AND HR.TARGET_CURRENCY = :target_curr

AND HR.USAGE_CODE = 'S')

AND (ghr.PERIOD_YEAR * 10000 + ghr.PERIOD_NUM) <=

(:latest_to_xlate_year * 10000 + :latest_to_xlate_num)

AND ghr.target_currency = :target_curr

AND ghr.account_type = 'O'

AND ghr.rate_type = 'E'

AND gtr.ledger_id = ghr.ledger_id

AND gtr.to_currency_code = ghr.target_currency

AND gtr.period_name = ghr.period_name

AND gtr.actual_flag = :balance_type

AND ghr.translated_rate <>

Decode(:rule, 'YTD', gtr.eop_rate, gtr.avg_rate))

stmt - UPDATE GL_HISTORICAL_RATES - rate type E ... bind var :

x_created_name - SRIHARI RAO.GURRAM : ldg_id - 300000031286408 : target_curr - USD : rule - PTD

: balance_type - A : latest_to_xlate_year - 2020 : latest_to_xlate_num - 4

SHRD0118: Updated 0 records in table: gl_historical_rates

SQL to Insert GL_HISTORICAL_RATES - translation rate - rate type E ...

INSERT INTO GL_HISTORICAL_RATES

(CODE_COMBINATION_ID, LEDGER_ID,

PERIOD_NAME, PERIOD_NUM, PERIOD_YEAR, ACCOUNT_TYPE,

TARGET_CURRENCY, UPDATE_FLAG, RATE_TYPE,

LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,

TRANSLATED_RATE, TRANSLATED_AMOUNT, USAGE_CODE)

SELECT DISTINCT GB.CODE_COMBINATION_ID, :ldg_id,

GTR.PERIOD_NAME, GB.PERIOD_NUM, GB.PERIOD_YEAR, 'O',

GTR.TO_CURRENCY_CODE, 'N', 'E',

systimestamp, systimestamp, :x_created_name, :x_created_name,

DECODE(:rule, 'YTD', GTR.EOP_RATE, GTR.AVG_RATE), NULL, 'S'

FROM GL_CODE_COMBINATIONS CC,

GL_HISTORICAL_RATES HR,

GL_BALANCES GB,

GL_XLATE_RATES_GT GTR

WHERE CC.CHART_OF_ACCOUNTS_ID = :coa_id

AND CC.ACCOUNT_TYPE = 'O'

AND CC.TEMPLATE_ID IS NULL

AND GB.LEDGER_ID = :ldg_id

AND GB.CURRENCY_CODE = :func_curr

AND GB.ACTUAL_FLAG = :balance_type

AND GB.TEMPLATE_ID IS NULL

AND (GB.PERIOD_YEAR * 10000 + GB.PERIOD_NUM) >= (:earliest_xlate_year * 10000 +

:earliest_xlate_num)

AND (GB.PERIOD_YEAR * 10000 + GB.PERIOD_NUM) <= (:latest_to_xlate_year * 10000 +

:latest_to_xlate_num)

AND GB.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND GTR.LEDGER_ID = GB.LEDGER_ID

AND GTR.TO_CURRENCY_CODE = :target_curr

AND GTR.PERIOD_NAME = GB.PERIOD_NAME

AND GTR.ACTUAL_FLAG = :balance_type

AND HR.CODE_COMBINATION_ID (+) = GB.CODE_COMBINATION_ID

AND HR.TARGET_CURRENCY (+) = :target_curr

AND HR.LEDGER_ID (+) = :ldg_id

AND HR.PERIOD_NAME (+) = GB.PERIOD_NAME

AND HR.USAGE_CODE (+)= 'S'

AND HR.UPDATE_FLAG IS NULL

stmt - INSERT INTO GL_HISTORICAL_RATES ... bind var :

x_created_name - SRIHARI RAO.GURRAM : coa_id - 99121 : ldg_id - 300000031286408 : func_curr -

INR : target_curr - USD : rule - PTD : balance_type - A : earliest_xlate_year - 2020 : earliest_xlate_num - 4
: latest_to_xlate_year - 2020 : latest_to_xlate_num - 4

SHRD0079: Number of records inserted into the gl_historical_rates table: 2.

SHRD0024: The routine: gltshr has successfully completed.

<< gltshr() 23-JUL-2019 03:23:17

>> gltgnr() 23-JUL-2019 03:23:17

SELECT PCURR.PERIOD_NAME, PCURR.PERIOD_NUM, PCURR.PERIOD_YEAR, PCURR.QUARTER_NUM,

PPREV.PERIOD_NAME, PPREV.PERIOD_NUM, PPREV.PERIOD_YEAR, PPREV.QUARTER_NUM

FROM GL_PERIODS PCURR, GL_PERIODS PPREV, GL_LEDGERS LDG, GL_PERIOD_TYPES PTYP

WHERE PCURR.PERIOD_SET_NAME = LDG.PERIOD_SET_NAME

AND PPREV.PERIOD_SET_NAME = LDG.PERIOD_SET_NAME

AND PCURR.PERIOD_TYPE = LDG.ACCOUNTED_PERIOD_TYPE

AND PPREV.PERIOD_TYPE = LDG.ACCOUNTED_PERIOD_TYPE

AND LDG.LEDGER_ID = :ldg_id

AND PTYP.PERIOD_TYPE = PCURR.PERIOD_TYPE

AND PTYP.PERIOD_TYPE = PPREV.PERIOD_TYPE

AND PPREV.PERIOD_NUM = DECODE(PCURR.PERIOD_NUM, 1, PTYP.NUMBER_PER_FISCAL_YEAR,

PCURR.PERIOD_NUM - 1)

AND PPREV.PERIOD_YEAR = DECODE(PCURR.PERIOD_NUM, 1, PCURR.PERIOD_YEAR - 1,

PCURR.PERIOD_YEAR)

AND (PCURR.PERIOD_YEAR * 10000 + PCURR.PERIOD_NUM) >= (:earliest_year * 10000 +

:earliest_num)

AND (PCURR.PERIOD_YEAR * 10000 + PCURR.PERIOD_NUM) <= (:latest_year * 10000 + :latest_num)

ORDER BY PCURR.PERIOD_YEAR ASC, PCURR.PERIOD_NUM ASC

Binds ldg_id - 300000031286408 : earliest_year - 2020 : earliest_num - 4 : latest_year - 2020 :

latest_num - 4

INSERT INTO GL_XLATE_POSTING_INTERIM

(CODE_COMBINATION_ID, PERIOD_NAME, LEDGER_ID,

CURRENCY_CODE, PERIOD_YEAR, PERIOD_NUM, PERIOD_TYPE,

ACCOUNT_TYPE, BAL_SEG_VALUE, SEC_BAL_SEG_VALUE, BAL_SEG_VALUE_3, ACTUAL_FLAG,

INCREMENTAL_FLAG, BUDGET_VERSION_ID,

PERIOD_NET_DR, PERIOD_NET_CR,

BEGIN_BALANCE_DR, BEGIN_BALANCE_CR,

QUARTER_TO_DATE_DR, QUARTER_TO_DATE_CR,

PROJECT_TO_DATE_DR, PROJECT_TO_DATE_CR,

SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,

SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,

SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,

SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,

SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,

SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30)

SELECT DISTINCT BCUR.CODE_COMBINATION_ID, BCUR.PERIOD_NAME, BCUR.LEDGER_ID,

'USD', BCUR.PERIOD_YEAR, BCUR.PERIOD_NUM, BCUR.PERIOD_TYPE,

CC.ACCOUNT_TYPE, CC.SEGMENT1, '-1', '-1', BCUR.ACTUAL_FLAG,

'N', NULL,

--

-- period_net_dr

--

DECODE(HR.TRANSLATED_AMOUNT, NULL,

DECODE(HR.TRANSLATED_RATE, NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),
 DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

(DECODE(-1,'-1',ROUND((

((NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

),2),

ROUND(((

((NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

)/-1))*-1)-(NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0)))))),

DECODE(CC.ACCOUNT_TYPE,'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),
ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

(DECODE(-1,'-1',ROUND((

(NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

* HR.TRANSLATED_RATE

),2),ROUND(((

(NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

*HR.TRANSLATED_RATE

)/-1))*-1)- (NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))))))),

0),

--

-- period_net_cr

--

DECODE(HR.TRANSLATED_AMOUNT, NULL,

DECODE(HR.TRANSLATED_RATE, NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',
 DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

(DECODE(-1,'-1',ROUND((

((NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

),2),ROUND(((

((NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

)/-1))*-1)- (NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0)))))),

DECODE(CC.ACCOUNT_TYPE,'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),
 DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

(DECODE(-1,'-1',ROUND((

(NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

* HR.TRANSLATED_RATE

),2),ROUND(((

(NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

* HR.TRANSLATED_RATE

)/-1))*-1) -(NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0))))))),

DECODE(CC.ACCOUNT_TYPE,'X',0,

'R',HR.TRANSLATED_AMOUNT,

'E',HR.TRANSLATED_AMOUNT,

'O',HR.TRANSLATED_AMOUNT,

DECODE(BPREV.CODE_COMBINATION_ID,NULL,HR.TRANSLATED_AMOUNT,

DECODE(:budget_first_period, 'Y',HR.TRANSLATED_AMOUNT,

(DECODE(-1,'-1',ROUND((

HR.TRANSLATED_AMOUNT

+ (NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))

),2),ROUND(((

HR.TRANSLATED_AMOUNT

+ (NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))

)/-1))*-1)

)- (NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0)))))),

--

-- begin_balance_dr
--

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

DECODE(:budget_first_period, 'Y',

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',
 DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))),

--

-- begin_balance_cr

--

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),
ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

DECODE(:budget_first_period, 'Y',

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),
 'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0))),

--

-- qtd_dr

--

DECODE(:first_period_in_quarter,1,0,

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',
 DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.QUARTER_TO_DATE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))),

DECODE(:first_period_in_quarter,1,0,

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),
ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.QUARTER_TO_DATE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0))),

DECODE(BPREV.CODE_COMBINATION_ID, NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.PROJECT_TO_DATE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0)),

DECODE(BPREV.CODE_COMBINATION_ID, NULL,

DECODE(HR.TRANSLATED_RATE,NULL,
DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.PROJECT_TO_DATE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0)),

CC.SEGMENT1,CC.SEGMENT2,CC.SEGMENT3,CC.SEGMENT4,

CC.SEGMENT5, CC.SEGMENT6, CC.SEGMENT7, CC.SEGMENT8,

CC.SEGMENT9, CC.SEGMENT10, CC.SEGMENT11, CC.SEGMENT12,

CC.SEGMENT13, CC.SEGMENT14, CC.SEGMENT15, CC.SEGMENT16,

CC.SEGMENT17, CC.SEGMENT18, CC.SEGMENT19, CC.SEGMENT20,

CC.SEGMENT21, CC.SEGMENT22, CC.SEGMENT23, CC.SEGMENT24,

CC.SEGMENT25, CC.SEGMENT26, CC.SEGMENT27, CC.SEGMENT28,

CC.SEGMENT29, CC.SEGMENT30

FROM GL_CODE_COMBINATIONS CC,

 GL_XLATE_RATES_GT RATE,

GL_HISTORICAL_RATES HR,

GL_BALANCES BPREV,

GL_BALANCES BCTRANS,

GL_BALANCES BCUR

WHERE BPREV.CODE_COMBINATION_ID (+) = BCUR.CODE_COMBINATION_ID

AND BCTRANS.CODE_COMBINATION_ID (+) = BCUR.CODE_COMBINATION_ID

AND CC.CODE_COMBINATION_ID = BCUR.CODE_COMBINATION_ID

AND BCUR.CODE_COMBINATION_ID = HR.CODE_COMBINATION_ID (+)

AND HR.USAGE_CODE (+) = 'S'

AND BCUR.PERIOD_NAME = :current_period_name

AND BCUR.LEDGER_ID = 300000031286408

AND BCUR.CURRENCY_CODE = 'INR'

AND BCUR.ACTUAL_FLAG = 'A'

AND BCUR.TEMPLATE_ID IS NULL

AND BPREV.ACTUAL_FLAG (+) = 'A'

AND NVL(BPREV.TRANSLATED_FLAG (+),'N')!='R'

AND BPREV.CURRENCY_CODE (+) = 'USD'

AND BPREV.PERIOD_NAME (+) = :prev_period_name

AND BPREV.LEDGER_ID (+) = BCUR.LEDGER_ID

AND BPREV.TEMPLATE_ID (+) IS NULL

AND HR.PERIOD_NAME (+) = BCUR.PERIOD_NAME

AND HR.RATE_TYPE (+) != 'C'

AND HR.TARGET_CURRENCY (+) = 'USD'

AND HR.LEDGER_ID (+) = 300000031286408

AND BCTRANS.CURRENCY_CODE (+) = 'USD'

AND BCTRANS.PERIOD_NAME (+) = BCUR.PERIOD_NAME

AND BCTRANS.LEDGER_ID (+) = BCUR.LEDGER_ID

AND BCTRANS.ACTUAL_FLAG (+) = 'A'

AND BCTRANS.TEMPLATE_ID (+) IS NULL

AND NVL(BCTRANS.TRANSLATED_FLAG (+), 'N') != 'R'

AND RATE.PERIOD_NAME = BCUR.PERIOD_NAME

AND RATE.LEDGER_ID = BCUR.LEDGER_ID

AND RATE.TO_CURRENCY_CODE = 'USD'

AND RATE.ACTUAL_FLAG = 'A'

AND BCTRANS.LAST_UPDATE_DATE IS NULL

AND CC.SEGMENT1 IN

(SELECT DISTINCT BAL_SEG_VALUE_1

FROM GL_TRANSLATION_BSV_GT)

update gl_xlate_posting_interim p

set (begin_balance_dr, begin_balance_cr, quarter_to_date_dr, quarter_to_date_cr, project_to_date_dr,

project_to_date_cr) =

(select decode(p.period_name, mbs.earliest_ever_period_name, p.begin_balance_dr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.begin_balance_cr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.quarter_to_date_dr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.quarter_to_date_cr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.project_to_date_dr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.project_to_date_cr, 0)

from gl_translation_bsv_gt mbs

where mbs.balancing_re_ccid = p.code_combination_id

and mbs.bal_seg_value_1 = p.bal_seg_value

and mbs.bal_seg_value_2 = p.sec_bal_seg_value

and mbs.bal_seg_value_3 = p.bal_seg_value_3)

where p.code_combination_id IN (select balancing_re_ccid from gl_translation_bsv_gt mbs1

where mbs1.ledger_id = p.ledger_id

and mbs1.bal_seg_value_1 = p.bal_seg_value

and mbs1.bal_seg_value_2 = p.sec_bal_seg_value

 and mbs1.bal_seg_value_3 = p.bal_seg_value_3

and mbs1.balancing_re_ccid IS NOT NULL)

and period_num = 1

and not exists (select 1 from gl_balances b1

where b1.ledger_id = :ldg_id

and b1.code_combination_id = p.code_combination_id

and b1.period_name = :last_period

and b1.actual_flag = 'A'

and nvl(b1.translated_flag, 'N') != 'R'

and b1.currency_code = :target_currency)

Entering Phase 2 of translation

Locking gl_concurrency_control records in mode - INSERT_BALANCES

Locking gl_concurrency_control records in mode - UPDATE_BALANCES

>> gltctr() 23-JUL-2019 03:23:17

Locating period rates for period Jul-2020

<< gltctr() 23-JUL-2019 03:23:17

Translating period Jul-2020. Previous period is Jun-2020

RE Bal translation rule - PTD : Binds for 'INSERT INTO GL_XLATE_POSTING_INTERIM...'

budget_first_period - N : first_period_in_quarter - 1 : current_period_name - Jul-2020 :

prev_period_name - Jun-2020

SHRD0079: Number of records inserted into the GL_XLATE_POSTING_INTERIM table: 9.

SHRD0118: Updated 0 records in table: GL_XLATE_POSTING_INTERIM

>> gltbpi() 23-JUL-2019 03:23:17

Update statement for begin balances of retained earnings accounts - First time translation...
UPDATE GL_XLATE_POSTING_INTERIM GPI

SET (BEGIN_BALANCE_DR, BEGIN_BALANCE_CR) =

(SELECT NVL(SUM(NVL(P2.BEGIN_BALANCE_DR,0)),0) +

NVL(GPI.BEGIN_BALANCE_DR,0),

NVL(SUM(NVL(P2.BEGIN_BALANCE_CR,0)),0) +

NVL(GPI.BEGIN_BALANCE_CR,0)

FROM GL_XLATE_POSTING_INTERIM P2

WHERE P2.ACCOUNT_TYPE IN ('R','E')

AND P2.BAL_SEG_VALUE = :bsv

AND P2.SEC_BAL_SEG_VALUE = :secbsv

AND P2.BAL_SEG_VALUE_3 = :thirdbsv)

WHERE GPI.CODE_COMBINATION_ID = :ret_ccid

Update statement for begin balances of retained earnings accounts - Re-translation...

UPDATE GL_XLATE_POSTING_INTERIM GPI

SET (BEGIN_BALANCE_DR, BEGIN_BALANCE_CR) =

(SELECT NVL(SUM(NVL(BPREV.BEGIN_BALANCE_DR,0)) +

SUM(NVL(BPREV.PERIOD_NET_DR,0)),0) +

NVL(GPI.BEGIN_BALANCE_DR,0),

NVL(SUM(NVL(BPREV.BEGIN_BALANCE_CR,0)) +

SUM(NVL(BPREV.PERIOD_NET_CR,0)),0) +

NVL(GPI.BEGIN_BALANCE_CR,0)

FROM GL_BALANCES BPREV,

GL_CODE_COMBINATIONS CC

WHERE BPREV.PERIOD_NAME = :prev_period_name

AND BPREV.LEDGER_ID = :ldg_id

AND BPREV.CURRENCY_CODE = :target_curr

 AND BPREV.ACTUAL_FLAG = 'A'

AND NVL(BPREV.TRANSLATED_FLAG,'X') != 'R'

AND BPREV.TEMPLATE_ID IS NULL

AND BPREV.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND CC.ACCOUNT_TYPE IN ('R','E')

AND CC.SEGMENT1 = :bsv)

WHERE GPI.CODE_COMBINATION_ID = :ret_ccid

Updating cta period net balances

1 records from GL_XLATE_POSTING_INTERIM processed

<< gltbpi() 23-JUL-2019 03:23:17

Inserting placeholder rows into GL_BALANCES

SHRD0117: Inserted the following number of records into the GL_BALANCES table: 10

10 records in table: GL_XLATE_POSTING_INTERIM

SHRD0118: Updated 10 records in table: GL_BALANCES

>> gltmtt() 23-JUL-2019 03:23:17

BSV - 100

Add GL_TRANSLATION_TRACKING record for first-time-ever translation for the bsv

SHRD0079: Number of records inserted into the GL_TRANSLATION_TRACKING table: 1.

Add GL_TRANSLATION_STATUSES record with status set to C for all the periods between earliest ever
translated and earliest never translated for the bsv

SHRD0079: Number of records inserted into the GL_TRANSLATION_STATUSES table: 1.

Update GL_TRANSLATION_STATUSES record with status set to C for selected records for the bsv
SHRD0118: Updated 0 records in table: GL_TRANSLATION_STATUSES

<< gltmtt() 23-JUL-2019 03:23:17

SHRD0117: Inserted the following number of records into the GL_BALANCES_DELTA table: 0

>> essbase_initialize_std() 23-JUL-2019 03:23:18

>> ESS_CubeInit_std() 23-JUL-2019 03:23:18

Request Id: 2188661

<< ESS_CubeInit_std() 23-JUL-2019 03:23:18

>> ESS_WriteToFile_std() 23-JUL-2019 03:23:18

>> get_sql_stmt_std() 23-JUL-2019 03:23:18

<< get_sql_stmt_std() 23-JUL-2019 03:23:18

No Profile option set using default value

Freeing SQLDA memory

<< ESS_WriteToFile_std() 23-JUL-2019 03:23:18

<< essbase_initialize_std() 23-JUL-2019 03:23:18

Success:essbase_initialize_std

>> essbase_load_buffer_std() 23-JUL-2019 03:23:18

>> ESS_Init_std() 23-JUL-2019 03:23:18

<< ESS_Init_std() 23-JUL-2019 03:23:18

>> ESS_Login_std() 23-JUL-2019 03:23:18

>> get_essbase_user_std() 23-JUL-2019 03:23:18

>> get_essbase_user_std -CredStore() 23-JUL-2019 03:23:18

Credential Store:

map:oracle.apps.security key:FUSION_APPS_GL_ESSBASE_APPID-KEY

Credential Store:

map:oracle.apps.security key:FUSION_APPS_GL_ESSBASE_APPID-KEY
username:FUSION_APPS_GL_ESSBASE_APPID

<< get_essbase_user_std -CredStore() 23-JUL-2019 03:23:18

<< get_essbase_user_std() 23-JUL-2019 03:23:18

<< ESS_Login_std() 23-JUL-2019 03:23:18

>> ESS_SetActive_std() 23-JUL-2019 03:23:18

<< ESS_SetActive_std() 23-JUL-2019 03:23:21

>> ESS_Inc_Import_std() 23-JUL-2019 03:23:21

Data Load Buffer ID : 320005

<< ESS_Inc_Import_std() 23-JUL-2019 03:23:22

<< essbase_load_buffer_std() 23-JUL-2019 03:23:22

Success:essbase_load_buffer_std

>> essbase_commit_buffer_std() 23-JUL-2019 03:23:22

>> ESS_Inc_Commit_std() 23-JUL-2019 03:23:22

<< ESS_Inc_Commit_std() 23-JUL-2019 03:23:22

<< essbase_commit_buffer_std() 23-JUL-2019 03:23:22

Success:essbase_commit_buffer_std

<< gltgnr() 23-JUL-2019 03:23:22

>> essbase_terminate_std() 23-JUL-2019 03:23:22

>> ESS_Abort_Buffer_std() 23-JUL-2019 03:23:22

<< ESS_Abort_Buffer_std() 23-JUL-2019 03:23:22

>> ESS_Logout_std() 23-JUL-2019 03:23:22

<< ESS_Logout_std() 23-JUL-2019 03:23:22

>> ESS_Term_std() 23-JUL-2019 03:23:22

<< ESS_Term_std() 23-JUL-2019 03:23:22

<< essbase_terminate_std() 23-JUL-2019 03:23:22

Success:essbase_terminate_std

<< gltmai_ledger_main() 23-JUL-2019 03:23:22

SHRD0019: GLTTRN - process exiting after successful execution.

<< main() 23-JUL-2019 03:23:22

+---------------------------------------------------------------------------+

UTIL-CURRENT TIME (DATE=23-JUL-2019) (TIME=03:23:22)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

>> main() 23-JUL-2019 03:59:06

>> gltini() 23-JUL-2019 03:59:06

<< gltini() 23-JUL-2019 03:59:06

User name = SRIHARI RAO.GURRAM

Session ID = 8E5236B7419881B9E05384A7480AE4AA

Request ID = 2188920

Ledger Short Name = 300000031286408

Access Set ID = 300000031370947

Chart of Accounts ID = 99121

Ledger ID = 300000031286408

Target Currency = USD

Period Name = Jul-2020

Balance Type = A

Balancing Segment = 100

>> glulsr() 23-JUL-2019 03:59:06

<< glulsr() 23-JUL-2019 03:59:06

>> gltmai_ledger_main() 23-JUL-2019 03:59:06

>> get_ldg_info() 23-JUL-2019 03:59:06

<< get_ldg_info() 23-JUL-2019 03:59:06

Functional currency = INR

Calendar name = USHARIP1

Period type = MONTH0967469821

CTA ccid = 300000052785895

Retained earnings ccid = 300000031286477

Period Average Rate Type = 300000052298237

Period End Rate Type = 300000052298237

Revenue / Expense Translation Rule = PTD

Owner's Equity Translation Rule = PTD

>> get_seg_info() 23-JUL-2019 03:59:06

INSERT INTO GL_MULTI_BAL_SEG_GT (

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

CHART_OF_ACCOUNTS_ID, LEDGER_ID, TEMPLATE_ACCOUNT_CCID,

TEMPLATE_ACCOUNT_CODE)

SELECT DISTINCT CC.SEGMENT1,

'-1',

'-1',

99121,

300000031286408,

300000052785895,

'CUM_TRANS_ADJ'

FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL, GL_LEDGERS LED

WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND LED.LEDGER_ID = 300000031286408

AND GBAL.LEDGER_ID = LED.LEDGER_ID

AND CC.CHART_OF_ACCOUNTS_ID = 99121

AND GBAL.ACTUAL_FLAG = 'A'

AND GBAL.PERIOD_NAME = 'Jul-2020'

AND GBAL.CURRENCY_CODE = 'INR'

AND GBAL.TEMPLATE_ID IS NULL

AND CC.SEGMENT1 = '100'ORDER BY 1

SHRD0079: Number of records inserted into the GL_MULTI_BAL_SEG_GT table: 1.

INSERT INTO GL_TRANSLATION_BSV_GT (

CHART_OF_ACCOUNTS_ID, LEDGER_ID,

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

TEMPLATE_CTA_CCID, BALANCING_CTA_CCID, TEMPLATE_RE_CCID)

SELECT CHART_OF_ACCOUNTS_ID, LEDGER_ID,

BAL_SEG_VALUE_1, BAL_SEG_VALUE_2, BAL_SEG_VALUE_3,

TEMPLATE_ACCOUNT_CCID, BALANCING_ACCOUNT_CCID, 99

FROM GL_MULTI_BAL_SEG_GT

SHRD0079: Number of records inserted into the GL_TRANSLATION_BSV_GT table: 1.

UPDATE GL_MULTI_BAL_SEG_GT

SET TEMPLATE_ACCOUNT_CCID = 300000031286477,

BALANCING_ACCOUNT_CCID = NULL,

TEMPLATE_ACCOUNT_CODE = 'RETAIN_EARN'

SHRD0118: Updated 1 records in table: GL_MULTI_BAL_SEG_GT

UPDATE GL_TRANSLATION_BSV_GT TBSV

SET (TEMPLATE_RE_CCID, BALANCING_RE_CCID) =

(SELECT TEMPLATE_ACCOUNT_CCID, BALANCING_ACCOUNT_CCID

FROM GL_MULTI_BAL_SEG_GT MBS

WHERE MBS.CHART_OF_ACCOUNTS_ID=TBSV.CHART_OF_ACCOUNTS_ID

AND MBS.LEDGER_ID=TBSV.LEDGER_ID

AND MBS.BAL_SEG_VALUE_1=TBSV.BAL_SEG_VALUE_1
 AND MBS.BAL_SEG_VALUE_2=TBSV.BAL_SEG_VALUE_2

AND MBS.BAL_SEG_VALUE_3=TBSV.BAL_SEG_VALUE_3)

SHRD0118: Updated 1 records in table: GL_TRANSLATION_BSV_GT

<< get_seg_info() 23-JUL-2019 03:59:07

>> gludlk() 23-JUL-2019 03:59:07

<< gludlk() 23-JUL-2019 03:59:08

>> gltscs() 23-JUL-2019 03:59:08

UPDATE GL_TRANSLATION_BSV_GT TBSV

SET (EARLIEST_EVER_PERIOD_NAME, EARLIEST_EVER_PERIOD_NUM, EARLIEST_EVER_PERIOD_YEAR,

EARLIEST_NEVER_PERIOD_NAME, EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR,

FIRST_TIME_TRANSLATION_FLAG) =

(SELECT EARLIEST_EVER_PERIOD_NAME, EARLIEST_EVER_PERIOD_NUM,

EARLIEST_EVER_PERIOD_YEAR,

GP.PERIOD_NAME, GP.PERIOD_NUM, GP.PERIOD_YEAR,

'N'

FROM GL_TRANSLATION_TRACKING GTT, GL_PERIODS GP, GL_LEDGERS GL

WHERE GTT.LEDGER_ID = TBSV.LEDGER_ID

AND GTT.BAL_SEG_VALUE = TBSV.BAL_SEG_VALUE_1

AND GTT.LEDGER_ID = :ledger_id

AND GTT.TARGET_CURRENCY = :target_currency

AND GTT.AVERAGE_TRANSLATION_FLAG = 'N'

 AND GL.LEDGER_ID = GTT.LEDGER_ID

AND GP.PERIOD_SET_NAME = GL.PERIOD_SET_NAME

AND GP.PERIOD_TYPE = GL.ACCOUNTED_PERIOD_TYPE

AND (GP.PERIOD_YEAR * 10000 + GP.PERIOD_NUM) =

(SELECT GREATEST(NVL(MAX(GP1.PERIOD_YEAR * 10000 + GP1.PERIOD_NUM),

:effective_period_num), :effective_period_num)

FROM GL_PERIODS GP1

WHERE GP1.PERIOD_SET_NAME = GP.PERIOD_SET_NAME

AND GP1.PERIOD_TYPE = GP.PERIOD_TYPE

AND (GP1.PERIOD_YEAR * 10000 + GP1.PERIOD_NUM) <= :effective_period_num

AND (GP1.PERIOD_YEAR * 10000 + GP1.PERIOD_NUM) <

(GTT.EARLIEST_NEVER_PERIOD_YEAR * 10000 + GTT.EARLIEST_NEVER_PERIOD_NUM))

AND GTT.ACTUAL_FLAG = 'A')

WHERE TBSV.LEDGER_ID = :ledger_id

AND EXISTS (SELECT 1 FROM GL_TRANSLATION_TRACKING GTT1

WHERE GTT1.LEDGER_ID = TBSV.LEDGER_ID

AND GTT1.BAL_SEG_VALUE = TBSV.BAL_SEG_VALUE_1

AND GTT1.TARGET_CURRENCY = :target_currency

AND GTT1.ACTUAL_FLAG = :balance_type

AND GTT1.AVERAGE_TRANSLATION_FLAG = 'N')

Binds used : ledger_id - 300000031286408 : target_currency - USD : effective_period_num - 20200004 :

balance_type - A

SHRD0118: Updated 1 records in table: GL_TRANSLATION_BSV_GT

TTRN0004: Translating from accounting period Jul-2020 to accounting period Jul-2020.

<< gltscs() 23-JUL-2019 03:59:08

BSV - 100

Continue with phase 1 / phase 2 translation to populate GL_XLATE_RATES_GT if necessary

DECLARE

conrate NUMBER;

denrate NUMBER;

numrate NUMBER;

BEGIN

gl_currency_api.get_closest_triangulation_rate(

x_from_currency => :currency_code,

x_to_currency => :target_currency,

x_conversion_date => to_date(:end_date,'DD-MON-YYYY'),

x_conversion_type => :eop_type,

x_max_roll_days => :max_rollback,

x_denominator => denrate,

x_numerator => numrate,

x_rate => conrate);

:eop_rate := substr(to_char(conrate,'999999999999999.999999999999999'),1,41);

:eop_denominator := substr(to_char(denrate,'999999999999999.999999999999999'),1,41);

:eop_numerator := substr(to_char(numrate,'999999999999999.999999999999999'),1,41);

EXCEPTION

WHEN gl_currency_api.NO_RATE THEN

:eop_rate := '-99';

:eop_denominator := '1';

:eop_numerator := '1';

END;
DECLARE

conrate NUMBER;

denrate NUMBER;

numrate NUMBER;

BEGIN

gl_currency_api.get_closest_triangulation_rate(

x_from_currency => :currency_code,

x_to_currency => :target_currency,

x_conversion_date => to_date(:end_date,'DD-MON-YYYY'),

x_conversion_type => :avg_type,

x_max_roll_days => :max_rollback,

x_denominator => denrate,

x_numerator => numrate,

x_rate => conrate);

:avg_rate := substr(to_char(conrate,'999999999999999.999999999999999'),1,41);

:avg_denominator := substr(to_char(denrate,'999999999999999.999999999999999'),1,41);

:avg_numerator := substr(to_char(numrate,'999999999999999.999999999999999'),1,41);

EXCEPTION

WHEN gl_currency_api.NO_RATE THEN

:avg_rate := '-99';

:avg_denominator := '1';

:avg_numerator := '1';

END;

INSERT INTO GL_XLATE_RATES_GT

(ledger_id, period_name, to_currency_code, actual_flag,

eop_rate, eop_rate_numerator, eop_rate_denominator,

avg_rate, avg_rate_numerator, avg_rate_denominator)

SELECT

:ldg_id, :period_name, :target_currency, :actual_flag,

to_number(:eop_rate,'999999999999999.999999999999999'),

to_number(:eop_numerator,'999999999999999.999999999999999'),

to_number(:eop_denominator,'999999999999999.999999999999999'),

to_number(:avg_rate,'999999999999999.999999999999999'),

to_number(:avg_numerator,'999999999999999.999999999999999'),

to_number(:avg_denominator,'999999999999999.999999999999999')

FROM DUAL

PER2: Period - Jul-2020...

For get_rate% binds used : currency_code - INR : target_currency - USD : end_date - 31-07-2019 :
eop_type - 300000052298237 : avg_type - 300000052298237 : max_rollback - 30

eop_rate - .014285714285714 : eop_denominator - 1.000000000000000 :

eop_numerator - .014285714285714

avg_rate - .014285714285714 : avg_denominator - 1.000000000000000 :

avg_numerator - .014285714285714

SHRD0079: Number of records inserted into the GL_XLATE_RATES_GT table: 1.

TTRN0081: The accounting period Jul-2020, period average rate .014285714285714 and period
end rate .014285714285714 were used for translation.

>> gltshr() 23-JUL-2019 03:59:08

SQL to get range of periods to translate ...

SELECT DISTINCT
 PCUR.PERIOD_NAME, PCUR.PERIOD_NUM, PCUR.PERIOD_YEAR,

PPREV.PERIOD_NAME, PPREV.PERIOD_NUM, PPREV.PERIOD_YEAR

FROM GL_PERIOD_STATUSES PPREV,

GL_PERIOD_STATUSES PCUR,

GL_PERIOD_TYPES PTYP,

GL_LEDGERS LDG

WHERE LDG.LEDGER_ID = :ldg_id

AND PTYP.PERIOD_TYPE = LDG.ACCOUNTED_PERIOD_TYPE

AND PCUR.LEDGER_ID = LDG.LEDGER_ID

AND PPREV.LEDGER_ID = PCUR.LEDGER_ID

AND PCUR.APPLICATION_ID = 101

AND PPREV.APPLICATION_ID = 101

AND PCUR.PERIOD_TYPE = PTYP.PERIOD_TYPE

AND PPREV.PERIOD_TYPE = PCUR.PERIOD_TYPE

AND PCUR.effective_period_num <= (:latest_to_xlate_year * 10000 + :latest_to_xlate_num)

AND PPREV.effective_period_num+0 >=

(SELECT min(HR.PERIOD_YEAR * 10000 + HR.PERIOD_NUM)

FROM GL_HISTORICAL_RATES HR

WHERE HR.LEDGER_ID = :ldg_id

AND HR.TARGET_CURRENCY = :target_curr

AND HR.USAGE_CODE = 'S'

AND (HR.PERIOD_YEAR * 10000 + HR.PERIOD_NUM) >=

(SELECT nvl(min(HR1.PERIOD_YEAR * 10000 + HR1.PERIOD_NUM),0)

FROM GL_HISTORICAL_RATES HR1,

GL_PERIODS PRD1,

GL_PERIOD_TYPES PTYP1,

GL_LEDGERS LDG1

WHERE HR1.LEDGER_ID = :ldg_id

AND LDG1.LEDGER_ID = HR1.LEDGER_ID

 AND PRD1.PERIOD_SET_NAME = LDG1.PERIOD_SET_NAME

AND PRD1.PERIOD_TYPE = LDG1.ACCOUNTED_PERIOD_TYPE

AND PRD1.PERIOD_NAME = HR1.PERIOD_NAME

AND PTYP1.PERIOD_TYPE = PRD1.PERIOD_TYPE

AND HR1.TARGET_CURRENCY = :target_curr

AND HR1.USAGE_CODE = 'S'

AND (HR1.PERIOD_YEAR * 10000 + HR1.PERIOD_NUM) >=

decode(:earliest_xlate_num,

1, ((:earliest_xlate_year - 1) * 10000 + PTYP1.NUMBER_PER_FISCAL_YEAR),

((:earliest_xlate_year * 10000 + :earliest_xlate_num) -1))))

AND PPREV.effective_period_num =

decode(PCUR.period_num,

1, (PCUR.PERIOD_YEAR-1) * 10000 + PTYP.NUMBER_PER_FISCAL_YEAR,

PCUR.effective_period_num - 1)

ORDER BY PPREV.PERIOD_YEAR ASC, PPREV.PERIOD_NUM ASC

SQL to Update GL_HISTORICAL_RATES - historical rate - rate type P ...

UPDATE gl_historical_rates ughr

SET (ughr.translated_rate, ughr.translated_amount,

ughr.rate_type, ughr.auto_roll_forward_flag,

last_update_date, last_updated_by) =

(SELECT glhr.translated_rate, glhr.translated_amount,

'P', 'Y',

systimestamp, :x_created_name

FROM GL_HISTORICAL_RATES glhr

WHERE glhr.ledger_id = ughr.ledger_id

AND glhr.code_combination_id = ughr.code_combination_id

AND glhr.usage_code = 'S'

 AND glhr.period_name = :prev_period_name

AND glhr.target_currency = :target_curr)

WHERE ughr.rowid IN

(SELECT ghr2.rowid

FROM GL_HISTORICAL_RATES ghr,

GL_HISTORICAL_RATES ghr2

WHERE ghr.ledger_id = :ldg_id

AND ghr.RATE_TYPE IN ('H','P', decode(:rule, 'YTD', 'C', NULL))

AND ghr.account_type IN ('A','L','O')

AND ghr.usage_code = 'S'

AND ghr2.usage_code = ghr.usage_code

AND ghr2.ledger_id = ghr.ledger_id

AND ghr.period_name = :prev_period_name

AND ghr2.period_name = :current_period_name

AND ghr.code_combination_id = ghr2.code_combination_id

AND ghr.target_currency = :target_curr

AND ghr2.target_currency = ghr.target_currency

AND ghr2.rate_type = 'P'

AND ghr.auto_roll_forward_flag = 'Y'

AND ( Decode(ghr.translated_rate, NULL, 'X', ghr.translated_rate) <>

Decode(ghr2.translated_rate, NULL, 'X', ghr2.translated_rate)

OR Decode(ghr.translated_amount, NULL, 'X', ghr.translated_amount) <>

Decode(ghr2.translated_amount, NULL, 'X', ghr2.translated_amount)))

SQL to Insert GL_HISTORICAL_RATES - historical rate - rate type P ...

INSERT INTO GL_HISTORICAL_RATES (

LEDGER_ID, PERIOD_NAME, PERIOD_NUM, PERIOD_YEAR,

ACCOUNT_TYPE, CODE_COMBINATION_ID, TARGET_CURRENCY,

 UPDATE_FLAG, RATE_TYPE, USAGE_CODE, AUTO_ROLL_FORWARD_FLAG,

TRANSLATED_RATE, TRANSLATED_AMOUNT,

LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY)

SELECT H1.LEDGER_ID, :current_period_name, :current_period_num, :current_period_year,

H1.ACCOUNT_TYPE, H1.CODE_COMBINATION_ID, H1.TARGET_CURRENCY,

'N', 'P', 'S', 'Y',

H1.TRANSLATED_RATE, H1.TRANSLATED_AMOUNT,

systimestamp, systimestamp, :x_created_name, :x_created_name

FROM GL_HISTORICAL_RATES H1, GL_HISTORICAL_RATES H2

WHERE H1.LEDGER_ID = :ldg_id

AND H1.RATE_TYPE in ('H','P', decode(:rule, 'YTD', 'C', NULL))

AND H1.ACCOUNT_TYPE in ('A', 'L', 'O')

AND H1.USAGE_CODE = 'S'

AND H2.USAGE_CODE (+) = 'S'

AND H2.LEDGER_ID (+) = H1.LEDGER_ID

AND H1.PERIOD_NAME = :prev_period_name

AND H2.PERIOD_NAME (+) = :current_period_name

AND H1.CODE_COMBINATION_ID = H2.CODE_COMBINATION_ID (+)

AND H1.TARGET_CURRENCY = :target_curr

AND H2.TARGET_CURRENCY (+) = H1.TARGET_CURRENCY

AND H1.AUTO_ROLL_FORWARD_FLAG = 'Y'

AND H2.UPDATE_FLAG IS NULL

stmt - Get range of periods to translate ... bind var :

ldg_id - 300000031286408 : target_curr - USD : earliest_xlate_year - 2020 : earliest_xlate_num - 4 :

latest_to_xlate_year - 2020 : latest_to_xlate_num - 4
SQL to Update GL_HISTORICAL_RATES - translation rate - rate type E ...

UPDATE gl_historical_rates glhr

SET (glhr.translated_rate, glhr.translated_amount,

glhr.last_update_date, glhr.last_updated_by) =

(SELECT Decode(:rule, 'YTD', gltr1.EOP_RATE, gltr1.AVG_RATE),

NULL, systimestamp, :x_created_name

FROM GL_XLATE_RATES_GT gltr1

WHERE gltr1.ledger_id = glhr.ledger_id

AND gltr1.to_currency_code = glhr.target_currency

AND gltr1.period_name = glhr.period_name

AND gltr1.actual_flag = :balance_type)

WHERE glhr.rowid

IN (SELECT ghr.rowid

FROM GL_HISTORICAL_RATES ghr,

GL_XLATE_RATES_GT gtr

WHERE ghr.ledger_id = :ldg_id

AND (ghr.PERIOD_YEAR * 10000 + ghr.PERIOD_NUM) >=

(SELECT min(HR.PERIOD_YEAR * 10000 + HR.PERIOD_NUM)

FROM GL_HISTORICAL_RATES HR

WHERE HR.LEDGER_ID = :ldg_id

AND HR.TARGET_CURRENCY = :target_curr

AND HR.USAGE_CODE = 'S')

AND (ghr.PERIOD_YEAR * 10000 + ghr.PERIOD_NUM) <=

(:latest_to_xlate_year * 10000 + :latest_to_xlate_num)

AND ghr.target_currency = :target_curr

AND ghr.account_type = 'O'

AND ghr.rate_type = 'E'

AND gtr.ledger_id = ghr.ledger_id

AND gtr.to_currency_code = ghr.target_currency

 AND gtr.period_name = ghr.period_name

AND gtr.actual_flag = :balance_type

AND ghr.translated_rate <>

Decode(:rule, 'YTD', gtr.eop_rate, gtr.avg_rate))

stmt - UPDATE GL_HISTORICAL_RATES - rate type E ... bind var :

x_created_name - SRIHARI RAO.GURRAM : ldg_id - 300000031286408 : target_curr - USD : rule - PTD

: balance_type - A : latest_to_xlate_year - 2020 : latest_to_xlate_num - 4

SHRD0118: Updated 0 records in table: gl_historical_rates

SQL to Insert GL_HISTORICAL_RATES - translation rate - rate type E ...

INSERT INTO GL_HISTORICAL_RATES

(CODE_COMBINATION_ID, LEDGER_ID,

PERIOD_NAME, PERIOD_NUM, PERIOD_YEAR, ACCOUNT_TYPE,

TARGET_CURRENCY, UPDATE_FLAG, RATE_TYPE,

LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,

TRANSLATED_RATE, TRANSLATED_AMOUNT, USAGE_CODE)

SELECT DISTINCT GB.CODE_COMBINATION_ID, :ldg_id,

GTR.PERIOD_NAME, GB.PERIOD_NUM, GB.PERIOD_YEAR, 'O',

GTR.TO_CURRENCY_CODE, 'N', 'E',

systimestamp, systimestamp, :x_created_name, :x_created_name,

DECODE(:rule, 'YTD', GTR.EOP_RATE, GTR.AVG_RATE), NULL, 'S'

FROM GL_CODE_COMBINATIONS CC,

GL_HISTORICAL_RATES HR,

GL_BALANCES GB,

GL_XLATE_RATES_GT GTR

WHERE CC.CHART_OF_ACCOUNTS_ID = :coa_id

AND CC.ACCOUNT_TYPE = 'O'

AND CC.TEMPLATE_ID IS NULL

AND GB.LEDGER_ID = :ldg_id

AND GB.CURRENCY_CODE = :func_curr

AND GB.ACTUAL_FLAG = :balance_type

AND GB.TEMPLATE_ID IS NULL

AND (GB.PERIOD_YEAR * 10000 + GB.PERIOD_NUM) >= (:earliest_xlate_year * 10000 +

:earliest_xlate_num)

AND (GB.PERIOD_YEAR * 10000 + GB.PERIOD_NUM) <= (:latest_to_xlate_year * 10000 +

:latest_to_xlate_num)

AND GB.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND GTR.LEDGER_ID = GB.LEDGER_ID

AND GTR.TO_CURRENCY_CODE = :target_curr

AND GTR.PERIOD_NAME = GB.PERIOD_NAME

AND GTR.ACTUAL_FLAG = :balance_type

AND HR.CODE_COMBINATION_ID (+) = GB.CODE_COMBINATION_ID

AND HR.TARGET_CURRENCY (+) = :target_curr

AND HR.LEDGER_ID (+) = :ldg_id

AND HR.PERIOD_NAME (+) = GB.PERIOD_NAME

AND HR.USAGE_CODE (+)= 'S'

AND HR.UPDATE_FLAG IS NULL

stmt - INSERT INTO GL_HISTORICAL_RATES ... bind var :

x_created_name - SRIHARI RAO.GURRAM : coa_id - 99121 : ldg_id - 300000031286408 : func_curr -

INR : target_curr - USD : rule - PTD : balance_type - A : earliest_xlate_year - 2020 : earliest_xlate_num - 4
: latest_to_xlate_year - 2020 : latest_to_xlate_num - 4

SHRD0079: Number of records inserted into the gl_historical_rates table: 0.

SHRD0024: The routine: gltshr has successfully completed.

<< gltshr() 23-JUL-2019 03:59:08

>> gltgir() 23-JUL-2019 03:59:08

INSERT INTO GL_TRANSLATION_INTERIM

(CODE_COMBINATION_ID, PERIOD_NAME, LEDGER_ID,

CURRENCY_CODE, PERIOD_YEAR, PERIOD_NUM, PERIOD_TYPE,

ACCOUNT_TYPE, BAL_SEG_VALUE, SEC_BAL_SEG_VALUE, BAL_SEG_VALUE_3,

ACTUAL_FLAG, INCREMENTAL_FLAG, PERIOD_NET_DR, PERIOD_NET_CR,

QUARTER_TO_DATE_DR, QUARTER_TO_DATE_CR,

PROJECT_TO_DATE_DR, PROJECT_TO_DATE_CR,

BEGIN_BALANCE_DR, BEGIN_BALANCE_CR,

SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,

SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,

SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,

SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,

SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,

SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30)

SELECT GB.CODE_COMBINATION_ID, GB.PERIOD_NAME, GB.LEDGER_ID,

:target_curr, GB.PERIOD_YEAR, GB.PERIOD_NUM, GB.PERIOD_TYPE,

CC.ACCOUNT_TYPE, CC.SEGMENT1, '-1', '-1',

GB.ACTUAL_FLAG, 'Y', GB.PERIOD_NET_DR, GB.PERIOD_NET_CR,

GB.QUARTER_TO_DATE_DR, GB.QUARTER_TO_DATE_CR,

GB.PROJECT_TO_DATE_DR, GB.PROJECT_TO_DATE_CR,

GB.BEGIN_BALANCE_DR, GB.BEGIN_BALANCE_CR,

CC.SEGMENT1, CC.SEGMENT2, CC.SEGMENT3, CC.SEGMENT4, CC.SEGMENT5,

CC.SEGMENT6, CC.SEGMENT7, CC.SEGMENT8, CC.SEGMENT9, CC.SEGMENT10,

CC.SEGMENT11, CC.SEGMENT12, CC.SEGMENT13, CC.SEGMENT14, CC.SEGMENT15,

CC.SEGMENT16, CC.SEGMENT17, CC.SEGMENT18, CC.SEGMENT19, CC.SEGMENT20,

CC.SEGMENT21, CC.SEGMENT22, CC.SEGMENT23, CC.SEGMENT24, CC.SEGMENT25,

CC.SEGMENT26, CC.SEGMENT27, CC.SEGMENT28, CC.SEGMENT29, CC.SEGMENT30

FROM GL_BALANCES GT, GL_CODE_COMBINATIONS CC,

GL_BALANCES GB, GL_TRANSLATION_STATUSES TS

WHERE TS.LEDGER_ID = :ldg_id

AND TS.TARGET_CURRENCY = :target_curr

AND TS.STATUS = 'U'

AND TS.ACTUAL_FLAG = 'A'

AND TS.AVERAGE_TRANSLATION_FLAG = 'N'

AND GB.ACTUAL_FLAG = 'A'

AND GB.TEMPLATE_ID IS NULL

AND GB.LEDGER_ID = :ldg_id

AND GB.CURRENCY_CODE = :func_curr

AND NVL(GB.TRANSLATED_FLAG,'X') != 'R'

AND GB.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND GB.PERIOD_NAME = GT.PERIOD_NAME

AND GT.LEDGER_ID = GB.LEDGER_ID

AND GT.ACTUAL_FLAG = 'A'

AND GT.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

AND GT.CURRENCY_CODE = :target_curr

AND GT.TEMPLATE_ID IS NULL

AND GT.TRANSLATED_FLAG = 'N'

AND (GT.period_year*10000+GT.period_num) <= (:period_year*10000+:period_num)

AND GT.PERIOD_NAME = TS.PERIOD_NAME

AND CC.SEGMENT1 = DECODE (GT.LEDGER_ID, NULL, NULL, TS.BAL_SEG_VALUE)

AND CC.SEGMENT1 IN

(SELECT DISTINCT BAL_SEG_VALUE_1

FROM GL_TRANSLATION_BSV_GT)

AND TS.BAL_SEG_VALUE = :bsegval

TTRN0015: No records were found in phase 1 of translation. Proceeding to phase 2.

<< gltgir() 23-JUL-2019 03:59:08

>> gltgnr() 23-JUL-2019 03:59:08

SELECT PCURR.PERIOD_NAME, PCURR.PERIOD_NUM, PCURR.PERIOD_YEAR, PCURR.QUARTER_NUM,

PPREV.PERIOD_NAME, PPREV.PERIOD_NUM, PPREV.PERIOD_YEAR, PPREV.QUARTER_NUM

FROM GL_PERIODS PCURR, GL_PERIODS PPREV, GL_LEDGERS LDG, GL_PERIOD_TYPES PTYP

WHERE PCURR.PERIOD_SET_NAME = LDG.PERIOD_SET_NAME

AND PPREV.PERIOD_SET_NAME = LDG.PERIOD_SET_NAME

AND PCURR.PERIOD_TYPE = LDG.ACCOUNTED_PERIOD_TYPE

AND PPREV.PERIOD_TYPE = LDG.ACCOUNTED_PERIOD_TYPE

AND LDG.LEDGER_ID = :ldg_id

AND PTYP.PERIOD_TYPE = PCURR.PERIOD_TYPE

AND PTYP.PERIOD_TYPE = PPREV.PERIOD_TYPE

AND PPREV.PERIOD_NUM = DECODE(PCURR.PERIOD_NUM, 1, PTYP.NUMBER_PER_FISCAL_YEAR,

PCURR.PERIOD_NUM - 1)

AND PPREV.PERIOD_YEAR = DECODE(PCURR.PERIOD_NUM, 1, PCURR.PERIOD_YEAR - 1,

PCURR.PERIOD_YEAR)

AND (PCURR.PERIOD_YEAR * 10000 + PCURR.PERIOD_NUM) >= (:earliest_year * 10000 +

:earliest_num)

AND (PCURR.PERIOD_YEAR * 10000 + PCURR.PERIOD_NUM) <= (:latest_year * 10000 + :latest_num)

ORDER BY PCURR.PERIOD_YEAR ASC, PCURR.PERIOD_NUM ASC

Binds ldg_id - 300000031286408 : earliest_year - 2020 : earliest_num - 4 : latest_year - 2020 :

latest_num - 4

INSERT INTO GL_XLATE_POSTING_INTERIM

(CODE_COMBINATION_ID, PERIOD_NAME, LEDGER_ID,

CURRENCY_CODE, PERIOD_YEAR, PERIOD_NUM, PERIOD_TYPE,

ACCOUNT_TYPE, BAL_SEG_VALUE, SEC_BAL_SEG_VALUE, BAL_SEG_VALUE_3, ACTUAL_FLAG,

INCREMENTAL_FLAG, BUDGET_VERSION_ID,

PERIOD_NET_DR, PERIOD_NET_CR,

BEGIN_BALANCE_DR, BEGIN_BALANCE_CR,

QUARTER_TO_DATE_DR, QUARTER_TO_DATE_CR,

PROJECT_TO_DATE_DR, PROJECT_TO_DATE_CR,

SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,

SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,

SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,

SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,

SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,

SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30)

SELECT DISTINCT BCUR.CODE_COMBINATION_ID, BCUR.PERIOD_NAME, BCUR.LEDGER_ID,

'USD', BCUR.PERIOD_YEAR, BCUR.PERIOD_NUM, BCUR.PERIOD_TYPE,

CC.ACCOUNT_TYPE, CC.SEGMENT1, '-1', '-1', BCUR.ACTUAL_FLAG,

'N', NULL,

--

-- period_net_dr

--

DECODE(HR.TRANSLATED_AMOUNT, NULL,

DECODE(HR.TRANSLATED_RATE, NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),
ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

(DECODE(-1,'-1',ROUND((

((NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

),2),

ROUND(((

((NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

)/-1))*-1)-(NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0)))))),

DECODE(CC.ACCOUNT_TYPE,'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),
ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_DR,0) * HR.TRANSLATED_RATE)/-1))*-1),

(DECODE(-1,'-1',ROUND((

(NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

* HR.TRANSLATED_RATE

),2),ROUND(((

(NVL(BCUR.BEGIN_BALANCE_DR,0) + NVL(BCUR.PERIOD_NET_DR,0))

*HR.TRANSLATED_RATE

)/-1))*-1)- (NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))))))),

0),

--

-- period_net_cr

--

DECODE(HR.TRANSLATED_AMOUNT, NULL,

DECODE(HR.TRANSLATED_RATE, NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),
 'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1),

(DECODE(-1,'-1',ROUND((

((NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

),2),ROUND(((

((NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

/ NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0)

)/-1))*-1)- (NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0)))))),

DECODE(CC.ACCOUNT_TYPE,'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'E',
 DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

DECODE(:budget_first_period, 'Y',

DECODE(-1,'-1',ROUND((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.PERIOD_NET_CR,0) * HR.TRANSLATED_RATE)/-1))*-1),

(DECODE(-1,'-1',ROUND((

(NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

* HR.TRANSLATED_RATE

),2),ROUND(((

(NVL(BCUR.BEGIN_BALANCE_CR,0) + NVL(BCUR.PERIOD_NET_CR,0))

* HR.TRANSLATED_RATE

)/-1))*-1) -(NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0))))))),

DECODE(CC.ACCOUNT_TYPE,'X',0,

'R',HR.TRANSLATED_AMOUNT,

'E',HR.TRANSLATED_AMOUNT,

'O',HR.TRANSLATED_AMOUNT,

DECODE(BPREV.CODE_COMBINATION_ID,NULL,HR.TRANSLATED_AMOUNT,

DECODE(:budget_first_period, 'Y',HR.TRANSLATED_AMOUNT,

(DECODE(-1,'-1',ROUND((

HR.TRANSLATED_AMOUNT

+ (NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))

),2),ROUND(((

HR.TRANSLATED_AMOUNT
 + (NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))

)/-1))*-1)

)- (NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0)))))),

--

-- begin_balance_dr

--

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),
DECODE(:budget_first_period, 'Y',

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_DR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

NVL(BPREV.BEGIN_BALANCE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))),

--

-- begin_balance_cr

--

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_AMOUNT,NULL,
 DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

DECODE(:budget_first_period, 'Y',

DECODE(HR.TRANSLATED_AMOUNT,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),
ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE),2),

ROUND(((NVL(BCUR.BEGIN_BALANCE_CR,0) * HR.TRANSLATED_RATE)/-1))*-1)),

0),

NVL(BPREV.BEGIN_BALANCE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0))),

--

-- qtd_dr

--

DECODE(:first_period_in_quarter,1,0,

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),
 'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.QUARTER_TO_DATE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0))),

DECODE(:first_period_in_quarter,1,0,

DECODE(BPREV.CODE_COMBINATION_ID,NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',
 DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.QUARTER_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.QUARTER_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.QUARTER_TO_DATE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0))),

DECODE(BPREV.CODE_COMBINATION_ID, NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),
 DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_DR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.PROJECT_TO_DATE_DR,0) + NVL(BPREV.PERIOD_NET_DR,0)),

DECODE(BPREV.CODE_COMBINATION_ID, NULL,

DECODE(HR.TRANSLATED_RATE,NULL,

DECODE(CC.ACCOUNT_TYPE, 'X',0,

'R',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'E',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

'O',

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.AVG_RATE_DENOMINATOR,1)) * NVL(RATE.AVG_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.AVG_RATE_DENOMINATOR,1)) *
NVL(RATE.AVG_RATE_NUMERATOR,0))/-1)*-1),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) /
NVL(RATE.EOP_RATE_DENOMINATOR,1)) * NVL(RATE.EOP_RATE_NUMERATOR,0),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) / NVL(RATE.EOP_RATE_DENOMINATOR,1)) *
NVL(RATE.EOP_RATE_NUMERATOR,0))/-1)*-1)),

DECODE(-1,'-1',ROUND((NVL(BCUR.PROJECT_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0)),2),

ROUND(((NVL(BCUR.PROJECT_TO_DATE_CR,0) * NVL(HR.TRANSLATED_RATE,0))/-1))*-1)),

NVL(BPREV.PROJECT_TO_DATE_CR,0) + NVL(BPREV.PERIOD_NET_CR,0)),

CC.SEGMENT1,CC.SEGMENT2,CC.SEGMENT3,CC.SEGMENT4,

CC.SEGMENT5, CC.SEGMENT6, CC.SEGMENT7, CC.SEGMENT8,

CC.SEGMENT9, CC.SEGMENT10, CC.SEGMENT11, CC.SEGMENT12,

CC.SEGMENT13, CC.SEGMENT14, CC.SEGMENT15, CC.SEGMENT16,

CC.SEGMENT17, CC.SEGMENT18, CC.SEGMENT19, CC.SEGMENT20,

CC.SEGMENT21, CC.SEGMENT22, CC.SEGMENT23, CC.SEGMENT24,

CC.SEGMENT25, CC.SEGMENT26, CC.SEGMENT27, CC.SEGMENT28,

CC.SEGMENT29, CC.SEGMENT30

FROM GL_CODE_COMBINATIONS CC,

GL_XLATE_RATES_GT RATE,

GL_HISTORICAL_RATES HR,

GL_BALANCES BPREV,

GL_BALANCES BCTRANS,

GL_BALANCES BCUR

WHERE BPREV.CODE_COMBINATION_ID (+) = BCUR.CODE_COMBINATION_ID

AND BCTRANS.CODE_COMBINATION_ID (+) = BCUR.CODE_COMBINATION_ID

AND CC.CODE_COMBINATION_ID = BCUR.CODE_COMBINATION_ID

AND BCUR.CODE_COMBINATION_ID = HR.CODE_COMBINATION_ID (+)

AND HR.USAGE_CODE (+) = 'S'

AND BCUR.PERIOD_NAME = :current_period_name

AND BCUR.LEDGER_ID = 300000031286408

AND BCUR.CURRENCY_CODE = 'INR'

AND BCUR.ACTUAL_FLAG = 'A'

AND BCUR.TEMPLATE_ID IS NULL

AND BPREV.ACTUAL_FLAG (+) = 'A'

AND NVL(BPREV.TRANSLATED_FLAG (+),'N')!='R'

AND BPREV.CURRENCY_CODE (+) = 'USD'

AND BPREV.PERIOD_NAME (+) = :prev_period_name

AND BPREV.LEDGER_ID (+) = BCUR.LEDGER_ID

AND BPREV.TEMPLATE_ID (+) IS NULL

AND HR.PERIOD_NAME (+) = BCUR.PERIOD_NAME

AND HR.RATE_TYPE (+) != 'C'

AND HR.TARGET_CURRENCY (+) = 'USD'

AND HR.LEDGER_ID (+) = 300000031286408

AND BCTRANS.CURRENCY_CODE (+) = 'USD'

AND BCTRANS.PERIOD_NAME (+) = BCUR.PERIOD_NAME

AND BCTRANS.LEDGER_ID (+) = BCUR.LEDGER_ID

AND BCTRANS.ACTUAL_FLAG (+) = 'A'

AND BCTRANS.TEMPLATE_ID (+) IS NULL

AND NVL(BCTRANS.TRANSLATED_FLAG (+), 'N') != 'R'

AND RATE.PERIOD_NAME = BCUR.PERIOD_NAME

AND RATE.LEDGER_ID = BCUR.LEDGER_ID

AND RATE.TO_CURRENCY_CODE = 'USD'

AND RATE.ACTUAL_FLAG = 'A'

AND BCTRANS.LAST_UPDATE_DATE IS NULL

AND CC.SEGMENT1 IN

(SELECT DISTINCT BAL_SEG_VALUE_1

FROM GL_TRANSLATION_BSV_GT)

update gl_xlate_posting_interim p

set (begin_balance_dr, begin_balance_cr, quarter_to_date_dr, quarter_to_date_cr, project_to_date_dr,

project_to_date_cr) =

(select decode(p.period_name, mbs.earliest_ever_period_name, p.begin_balance_dr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.begin_balance_cr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.quarter_to_date_dr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.quarter_to_date_cr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.project_to_date_dr, 0),

decode(p.period_name, mbs.earliest_ever_period_name, p.project_to_date_cr, 0)

from gl_translation_bsv_gt mbs

where mbs.balancing_re_ccid = p.code_combination_id

and mbs.bal_seg_value_1 = p.bal_seg_value

and mbs.bal_seg_value_2 = p.sec_bal_seg_value

 and mbs.bal_seg_value_3 = p.bal_seg_value_3)

where p.code_combination_id IN (select balancing_re_ccid from gl_translation_bsv_gt mbs1

where mbs1.ledger_id = p.ledger_id

and mbs1.bal_seg_value_1 = p.bal_seg_value

and mbs1.bal_seg_value_2 = p.sec_bal_seg_value

and mbs1.bal_seg_value_3 = p.bal_seg_value_3

and mbs1.balancing_re_ccid IS NOT NULL)

and period_num = 1

and not exists (select 1 from gl_balances b1

where b1.ledger_id = :ldg_id

and b1.code_combination_id = p.code_combination_id

and b1.period_name = :last_period

and b1.actual_flag = 'A'

and nvl(b1.translated_flag, 'N') != 'R'

and b1.currency_code = :target_currency)

Entering Phase 2 of translation

Locking gl_concurrency_control records in mode - INSERT_BALANCES

Locking gl_concurrency_control records in mode - UPDATE_BALANCES

>> gltctr() 23-JUL-2019 03:59:08

Locating period rates for period Jul-2020

<< gltctr() 23-JUL-2019 03:59:08

Translating period Jul-2020. Previous period is Jun-2020

RE Bal translation rule - PTD : Binds for 'INSERT INTO GL_XLATE_POSTING_INTERIM...'

budget_first_period - N : first_period_in_quarter - 1 : current_period_name - Jul-2020 :

prev_period_name - Jun-2020
SHRD0079: Number of records inserted into the GL_XLATE_POSTING_INTERIM table: 0.

<< gltgnr() 23-JUL-2019 03:59:08

<< gltmai_ledger_main() 23-JUL-2019 03:59:08

SHRD0019: GLTTRN - process exiting after successful execution.

<< main() 23-JUL-2019 03:59:08

+---------------------------------------------------------------------------+

UTIL-CURRENT TIME (DATE=23-JUL-2019) (TIME=03:59:08)

+---------------------------------------------------------------------------+

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

dummy file is deleted

================ BIPJobExecutable Execution Started ============

OutputFile : /u01/APPLTOP/instance/ess/rfd/2189131/out/2189131.xml

Request ID: 2189131 ClassLoader is weblogic.utils.classloaders.GenericClassLoader@3d7f8ed8 finder:

weblogic.utils.classloaders.CodeGenClassFinder@215fa802 annotation:
ORA_FSCM_ESSAPP#V2.0.rAL15hJMO8KCqktRb1ZAbA@

RequestId:2189131

ReportID : /Financials/General Ledger/Trial Balances/GlTrialBalance.xdo

The bipJobID : 18406

bipJobID is updated to ESSRuntimeService

BIP_STATUS_URL is added to ESSRuntimeService

================ BIPJobExecutable is running in async mode. ============

================ Please check the ess status for more detail info. ============