Convert Date Time to Another Timezone

SELECT
to_char(
CAST((
FROM_TZ(CAST(sysdate AS TIMESTAMP),'GMT') 
AT TIME ZONE 'Asia/Dubai'
) AS DATE) 
,'HH:MI PM') 
Asia_Dubai
FROM
dual

Oracle Fusion Technical: How to create a Financial Extract

1. Submit program name: Generate Data File for Export
2. Program has multiple parameters:

• I am running here for: Payables Transactions Extract

• Provide: File Prefix: APINVEXT01

[so that you can search in content management server (file import and export)]

• Once you sumit: it will submit: Payables Transactions Extract
• You will find a file crated in UCM server [setup needs to be done, else no file]

Oracle Fusion Technical: How to create an ESS Job in Fusion

1. How to create an ESS Job in Fusion

> Setup and Maintenance > Tasks > Search > Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications

> There you will be able to create custom job

1. Job Application Name: FscmEss
2. Job Type: BIPJobType
3. ReportID: /Custom/AA/xxessjob01Report.xdo
4. Path: /fin/ap
5. add parameters: [you need to create a Value set or Lookup based parameter only, BIP LOV does not apply here, only parameter seq is the relation between ess job param and bip job param]

Pre-Req
1. create a bi publisher report, notedown its xdo file path, [also create its rtf template, without that program will give error]

/Custom/AA/xxessjob01.xdm

/Custom/AA/xxessjob01Report.xdo

BI Publisher: function listing

https://docs.google.com/spreadsheets/d/1f2-QJ8l2_FpSGyihVeJaHiPOy9-MKzYzzTS8SpQy2qs/edit#gid=1

bi publisher: xdoxslt: array with variables

-> Define variables

<?xdoxslt:set_variable($_XDOCTX, 'PaymentTotalVar', 0)?>

<?xdoxslt:set_variable($_XDOCTX, 'ReceiptTotalVar', 0)?>

-> assign some value to variables

-> Using Arrays

setting arrays [key, value] key has to be a number

<?xdoxslt:set_array($_XDOCTX, ‘NetPay’, 1, xdoxslt:get_variable($_XDOCTX, 'ReceiptTotalVar'))?>

<?xdoxslt:set_array($_XDOCTX, ‘NetPay’, 2, xdoxslt:get_variable($_XDOCTX, 'PaymentTotalVar'))?>

getting values

<?xdoxslt:get_array($_XDOCTX, ‘NetPay’, 1)?>

<?xdoxslt:get_array($_XDOCTX, ‘NetPay’, 2)?>

-> Calculation
use in rtf tags [subtraction of values]

<?xdoxslt:get_array($_XDOCTX, ‘NetPay’, 2) -xdoxslt:get_array($_XDOCTX, ‘NetPay’, 1)?>

BI Publisher: setting xdoxslt based on condition

This will work under boilerplate text in rtf element

C1  EC C2  EC

<?xdoxslt:ifelse(TRANSACTION_TYPE='Available Receipts', 'Receipts', 'Payment')?>

<?xdoxslt:get_variable($_XDOCTX, 'ReceiptTotalVar')?>

<?xdoxslt:get_variable($_XDOCTX, 'PaymentTotalVar')?>

0.00

C1=>
<?if@inlines:TRANSACTION_TYPE='Available Receipts'?><?xdoxslt:set_variable($_XDOCTX, 'ReceiptTotalVar', sum(current-group()/ PAYMENT_RECEIPT_AMT))?>


EC=>
<?end if?>

C2=> <?if@inlines:TRANSACTION_TYPE='Available Payments'?><?xdoxslt:set_variable($_XDOCTX, 'PaymentTotalVar', sum(current-group()/ PAYMENT_RECEIPT_AMT))?>

Based on condition only variables will have values assigned.

0.00 =>

<?xdoxslt:get_variable($_XDOCTX, 'PaymentTotalVar')-xdoxslt:get_variable($_XDOCTX, 'ReceiptTotalVar')?>

BI Publisher: XDOXSLT

		Description	Example	Result
<?xdoxslt:abs(NUMBER)?>		Return absolute value of number	<?xdoxslt:abs(-123.45)?>	123.45
<?xdoxslt:current_date('DD-MM-YYYY')+1?>		Retrieve current time	<?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?>	2011-11-11
<?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?>			<?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?>	12:30:44 PM
<?xdoxslt:date_diff(RESULT, FROM_DATE, TO_DATE, $_XDOLOCALE, $_XDOTIMEZONE)?>		Determine difference between dates	<?xdoxslt:date_diff(‘m', ‘2010-07-30’, ‘2010-12-31’, $_XDOLOCALE, $_XDOTIMEZONE)?>	5
<?xdoxslt:foreach_number($_XDOCTX, 1, ITERATIONS, 1)?>		Used in conjunction with for-each loop	<?for-each:xdoxslt:foreach_number($_XDOCTX, 1, $iterations, 1)?>	-
		Format number	<?xdoxslt:format_number(12345, 2, $_XDOLOCALE)?>	12,456.00
		Format number with custom separators	<?xdoxslt:format_number(12345, 3, '.', 'x', $_XDOLOCALE)?>	12.456x000
<?xdoxslt:get_array($_XDOCTX, TABLE_NAME, INDEX)?>		Retrieve value from hash table	<?xdoxslt:get_array($_XDOCTX, ‘MyTable’, 2)?>	'Test'
<?xdoxslt:get_day(DATE, LOCALITY)?>		Get current day	<?xdoxslt:get_day(‘2010-07-29’, 'Australia/Brisbane')?>	29
<?xdoxslt:get_month(DATE, LOCALITY)?>		Get current month	<?xdoxslt:get_month(‘2010-07-29’, 'Australia/Brisbane')?>	7
<?xdoxslt:get_variable($_XDOCTX, VAR_NAME)?>		Get value	<?xdoxslt:get_variable($_XDOCTX, 'test')?>	'Hello'
<?xdoxslt:get_year(DATE, LOCALITY)?>		Get current year	<?xdoxslt:get_year(‘2010-07-29’, 'Australia/Brisbane')?>	2010
<?xdoxslt:ifelse(EXPRESSION, TRUE, FALSE)?>		Conditional Statement	<?xdoxslt:ifelse(a = b, 'Equivalent', 'Mismatch')?>	-
<?xdoxslt:init_cap(STRING)?>		Convert initial characters to capitals	<?xdoxslt:init_cap('this is a test')?>	This Is A Test
<?xdoxslt:left(STRING, LENGTH)?>		Extract beginning portion of string	<?xdoxslt:left(‘abcdefg’, 3)?>	'abc'
<?xdoxslt:ltrim(STRING)?>		Removes leading white space in string	<?xdoxslt:ltrim(‘ a ‘)?>	'a '
<?xdoxslt:maximum(ELEMENT_NAME)?>		Retrieve largest value from element	<?xdoxslt:maximum(Gross_Claims_ID24)?>	253456
<?xdoxslt:month_name(MONTH_NUM, ABBREVIATE, $_XDOLOCALE)?>		Retrieves name of specified month	<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>	'January'
<?xdoxslt:minimum(ELEMENT_NAME)?>		Retrieve smallest value from element	<?xdoxslt:minimum(Gross_Claims_ID24)?>	-
<?xdoxslt:next_element(GROUP, ., ELEMENT_NAME)?>		Retrieves subsequent element in group	<?xdoxslt:next_element(current-group(), ., Pay_ID21)?>
<?xdoxslt:pat_format_number(NUMBER, FORMAT, $_XDOLOCALE)?>		Format number with pattern	<?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?>	12,345.00
<?xdoxslt:prev_element(GROUP, .., ELEMENT_NAME)?>		Retrieves previous element in group	<?xdoxslt:prev_element(current-group(), .., Pay_ID21)?>
<?xdoxslt:replace(STRING, OLD_CHAR, NEW_CHAR)?>		Replace consecutive characters	<?xdoxslt:replace(‘Mist’, ‘Mi’, ‘Te’)?>	Test
<?xdoxslt:right(STRING, LENGTH)?>		Replicate string	<?xdoxslt:replicate(‘String’, 3)?>
		Extract end portion of string	<?xdoxslt:right(‘abcdefg’, 3)?>	'StringStringString'
<?xdoxslt:round(NUMBER [, DECIMALS])?>		Round number	<?xdoxslt:round(4.56 , 0)?>	5
<?xdoxslt:rtrim(STRING)?>		Removes trailing white space in string	<?xdoxslt:rtrim(‘ a ‘)?>	' a'
<?xdoxslt:sec_diff(FROM_TIME, TO_TIME, $_XDOLOCALE, $_XDOTIMEZONE)?>		Determine difference between times	<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE)?>	3600
<?xdoxslt:set_array($_XDOCTX, TABLE_NAME, INDEX, STORE_VAL)?>		Truncate number to specified decimals	<?xdoxslt:set_array($_XDOCTX, ‘MyTable’, 2, ‘Test’)?>	-
<?xdoxslt:set_variable($_XDOCTX, VAR_NAME, VALUE)?>			<?xdoxslt:set_variable($_XDOCTX, 'test', "'Hello'")?>	-
<?xdoxslt:truncate(NUMBER [, DEC_INT])?>			<?xdoxslt:truncate(2.6789, 2)?>	2.67
<?xdoxslt:trim(STRING)?>		Removes white space in string	<?xdoxslt:trim(‘ A ‘)?>	'A'