SELECT DISTINCT
PROJECT_NAME_ALIAS,
USER_EMAIL,
REGEXP_SUBSTR(MODULE_NAME, '[^:]+', 1, LEVEL) AS MODULE_NAME,
LEVEL
FROM
XXUSER_PROJECTS_MODULES
WHERE
PROJECT_NAME_ALIAS like 'A%'
CONNECT BY
REGEXP_SUBSTR(MODULE_NAME, '[^:]+', 1, LEVEL) IS NOT NULL
AND PRIOR SYS_GUID() IS NOT NULL
select * from XXUSER_PROJECTS_MODULES
where project_name_alias like 'A%'
Hi Team,
If you have requirement where you want to display new page for each invoice then below section is for you.
Happy Learning.
<LIST_G_INVOICE>
<G_INVOICE>
<BILL_CUST_NAME>Vision, Incusive </BILL_CUST_NAME>
<TRX_NUMBER>234568</TRX_NUMBER>
...
</G_INVOICE>
<G_INVOICE>
<BILL_CUST_NAME>BCD </BILL_CUST_NAME>
<TRX_NUMBER>234</TRX_NUMBER>
...
</G_INVOICE>
...
</LIST_G_INVOICE>
...
Each G_INVOICE element contains an invoice for a potentially different customer. To instruct BI Publisher to start a new section for each occurrence of the G_INVOICE element, add the @section command to the opening for-each statement for the group, using the following syntax:
<?for-each@section:group name?>
where group_name is the name of the element for which you want to begin a new section.
For example, the for-each grouping statement for this example is as follows:
<?for-each@section:G_INVOICE?>
The closing <?end for-each?> tag is not changed.
The following figure shows a sample template for batch reports:
Note:
The G_INVOICE group for-each declaration is still within the body of the report, even though the headers are reset by the command.
The following table describes the values of the form fields from the template in the previous figure (that shows a sample template for batch reports):
| Default Text Entry | Form Field Help Text | Description |
|---|---|---|
for-each G_INVOICE |
| Begins the G_INVOICE group, and defines the element as a Section. For each occurrence of G_INVOICE, a new section is started. |
<?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)
<?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
Insert Table in the RTF template , Insert XML schema for columns.
For example :
Now click on individual cells and select TABLE PROPERTIES , click on Column and change the "Measure in:" : Percent
Above screenshot is for Column1 and then we can navigate to further columns.
See below as example :
Sometimes there are requirement where header and footer are changing and rest content/body of template is same.
For example there are multiple addresses for any parent company.
In that case we can create subtemplate and call that subtemplate in parent template.
Creating Subtemplate:
In RTF template , do below
<?template:MyAddressUS?> My Company 500 Main Street Any City, CA 98765 <?end template?>
?template:MyAddressIndia?> My Company 500 Main Street India <?end template?>
Now we can save that template in fusion, Go to oracle fusion
its extension will be .xsb
Now we need to call that template in our main template,wherever we want to call in the main templateCalling of Subtemplate from Main template:In the Main template enter the following import statements in the form field or directlyanywhere in the template.<?param@begin:ParameterName?>
For example:
<?param@begin:DeptName?>
Happy Learning .
Hi Team,
First create Data mode and report with parameters.
Let suppose 3 parameters
Please make sure that order of ESS Job parameters and BIP Data model should be same.
In ESS Job , it should be
Business Unit Id
From Date
To Date.
Hi Team ,
When you are passing date parameter from ESS Job to BIP report then it is not fetching data due to wrong parameters.
When opening XML it is showing
Solution: Make the date format in SQL BIP data model as yyyy-MM-dd
Thanks and Happy Reading.
Excel cannot recognize canonical date format. If the date format in the XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply a function to display it properly.
One option to display a date is to use the Excel REPLACE and SUBSTITUTE functions. This option retains the full date and timestamp. If you only require the date portion in the data (YYY-MM-DD), then another option is to use the DATEVALUE function. The following example shows how to use both options.
Example: Formatting a Canonical Date in Excel
Using the Employee by Department template and data from the first example, this procedure adds the HIRE_DATE element to the layout and displays the date as shown in Column E of Figure 6-18.
Figure 6-18 The Employee by Department Template Showing the Hire Date
To format the date:
Add a column to the table in your layout for HIRE_DATE.
In the table row where the data is to display, use the Template Builder to insert the HIRE_DATE field.
Note:
If you are not using the Template Builder, copy and paste a sample value for HIRE_DATE from the XML data into the cell that is to display the HIRE_DATE field. For example:
Copy and paste
1996-02-03T00:00:00.000-07:00
into the E8 cell.
Assign the cell the defined name XDO_?HIRE_DATE? to map it to the HIRE_DATE element in the data.
The inserted field is shown in Figure 6-19.
If you do nothing else, the HIRE_DATE value is displayed as shown. To format the date as "3-Feb-96", you must apply a function to that field and display the results in a new field.
Insert a new Hire Date column. This is now column F, as shown in Figure 6-20.
Figure 6-20 The New Hire Date Column in Column F
In the new Hire Date cell (F8), enter one of the following Excel functions:
To retain the full date and timestamp, enter:
=--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")
To retain only the date portion (YYY-MM-DD), enter:
=DATEVALUE(LEFT(E8,10))
Notice that in both functions, "E8" refers to the cell that contains the value to convert.
After you enter the function, it populates the F8 cell as shown in Figure 6-21.
Apply formatting to the cell.
Right-click the F8 cell. From the menu, select Format Cells. In the Format Cells dialog, select Date and the desired format, as shown in Figure 6-22.
Figure 6-22 Applying the Format for the Date in the Format Cells Dialog
The sample data in the F8 cell now displays as 3-Feb-96.
Hide the E column, so that report consumers do not see the canonical date that is converted.
Figure 6-23 shows the template with column E hidden.
Step1: First create BIP report with SQL query in BI publisher in oracle fusion.
Step2: Download XML data from BIP output.
Step3: Open Excel template
Step4: Upload XML data into excel template .
Navigation: Go to BI Publisher>Sample XML> Select XML file and click Ok.
Then Click on Field, it will be like as below
Step5: Right click on tab and select unhide
Run the excel report. It will work .
If User want to show in two different columns, Debit and credit columns then
Debit column will be :
<?if: CD_TRX_AMOUNT>=0?>
<?format-number(CD_TRX_AMOUNT,"### ,###.00")?>
<?end if?>
<?if: CD_TRX_AMOUNT<0?>
<?''?>
<?end if?>
We can use Parameter2 for sending mail in CC.
But if you want to give access to user for the same then create a lookup and define emails in "DESCRIPTION" of that lookup.
for example :
abc@gmail.com,def@gmail.com, cef@gmail.com
and call that lookup in BI bursting query .
It will work
(Select description from fnd_lookups where lookup_type='XXX EMAIL CC INV PRINT' and rownum=1) PARAMETER2,
Please follow the below steps and retry running the Print Receivables job after.
Please pass the following SQL Query in the Transaction Delivery Bursting > Attachment tab.
Attachment Repository = FA_UCM_PROVISIONED
SQL Query:
-----------------
SELECT del.customer_trx_id || '_' || del.payment_schedule_id AS "KEY",
doc.DM_VERSION_NUMBER AS "ATTACHMENT_ID"
FROM AR_BPA_DELIVERY_DETAILS del,
fnd_attached_documents att,
FND_DOCUMENTS_VL doc
WHERE att.entity_name = 'RA_CUSTOMER_TRX_ALL'
AND att.pk1_value = TO_CHAR(del.CUSTOMER_TRX_ID)
AND doc.DOCUMENT_ID =att.DOCUMENT_ID
AND att.Category_Name IN
(SELECT Lookup_Code
FROM Fnd_Lookups
WHERE LOOKUP_TYPE = 'AR_PRINT_ATTACHMENT_CATEGORIES'
AND ENABLED_FLAG = 'Y'
AND (TRUNC(SYSDATE) BETWEEN nvl(Start_Date_Active,TRUNC(SYSDATE)) AND nvl(End_Date_Active,TRUNC(SYSDATE)))
)
The Print Receivables Transactions process prints transaction attachments that satisfy the following criteria:
SELECT gcc.CODE_COMBINATION_ID, GCC.ATTRIBUTE_CATEGORY, gcc.segment1 seg1, gcc.segment2 seg2, gcc.segment3 seg3, gcc.segment4 seg4, gcc.segment5 seg5, &VSH_SELECT account FROM GL_CODE_COMBINATIONS GCC WHERE gcc.CHART_OF_ACCOUNTS_ID = 9090 AND &VSH_WHERE
&VSH_SELECT value will be null&VSH_WHERE value will be 1=1