<?format-date:ASSIGNMENT_POSTING_DATE;’XDODEFDATE’?>
<?format-number:BASE_SALARY_NEW;’D999'?>
<?format-date: ACCT_DATE;’YYYY/MM/DD’?>
<?format-date:xdoxslt:sysdate_as_xsdformat();’DD MON YYYY’?>
<?format-date:<?format-date:ASSIGNMENT_START_DATE?;'DD-MON-YYYY'?>;'YYYY-MM-DD'?>
<?format-date:ASSIGNMENT_START_DATE;'DD-MON-YYYY'?>
<?format-date:PERIOD_START_DATE;'XDODEFDATE'?>
<?xdofx:to_date(COMP_1_COMPENSATION_EFF_DATE,'Mon YYYY' )?>
<?format-number:BASE_SALARY_NEW;’D99'?>
1. Create one lookup table and enter lookup_type column as per need . For example below.
select LOOKUP_VALUE, lookup_code
from XXXX_APPLICATION_LOOKUP
where lookup_type= 'OFFERINGS'
2. Select column which is based on above "OFFERINGS", select type as "LOV"
3. Go to Next Column which is based on previous column , Step 2. (output of step2)
4. Put Value in any "ATTRIBUTES" say ATTRIBUTE1 in XXXX_APPLICATION_LOOKUP.
That value is based on Master value and will act as Mapping
Say : OFFERING is ERP
and MODULE is Finance.
so put value in "LOOKUP_TYPE" as "MODULE" and put
Lookup_value as "Finance"
and in ATTRIBUTE1 as "ERP"
it will create as MAPPING between two datas.
put below query in target LOV (or Child LOV)
where P9_REGION in below query is Name of Master LOV
Query :
select lookup_value , lookup_code
from XXXX_APPLICATION_LOOKUP
where lookup_type= 'COUNTRY'
and attribute1 in (select regexp_substr (
:P9_REGION,
'[^:]+',
1,
level
) value
from dual
connect by level <=
length ( :P9_REGION ) - length ( replace ( :P9_REGION, ':' ) ) + 1)
1. Create New Application and its respective page.
2. In the "Region" Create Type as "Card" and add below query .
The more the unions, more the TILES.
In below Query :
"'f?p=&APP_ID.:33:&SESSION.::&DEBUG.:::' target_url"
33 is page number where your TILE will navigate
Query :
select
-- data
1 card_primary_key, -- primary key
null card_secondary_key, -- secondary key if needed
'Manage Projects' card_title, -- title
null card_subtitle, -- subtitle
'View Assigned Projects' card_body, -- card body text
null card_secondary_body, -- card secondary text, positioned near bottom
'fa fa-plus-square' card_icon , -- icon class, e.g. fa-cloud
'f?p=&APP_ID.:33:&SESSION.::&DEBUG.:::' target_url
from dual
union
select
-- data
2 card_primary_key, -- primary key
null card_secondary_key, -- secondary key if needed
'Project Access Request' card_title, -- title
null card_subtitle, -- subtitle
'Submit Project Access Request' card_body, -- card body text
null card_secondary_body, -- card secondary text, positioned near bottom
'fa fa-bar-chart' card_icon , -- icon class, e.g. fa-cloud
'f?p=&APP_ID.:17:&SESSION.::&DEBUG.:::' target_url
from dual
<?xdofx:to_check_number(SALARY_AMOUNT,’JPY’,’CASE_INIT_CAP’)?>
<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>
Attribute Description Valid Value amount
The number to be transformed.
Any number
precisionOrCurrency
For this attribute you can specify either the precision, which is the number of digits after the decimal point; or the currency code, which governs the number of digits after the decimal point. The currency code does not generate a currency symbol in the output.
An integer, such as 2; or a currency code, such as 'USD'.
caseType
The case type of the output.
Valid values are: 'CASE_UPPER', 'CASE_LOWER', 'CASE_INIT_CAP'
decimalStyle
Output type of the decimal fraction area.
Valid values are: 'DECIMAL_STYLE_FRACTION1', 'DECIMAL_STYLE_FRACTION2', 'DECIMAL_STYLE_WORD'
The following table displays the example function as entered in an RTF template and the returned output.
RTF Template Entry Returned Output <?xdofx:to_check_number(12345.67, 2)?>
Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345.67, 'USD')?>
Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345, 'JPY', 'CASE_UPPER')?>
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
<?xdofx:to_check_number(12345.67, 'EUR', 'CASE_LOWER', 'DECIMAL_STYLE_WORDS')?>
twelve thousand three hundred forty-five and sixty-seven
select distinct aia.invoice_id,
aia.INVOICE_CURRENCY_CODE "Functional Currency"
,(select distinct name from xle_entity_profiles
where legal_entity_id in ( select default_legal_context_id from hr_operating_units
where organization_id=aia.org_id))company
,aila.tax_classification_Code "Tax Code"
,aia.invoice_num "Transaction Number"
,aia.invoice_type_lookup_code "Transaction Class"
,'' Related_Transaction
,aia.invoice_date "Transaction_Date"
,(Select hp.party_name from hz_parties hp where aia.party_id = hp.party_id and rownum=1) "Billing Partner Name"
,zl.TAX_REGISTRATION_NUMBER
,aila.Amount "Taxable_Amt"
,zl.Tax_Amt
,zl.Tax_regime_code
,zl.tax
,(
Select distinct gcc.segment4
from gl_code_combinations gcc
where 1=1
and gcc.code_combination_id=aida.DIST_CODE_COMBINATION_ID
and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER
and aida.LINE_TYPE_LOOKUP_CODE in ('ITEM')
and aida.invoice_id=aila.invoice_id
and aila.tax_classification_code is not null
)"Line GL Account"
,(
Select distinct gcc.segment3
from gl_code_combinations gcc
where 1=1
and gcc.code_combination_id=aida.DIST_CODE_COMBINATION_ID
and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER
and aida.LINE_TYPE_LOOKUP_CODE in ('ITEM')
and aida.invoice_id=aila.invoice_id
and aila.tax_classification_code is not null
)"Line Cost Centre"
,(
Select distinct gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8
from gl_code_combinations gcc
where 1=1
and gcc.code_combination_id=aida.DIST_CODE_COMBINATION_ID
and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER
and aida.LINE_TYPE_LOOKUP_CODE in ('ITEM')
and aida.invoice_id=aila.invoice_id
and aila.tax_classification_code is not null
)"Line Account Combination"
,(select distinct gcc.segment4 from gl_code_combinations gcc where code_combination_id IN
(SELECT tax_account_ccid
FROM zx_rates_b RB, ZX_ACCOUNTS TA
WHERE TA.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
and TA.TAX_ACCOUNT_ENTITY_ID = RB.tax_rate_id
and rb.tax_rate_code=aila.tax_classification_Code
and ta.ledger_id=zl.ledger_id
and rb.ACTIVE_FLAG='Y')
) "Tax GL Account"
,(select distinct gcc.segment3 from gl_code_combinations gcc where code_combination_id IN
(SELECT tax_account_ccid
FROM zx_rates_b RB, ZX_ACCOUNTS TA
WHERE TA.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
and TA.TAX_ACCOUNT_ENTITY_ID = RB.tax_rate_id
and rb.tax_rate_code=aila.tax_classification_Code
and ta.ledger_id=zl.ledger_id
and rb.ACTIVE_FLAG='Y')
) "Tax Cost Centre"
,(select distinct gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 from gl_code_combinations gcc where code_combination_id IN
(SELECT tax_account_ccid
FROM zx_rates_b RB, ZX_ACCOUNTS TA
WHERE TA.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
and TA.TAX_ACCOUNT_ENTITY_ID = RB.tax_rate_id
and rb.tax_rate_code=aila.tax_classification_Code
and ta.ledger_id=zl.ledger_id
and rb.ACTIVE_FLAG='Y')
) "Tax Account Combination"
from ap_invoices_all aia
,ap_invoice_lines_all aila
,ZX_LINES zl
,xla_transaction_entities xte
,xla_Ae_headers xah
,xla_events xe
,ap_invoice_distributions_all aida
--,xla_ae_lines xal
where 1=1
--and aila.tax_classification_code is not null
--and aia.invoice_num in ('Test tax invoice','Test9879','Test9878','Test098')
--and aia.invoice_num in ('Tax Test Jun-24')
And aia.invoice_num=zl.trx_number
and zl.trx_id = aila.invoice_id
and zl.trx_line_id = aila.line_number
and zl.entity_code ='AP_INVOICES'
and aia.invoice_id=aila.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
and upper(aia.approval_status) IN ('APPROVED','CANCELLED')
and aia.invoice_id = xte.source_id_int_1
and xah.gl_transfer_status_code = 'Y'
and xte.LEDGER_ID =aia.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE='AP_INVOICES'
AND xe.ENTITY_ID =XTE.ENTITY_ID
AND xe.EVENT_ID =xah.EVENT_ID
--and xal.ae_header_id=xah.ae_header_id
and xah.entity_id = xte.entity_id
and aia.org_id = :p_Business_unit
and aida.accounting_date between :p_from_date and :p_to_date
