Wednesday, 21 February 2018

How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export

 

How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export

Hi Friends , in this Post we will discuss , how we can send BIP reports output directly to UCM Content server to avoid or automate File Import and Export Process. In fusion , if we want to import any file then we need to import this file in fusion under UCM content server. But if you are creating this file thorough any BIP or OTBI report then you use this feature to send report output directly in UCM Content server in desired UCM account.
 
You have to create your Report first in Fusion and then in the output window of the report you need to do below changes in your report.
 
Step1:- Go to Your Report Output screen in Oracle BI and then go to Action as below
 
 
 
 
Step2:- Click on the Action Icon and select the 'Schedule' Option.
 Step3:- click on the Output Tab as below


Step4:- You have to Select Destination Type 'Content Server' in the output tab.
Server should be your UCM.
Security Group : Import and Export.
Account : Select the UCM account like fin/autoInvoice/import. Put that account in which you want to put this output file for import.
File Name:- You can put the name of the file so that your output will be saved by this file name.


 
These are complete steps to automate the process of File Import and Export through BIP reports.
 
How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export
 

Oracle Apps SQL Query to Fetch GST TDS Calculation Report

 

Oracle Apps SQL Query to Fetch GST TDS Calculation

 
Hi Friends, in this post , I will share you the Oracle Apps SQL Query to Fetch GST TDS Calculation. We all know that in India , GST has been implemented and due to which all the India Localizations tables has been changed due to which all the past Localization reports tables and reports has been obsoleted. I am sharing you the new GST TDS Calculation query to fetch the TDS details.
 
 

SQL Query: -

 
SELECT   ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
            hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
            pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
            MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')) gl_date,
            ap_inv_all.invoice_amount,
                               base_amt_tds tax_on,
            (SELECT ABS (SUM (aa.amount))
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) prepay_on,
            MAX (ap_ida.global_attribute1) tax_id, tax_tds.invoice_num tds_inv_no,
            tax_tds.gl_date tds_inv_gl_date,
            tax_tds.doc_sequence_value tds_voucher_no,
            tax_tds.invoice_amount tds_amount, tax_tds.base_amt_tds, jit.tax_name,
            jit.tax_rate, jath.pan_no, jit.section_code,
            jath.tds_vendor_type_lookup_code, jit.section_type
       FROM APPS.ap_invoices_all ap_inv_all,
            APPS.ap_invoice_distributions_all ap_ida,
            APPS.hz_parties hp,
            APPS.fnd_user fu,
            APPS.po_vendors pov,
            APPS.jai_cmn_taxes_all jit,
            APPS.jai_ap_tds_vendor_hdrs jath,
            (SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
                             ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
                             ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
                             jti.invoice_amount base_amt_tds
                        FROM APPS.jai_ap_tds_invoices jti, APPS.ap_invoices_all ap_inv_all
                       WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
                         AND ap_inv_all.attribute1 IS NOT NULL
                         AND vendor_id = '2'
            ) tax_tds
      WHERE 1 = 1
        AND ap_inv_all.invoice_id = ap_ida.invoice_id
        AND ap_ida.line_type_lookup_code != 'PREPAY'
        AND ap_ida.global_attribute1 IS NOT NULL
        AND ap_inv_all.org_id = ap_ida.org_id
        AND tax_tds.attribute1 = to_char(ap_inv_all.invoice_id)
        AND NVL (TO_CHAR (tax_tds.tds_tax_id), ap_ida.global_attribute1) =
                                                        ap_ida.global_attribute1
        AND pov.vendor_id = ap_inv_all.vendor_id
        AND fu.user_id = ap_inv_all.created_by
        AND pov.party_id = hp.party_id
        AND ap_inv_all.org_id = ap_ida.org_id
        AND ap_inv_all.cancelled_date IS NULL
        AND jath.vendor_site_id = ap_inv_all.vendor_site_id
        AND jit.tax_id = ap_ida.global_attribute1
        AND ap_ida.match_status_flag = 'A'
        AND UPPER (tax_tds.invoice_num) NOT LIKE '%RTN%'
   GROUP BY ap_inv_all.invoice_id,
            ap_inv_all.invoice_num,
            ap_inv_all.invoice_date,
            TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR'),
            ap_inv_all.invoice_amount,
            ap_ida.global_attribute1,
            tax_tds.invoice_num,
            tax_tds.gl_date,
            tax_tds.doc_sequence_value,
            tax_tds.invoice_amount,
            tax_tds.base_amt_tds,
            pov.vendor_name,
            hp.address1,
            hp.address2,
            hp.address3,
            hp.address4,
            hp.city,
            hp.postal_code,
            hp.state,
            pov.segment1,
            jit.tax_name,
            jit.tax_rate,
            jath.pan_no,
            jit.section_code,
            jath.tds_vendor_type_lookup_code,
            jit.section_type
            ap_inv_all.org_id
   UNION ALL
   SELECT   ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
            hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
            pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
            MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')) gl_date,
            ap_inv_all.invoice_amount,
                               base_amt_tds tax_on,
            (SELECT ABS (SUM (aa.amount))
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND aa.line_type_lookup_code = 'ITEM'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) prepay_on,
            MAX (ap_ida.global_attribute1) tax_id, tax_tds.invoice_num tds_inv_no,
           tax_tds.gl_date tds_inv_gl_date,
            tax_tds.doc_sequence_value tds_voucher_no,
            tax_tds.invoice_amount tds_amount, tax_tds.base_amt_tds, NULL tax_name,
            tax_tds.tax_rate, jpl.registration_number, tax_tds.section_code,
           (select REPORTING_CODE from  APPS.JAI_reporting_associations_v JRA,APPS.JAI_PARTY_REGS_V JPR
where REGIME_CODE in ('TDS','GST')
AND ENTITY_ID=PARTY_REG_ID
AND PARTY_ID=pov.vendor_id
AND ORG_ID=ap_inv_all.org_id
AND REPORTING_TYPE_NAME='Vendor Type'
AND EFFECTIVE_TO IS NULL
AND PARTY_SITE_ID=ap_inv_all.VENDOR_SITE_ID
AND ROWNUM=1)  tds_vendor_type_lookup_code, NULL section_code
       FROM APPS.ap_invoices_all ap_inv_all,
            APPS.ap_invoice_distributions_all ap_ida,
            APPS.hz_parties hp,
            APPS.fnd_user fu,
            APPS.po_vendors pov,
            APPS.jai_party_reg_lines jpl,
            APPS.jai_party_regs jpr,
            (SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
                             ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
                             ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
                             jti.invoice_amount base_amt_tds,
                             jti.invoice_id tds_invoice_id,
                             jtrl.tax_rate_percentage tax_rate,
                             jti.tds_section section_code
                        FROM APPS.jai_ap_tds_invoices jti,
                             APPS.ap_invoices_all ap_inv_all,
                             APPS.jai_tax_categories jtc,
                             APPS.jai_tax_category_lines jtl,
                             APPS.jai_tax_rates jtr,
                             APPS.jai_tax_rate_details jtrl
                       WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
                         AND jtc.tax_category_id = jtl.tax_category_id
                         AND jtc.tax_category_id = jti.tax_category_id
                         AND jtc.org_id = ap_inv_all.org_id
                         AND jtc.effective_to IS NULL
                         AND jtrl.effective_to IS NULL
                         AND jtrl.tax_rate_detail_code = 'RATE_DETAILS'
                         AND jtl.tax_rate_id = jtr.tax_rate_id
                         AND jtl.tax_type_id = jtr.tax_type_id
                         AND jtr.tax_rate_id = jtrl.tax_rate_id
                         AND vendor_id = '2'
            ) tax_tds
      WHERE 1 = 1
        AND ap_inv_all.invoice_id = ap_ida.invoice_id
        AND ap_ida.line_type_lookup_code != 'PREPAY'
        AND ap_inv_all.org_id = ap_ida.org_id
        AND tax_tds.tds_invoice_id = ap_inv_all.invoice_id
        AND pov.vendor_id = ap_inv_all.vendor_id
        AND fu.user_id = ap_inv_all.created_by
        AND pov.party_id = hp.party_id
        AND ap_inv_all.org_id = ap_ida.org_id
        AND ap_inv_all.cancelled_date IS NULL
        AND jpr.party_reg_id = jpl.party_reg_id
        AND ap_inv_all.org_id = jpr.org_id
        AND jpl.registration_type_code = 'PAN'
        AND jpl.effective_to IS NULL
        AND ap_inv_all.vendor_id = jpr.party_id
        AND ap_inv_all.vendor_site_id = jpr.party_site_id
        AND jpr.party_type_code = 'THIRD_PARTY_SITE'
        AND jpr.supplier_flag = 'Y'
        AND ap_ida.match_status_flag = 'A'
        AND UPPER (tax_tds.invoice_num) NOT LIKE '%RTN%'
   GROUP BY ap_inv_all.invoice_id,
            ap_inv_all.invoice_num,
            ap_inv_all.invoice_date,
            TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR'),
            ap_inv_all.invoice_amount,
            ap_ida.global_attribute1,
            tax_tds.invoice_num,
            tax_tds.gl_date,
            tax_tds.doc_sequence_value,
            tax_tds.invoice_amount,
            tax_tds.base_amt_tds,
            pov.vendor_name,
            hp.address1,
            hp.address2,
            hp.address3,
            hp.address4,
            hp.city,
            hp.postal_code,
            hp.state,
            pov.segment1,
            tax_tds.tax_rate,
            jpl.registration_number,
            tax_tds.section_code,
            ap_inv_all.org_id,
            pov.vendor_id,
            ap_inv_all.VENDOR_SITE_ID
   UNION ALL
   SELECT   ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
            hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
            pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
            MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')),
            ap_inv_all.invoice_amount,
            (SELECT SUM (aa.amount)
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code != 'PREPAY'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) tax_on,
            (SELECT ABS (SUM (aa.amount))
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) prepay_on,
            MAX (ap_ida.global_attribute2) tax_id, wct.invoice_num tds_inv_no,
            wct.gl_date tds_inv_gl_date,
            wct.doc_sequence_value tds_voucher_no,
            wct.invoice_amount tds_amount, wct.base_amt_tds, NULL tax_name,
            jit.tax_rate, jath.pan_no, jit.section_code,
            jath.tds_vendor_type_lookup_code, jit.section_type
       FROM APPS.ap_invoices_all ap_inv_all,
            APPS.ap_invoice_distributions_all ap_ida,
            APPS.hz_parties hp,
            APPS.fnd_user fu,
            APPS.po_vendors pov,
--            PO_VENDOR_SITES_ALL POVS,
            APPS.jai_cmn_taxes_all jit,
            APPS.jai_ap_tds_vendor_hdrs jath,
            (SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
                             ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
                             ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
                             jti.invoice_amount base_amt_tds
                        FROM APPS.jai_ap_tds_invoices jti,APPS.ap_invoices_all ap_inv_all
                       WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
                         AND ap_inv_all.attribute1 IS NOT NULL
                         AND vendor_id = '2'
            ) wct
      WHERE 1 = 1
        AND ap_inv_all.invoice_id = ap_ida.invoice_id
        AND ap_ida.line_type_lookup_code != 'PREPAY'
        AND ap_ida.global_attribute2 IS NOT NULL
        AND ap_inv_all.org_id = ap_ida.org_id
        AND wct.attribute1 = to_char(ap_inv_all.invoice_id)
        AND NVL (TO_CHAR (wct.tds_tax_id), ap_ida.global_attribute1) =
                                                        ap_ida.global_attribute1
        AND pov.vendor_id = ap_inv_all.vendor_id
        AND fu.user_id = ap_inv_all.created_by
        AND pov.party_id = hp.party_id
        AND ap_inv_all.org_id = ap_ida.org_id
        AND ap_inv_all.cancelled_date IS NULL
        AND jath.vendor_site_id = ap_inv_all.vendor_site_id
        AND jit.tax_id = ap_ida.global_attribute2
        AND ap_ida.match_status_flag = 'A'
        AND UPPER (wct.invoice_num) NOT LIKE '%RTN%'
   GROUP BY ap_inv_all.invoice_id,
            ap_inv_all.invoice_num,
            ap_inv_all.invoice_date,
            ap_inv_all.invoice_amount,
            ap_ida.global_attribute1,
            wct.invoice_num,
            wct.gl_date,
            wct.doc_sequence_value,
            wct.invoice_amount,
            wct.base_amt_tds,
            pov.vendor_name,
            hp.address1,
            hp.address2,
            hp.address3,
            hp.address4,
            hp.city,
            hp.postal_code,
            hp.state,
            pov.segment1,
            jit.tax_name,
            jit.tax_rate,
            jath.pan_no,
            jit.section_code,
            jath.tds_vendor_type_lookup_code,
            jit.section_type,
            ap_inv_all.org_id





Oracle Apps SQL Query to Fetch GST TDS Calculation
 
 
 
 

Tuesday, 20 February 2018

Oracle Fusion : SQL query to Fetch Key Flex Fields Segment Values and Description


SQL query to Fetch Key Flex Fields Segment Values and Description

 
Hi Friends, In this post I will share you the Tables and Sql Query of Key Flex Fields Segments value sets. In Fusion these value set tables has been totally changed as compared to Oracle r12. Please find below SQL query to Fetch Key Flex Fields Segment Values and Description
 
 

KFF Value Set Tables

 
1.FND_VS_VALUE_SETS
2.FND_VS_VALUES_B
 

Sql Query

 
 select *
from FND_VS_VALUE_SETS a1,FND_VS_VALUES_B a2
where a1.VALUE_SET_CODE=:KFF_SEGMENT_NAME
and a1.VALUE_SET_ID =a2.VALUE_SET_ID


SQL query to Fetch Key Flex Fields Segment Values and Description



 

What is Sandbox in Oracle Fusion

 

What is Sandbox in Oracle Fusion

 
Hi Friends ,in this Post we will discuss about Sandbox in Oracle Fusion. I am sure you have listened this word before in Oracle r12. In EBS Sandbox related to Server but in Oracle fusion Sandbox is totally different concept.
 
In Oracle Fusion , We will Use Sandbox to do any kind of Personalizations or Extensions or Page layout changes in Oracle Fusion Web-pages. It means we need to create Sandbox first to make any changes in Oracle Fusion application in terms of Personalizations or Extensions. Then all the changes will be done under this sandbox and this is the important feature of the sandbox that , we can do the changes in the application without impacting the complete application so these changes will only apply under the sandbox and other users of the application will not see these changes until unless you have tested the changes under sandbox and publish this sandbox. Once you will publish the sandbox in fusion then all the changes under the sandbox will be published to all the users in the application.
 
This is really an good feature , because typically we do Personalizations and extensions in  test environment and then after the testing we do the changes in Production but in sandbox feature , you can do the changes specific to the sandbox that will not impact to complete application and once you are completely sure about the changes then you can publish the changes under the sandbox.
 
Sandboxes allow customizers to make their changes in a segregated environment. Sandboxes keep the customization XML files stored in an MDS repository that is only available when you choose to work in that particular sandbox (this repository is separate from the repository that holds customizations).
 
You can also create a sandbox when you create security policies for custom objects that you have created using CRM Application Composer. These security sandboxes create new database tables to store the security information, and these tables are only available when you choose to work in that sandbox.

Once customizations in a sandbox are complete, the sandboxes can be reviewed and approved by others, and once approved, published to the full test environment where they become part of that repository

For flexfields, if you want to test the flexfield configuration before deploying it to the full test environment, you can deploy the flexfield to a flexfield sandbox. The changes that you deploy to a sandbox are isolated from the full test environment and can only be seen by those who make the flexfield sandbox active in their session. Once you are satisfied with the changes in the sandbox, you can deploy the changes to the full test environment.

Labeling in the Sandbox

When you publish the sandbox in the application then it is labeled in the application . It means it is save point of the application. So if in future you got some issues in the application due to Extensions or changes then you can go back to the last save point and roll back all the changes in the application.
 

Sandbox Settings -

You need to go top right side of the application under your user name.
 
What is Sandbox in Oracle Fusion
 
 
What is Sandbox in Oracle Fusion
 
 
 
 

If you want to know how to do the personalization under the sandbox , you can see Check this link Fusion Personalization


What is Sandbox in Oracle Fusion



 
 

Monday, 19 February 2018

Personalizations in Oracle Fusion

 

Personalization in Oracle Fusion

 
In friends , I this post we will discuss how we can do Personalizations in Oracle Fusion. I will show you the steps by which how we can make any field required in Oracle Fusion Pages. In this post , I am taking the Example of Payables Invoice where I will make Invoice Description Field Required.
 
Step1:- You need to create a Sandbox In Fusion for the personalization. Go to your user name in the right hand side of you Application home page and click on the user name.
 
click on Manage Sandboxes.
 
Personalizations in Oracle Fusion
 
Step2:-Create a Sandbox as below
 
Personalizations in Oracle Fusion
 
Step3:- This is the page where you can see Description is a Non- Required Field.
After Enabled the Sandbox again go back to the user name in the Top right hand side of the application and click on Customize Pages as below.
Personalizations in Oracle Fusion
 
Then This Message box will appear click on Edit.
 



Step4:- Now Click on the Structure Tab as below and click on the Description field.

Personalizations in Oracle Fusion


Step5:- After Click on The Invoice Description Field then at the bottom of the page you see below settings to change the properties of Description Field.
 

Personalizations in Oracle Fusion

 

Step6:- Then you can see below the Properties of the description field.

Personalizations in Oracle Fusion


Step7:- click on the Show Required as below and apply the changes as below.
Personalizations in Oracle Fusion
 
 

Now you can see below Description field is showing required in the Invoice Page.
Personalizations in Oracle Fusion
 
 
Personalizations in Oracle Fusion

How to Print MICR Font in Oracle Check Printing Report



How to Print MICR Font in Oracle Check Printing Report

 

Hi Friends, In this post , we will discuss how we can Print MICR Font in Oracle Check Printing Reports. When we see banks printed checks we can see in the bottom of the check where bank prints the check number and other unique identification number in the MICR font but if you are printing the Checks directly from Oracle means you are not using banks printed checks then you also need to print the same instructions in MICR font.
MICR code is a character-recognition technology used mainly by the banking industry to ease the processing and clearance of checks and other documents.
The MICR encoding, called the MICR Line, is at the bottom of checks and other vouchers and typically includes the document-type indicator, Bank code, Bank Account Number, check Number, check amount, and a control indicator.
 
How to Print MICR Font in Oracle Check Printing Report
In oracle, we can also print these MICR font directly in our check Printing Layouts without doing lot of efforts to install MICR font in Printers 
imply you need to do is, first you need to register the MICR and E-13b font in Oracle application and then you need to Create the Font Mapping but before this first you need to download the MICR and E-13B font file from google and then upload in Oracle application.
 
I will show the complete steps in detail as below.
Step1:- Go to XML Publisher Administration Responsibility and then go to Administration Tab and Then Font Files Tab.
Step2:- Create new Fonts for MICR and E_13b and attach the font file MICR.ttf and E-13b.ttf in these two fonts.
How to Print MICR Font in Oracle Check Printing Report
 
Step3:- Now Create Font Mapping for the Fonts which you have created in the Step2.
How to Print MICR Font in Oracle Check Printing Report
 
 
Step4:- Now go to your RTF Template and select the Text which you want to print in MICR Font and then Select the E-13B font.
 
How to Print MICR Font in Oracle Check Printing Report
 




How to Print MICR Font in Oracle Check Printing Report

Sunday, 18 February 2018

My Folders and Shared folders in Oracle Fusion Reports



My Folders and Shared folders in Oracle Fusion Reports

 
Hi Friends, in this post we will discuss about My Folders and Shared folders in Oracle Fusion Reports. We have two Folder options in Oracle Fusion to register and save our custom OTBI and BIP reports.
 

My Folders

 'My Folders' is your own folder. It means it is specific to each user. when you will create your report under this folder then no one in the application can see and access these reports because these are saved and created under your My folder. so this is totally yours personal Development.
 

Shared Folders

Shared Folders is your Application common Folder. It means it is shared across users of the application as per the roles. Shared folders has many Sub-folders related to the Work related and Module Related.
 
Oracle has already given standard OTBI reports ,Data Models and Dashboards under the sub-folders in the Shared Folders. When you will create any report under the sub-folder of Shared Folders then your report will be visible and accessible to others users of the application as per the Permission and role of that user.
 
 
My Folders and Shared folders in Oracle Fusion Reports
 
 
Subfolder under Shared folders 
My Folders and Shared folders in Oracle Fusion Reports
 

Custom Folder

 
Oracle fusion has given custom folder under the shared folders of Reports. The purpose of this Custom folder is that , if you want to create a report which will be visible to other user of the application then you have to create this report under the Shared Folders but in the Shared folder you will be create this report under the sub-folder related to your report like for AP invoice report you will be create under 'Financials' folder but as per the oracle Fusion practice , you should not to create the custom report under standard folder of shared folder because during upgrade or any patch impact your custom report could be removed so for this Fusion has provided you custom folder under this custom folder oracle fusion provided all the standard sub-folder. So report under the custom folder is the recommended method by Oracle.
If you want to know how to create fusion report you can Click Fusion Reports
 
 
My Folders and Shared folders in Oracle Fusion Reports
 
 
 
 

Thursday, 15 February 2018

How to Upload Data Through ADFDI in Oracle Fusion


How to Upload Data Through ADFDI in Oracle Fusion

 
Hi Friends, In this post we will discuss , how to Upload Data Through ADFDI in Oracle Fusion. Fusion has given FBDI and ADFDI tools to upload data in Oracle Fusion. FBDI mostly uses when we have large volume of data and to do automate Integration processes in Oracle fusion. On the other side, ADFI fits better , to those cases when you want to upload data from Excel sheets same like WEBDI in Oracle Apps r12. When you have small volume of Data then ADFDI is really a great option.
 
Second good thing in ADFDI is that , it provided real time interaction with Fusion Application. It means you select values from List of Values from the ADFDI Excel sheets. Like BU ,Supplier, Its Sites. So if you are entering any wrong information in ADFDI template which does not exists in Fusion Application , it will give error on the same time when you are entering this values in the ADFI template but this is not possible in FBDI. FBDI does not provide your selection and list validation from the Application. in FBDI you will get errors when you will trying to import data in fusion Base Tables.
 
Fusion has given you ADFDI for each important function of the Business like (Supplier, customer, AP Invoice ,AR Invoice, GL Journals, Fixed Assets, Purchase Orders and many more).
 
In this Example I will share you the Example of AP invoice through ADFDI.
 
Before using ADFDI, you have to do some settings in your system which you can find in my previous post.
 
Step1:- Go to Payables Roles in fusion.
 
How to Upload Data Through ADFDI in Oracle Fusion
 
Step2:- Go to the right side of the Task list and Select Create Invoice in Spreadsheet option and Save the Excel Worksheet in your Desktop.
 
 
How to Upload Data Through ADFDI in Oracle Fusion
 
Step3:- Open the Saved Excel Workbook and enter the Invoice Details as below.
 
How to Upload Data Through ADFDI in Oracle Fusion
 
 
 
Ste4:-  After Inserting Data in Excel Workbook click on create Invoices Tab as below and Submit the Invoice Import , your data will be start uploading in fusion application after that.



How to Upload Data Through ADFDI in Oracle Fusion




How to Upload Data Through ADFDI in Oracle Fusion
 

Excel and System Settings for ADFDI in Oracle Fusion


Excel and System Settings for ADFDI in Oracle Fusion

 
Hi Friends, In this post we will discuss Excel and System Settings for ADFDI in Oracle Fusion. In Fusion , we have ADFDI to upload data in Oracle application same like WEBADI in Oracle Apps12. In Fusion , we have no WEBADI but we have ADFDI to upload data in Oracle Fusion with the help of Excel Sheets.
 
But before using ADFDI , we need to do some settings in our system and in Excel.
 
Step1:- First we need to Download 'Desktop Integration Installer' from Oracle fusion.
Go to Navigator and Download the 'Desktop Integration Installer'  as below.
 
Excel and System Settings for ADFDI in Oracle Fusion
 
Step2:- After download the Desktop Integration , We need to do settings in our Excel settings.
Open an empty excel spreadsheet. Navigate to Excel Options.
 
 
Step3:- Navigate to Trust Center.
Excel and System Settings for ADFDI in Oracle Fusion
 
 
Step4:- 
Navigate to Active X settings and make sure it looks like the below.
 
 
Step5:-
Navigate to Macro Settings and make sure it looks like this.
 
Excel and System Settings for ADFDI in Oracle Fusion
 
 
Step6:- Check for Add-Ins
Navigate to Excel Options and click on Add-Ins and select Com Add-Ins and select Go.
Excel and System Settings for ADFDI in Oracle Fusion

 
 
Step7:-
Check If Oracle ADF 11g Desktop Integration is enabled. If not, please check it.
Excel and System Settings for ADFDI in Oracle Fusion
 
 
Step8:-
 
Internet Explorer Settings.
 
Make sure that your Application url should be add in the Trusted Sites.
 
 
Excel and System Settings for ADFDI in Oracle Fusion
 
 
 
 

Wednesday, 14 February 2018

How to Automate Data Import Process in Oracle fusion.


How to Automate Data Import Process in Oracle fusion.

 
In this post , We will discuss how we can Automate Data Import Process in Oracle fusion. In fusion , We have 'Load interface Files for Import' Schedules process by which we import data from Data Templates to Oracle Fusion. But in this program , we also need to select the Data file name which we have uploaded in Oracle fusion and this is the constraint because when we are doing automate then how can we put the file name manually in this program parameter.
 
So I am going to discuss here , how we can automate our data import in fusion.
 
suppose , you want to do integration of Oracle fusion ,with any third party tool , which creates your supplier information and this third party tool puts new supplier information file in oracle fusion FTP. Now , you want to automate the data import from this file to oracle fusion. but if you will run 'Load interface Files for Import'  then also you need to put the file name manually in this process. but if you are scheduling this program so that your data will be automatic move to oracle fusion then how can you select the file name every time in your scheduled program.
 
For this , Oracle fusion has provided another scheduled process 'Load Multiple Interface Files for Import' in which you don't need to put select the file in this process.
 
Here you can see below , I have selected Import process 'Import Suppliers' and File Prefix 'PozSuppliersInt' then after that click on Advanced button above to schedule this process.
 
How to Automate Data Import Process in Oracle fusion.
Schedule this program as below and now your Data Import process has been automate :)
 
 
How to Automate Data Import Process in Oracle fusion.
 


How to Automate Data Import Process in Oracle fusion.
 
 
 

How to do Integration with Oracle Fusion cloud


How to do Integration with Oracle Fusion cloud

 
In this post , We are going to Discuss how we can do Integration with Oracle Fusion cloud. Oracle has provided many tools in fusion , through which we can integrate with it. I am going to discuss integration tools in details.
 
 
1. ADFDI :- Oracle fusion has developed with Application Development Framework(ADF) and it has provided ADFDI tool means ADF Desktop Integration tool to do integration with fusion.
 
ADFDI is similar like WEBADI in Oracle Apps r12. It integrates with Oracle Fusion same like WEBADI in R12.
The integration provided with ADFDI includes interactivity that enables web picker to search for valid values, perform validation during data entry, display error messages, and immediately submit transactions directly from MS Excel.
 
This tool is very useful for Small value of Data uploading in Oracle Fusion. I will create post related to this topic in future.
 
2.FBDI :-
 FBDI Stands for File Based Data Import. This is a very good tool to do integration with Oracle fusion when you are dealing with High volume of Data. This FBDI is same as Data Import in Oracle Apps r12 through Interfaces. In FBDI , we download standard Oracle data Templates from Oracle Repository specific to the process like customer, supplier,item,ar invoice and then we put data in these templates and upload these data files in fusion and then run the standard import processes to move data in Fusion Standard interface Tables.
This process is very fast as compared to ADFDI and uses for implementations and to handle high volume of Data.
 
3.ADF Services :-
 A web service provides a standard way to integrate two web-based applications. A web service is a program that can be accessed remotely using different XML-based languages. These ADF services calls from Fusion middleware(SOA) to automate processes in Oracle fusion. Fusion has given many standard web-services to do automation in the integration process.


 How to do Integration with Oracle Fusion cloud

 
 
 

Tuesday, 13 February 2018

Supplier Banking Information SQL Query in Oracle Apps





Supplier Banking Information SQL Query in Oracle Apps



select VENDOR_NAME,vendor_site_code,IEBA.BANK_ACCOUNT_NUM,IEBA.IBAN,IEBA.BANK_ACCOUNT_NAME,cbv.BANK_NAME,cbv.ADDRESS_LINE1 BANK_ADDRESS_1,CBV.COUNTRY BANK_COUNTRY,
CBV.CITY BANK_CITY,
CBBV.BANK_BRANCH_NAME,CBBV.ADDRESS_LINE1 BRANCH_ADDRESS_1,
CBBV.CITY BRANCH_CITY,CBBV.COUNTRY BRANCH_COUNTRY,CBBV.BRANCH_NUMBER,CBBV.EFT_SWIFT_CODE BIC ,FOREIGN_PAYMENT_USE_FLAG  from apps.AP_SUPPLIERS APS,apps.AP_SUPPLIER_SITES_ALL ass,apps.IBY_EXTERNAL_PAYEES_ALL IEPA,
apps.IBY_PMT_INSTR_USES_ALL IPIUA,APPS.IBY_EXT_BANK_ACCOUNTS IEBA
,apps.ce_banks_v cbv, apps.ce_bank_BRANCHES_V CBBV
where aps.VENDOR_ID=ass.VENDOR_ID
and ass.ORG_ID=:P_ORG_ID
AND IEPA.PAYEE_PARTY_ID=APS.PARTY_ID
and nvl(iepa.PARTY_SITE_ID,supplier_SITE_ID)=nvl(ass.PARTY_SITE_ID,VENDOR_SITE_ID)
--and  iepa.PARTY_SITE_ID IS not NULL
AND IPIUA.EXT_PMT_PARTY_ID(+)=IEPA.EXT_PAYEE_ID
AND IEBA.EXT_BANK_ACCOUNT_ID(+)=IPIUA.INSTRUMENT_ID
AND IEBA.BANK_ID=cbv.BANK_PARTY_ID(+)
AND IEBA.BRANCH_ID=CBBV.BRANCH_PARTY_ID(+)
group by VENDOR_NAME,vendor_site_code, IEBA.BANK_ACCOUNT_NUM,IEBA.IBAN,IEBA.BANK_ACCOUNT_NAME,cbv.BANK_NAME,cbv.ADDRESS_LINE1,CBV.COUNTRY,
CBV.CITY ,
CBBV.BANK_BRANCH_NAME,CBBV.ADDRESS_LINE1 ,CBBV.CITY ,CBBV.COUNTRY ,CBBV.BRANCH_NUMBER,CBBV.EFT_SWIFT_CODE  ,FOREIGN_PAYMENT_USE_FLAG,
IEBA.ATTRIBUTE2,IEBA.ATTRIBUTE3,IEBA.ATTRIBUTE8,IEBA.ATTRIBUTE9,IEBA.ATTRIBUTE10

Friday, 9 February 2018

How to Create Dependent Table Value set in Oracle Fusion

 
 
Dependent Table Value set in Oracle Fusion:
 
 
Hi friends , we all know that in Oracle Apps R12 we creates dependent value set through $Flex command but in Fusion this thing has been changed now. Here we use some different syntax to create Dependent table value set.
 
 
If you need to validate the input against a list of values from an application table, then create a value set with the Table validation type. You define which table you want to use and you specify the column that contains the valid value. You can optionally specify the description and ID columns, a WHERE clause to limit the values to use for your set, and an ORDER BY clause.
 
1. :{SEGMENT.segment_code}  :- If you want to show the Table valueset value based on the Value selected in your Descriptive Flex Field Segment. Then you can use this System. Where in this syntax segment_code is the name of the DFF segment.
 
 
2.:{VALUESET.value_set_code} :- if you want to show the Table valueset value based on the value selected in other value set in your web-page. you can use this syntax.
 
3.:{FLEXFIELD.internal_code} :- This bind variable refers to an internal code of the flexfield in which the value set is used, or to a validation date.
 
The internal_code could be
 
APPLICATION_ID — the application ID of the flexfield in which this value set is used. The data type of APPLICATION_ID and its resulting bind value is NUMBER

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

DESCRIPTIVE_FLEXFIELD_CODE — the identifying code of the flexfield in which this value set is used. The data type of DESCRIPTIVE_FLEXFIELD_CODE and its resulting bind value is VARCHAR2. Note that you use this string for both descriptive and extensible flexfields.


CONTEXT_CODE — the context code of the flexfield context in which this value set is used. The data type of CONTEXT_CODE and its resulting bind value is VARCHAR2.


SEGMENT_CODE — the identifying code of the flexfield segment in which this value set is used. The data type of SEGMENT_CODE and its resulting bind value is VARCHAR2.
 
 


4:-:{PARAMETER.parameter_code}:- If you want to show your table value set values based on the value selected in the fields of your web-page backend table then you can use this syntax.
 
 
 
 
 

 

Thursday, 8 February 2018

Part2 : Customer Import in Oracle Fusion Through FBDI


Part2 : Customer Import in Oracle Fusion Through FBDI

This is the Seconds part of Customer Import in Oracle Fusion Through FBDI. If you want to see the first Part please go to the below url.

Part 1:- https://rpforacle.blogspot.in/2018/02/customer-import-in-oracle-fusion-through-fbdi.html


Step10:- Go to Oracle Fusion and go to Navigator and Click on File Import and Export as below

 
Step11:- Now Upload Customer Import Zip file as below and select the ucm account.
 
 

Step12:- Now Again go back to Navigator and click on Scheduled Processes.


Step13:- Select Load Interface File for Import ESS job as below.

 
Step14:- Enter the Parameters for Load Interface File for Import as below
Import process :- Import Trading Community Data.
 
 

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.


Step15:- Now Again back to Data Import Role as below



Step16 :- Now Select that Data Import Batch which you have created Initially in First Part of this Post.
and Then Click on Actions and Select Import as below.




Step17:-  This is last step of the Customer Data Import when you will click on Import action as above then after that either you can run this data import as a preview mode to see all the errors and make it correct or you can directly import that data by skipping preview option.



After that you can see the output report to check how many records got imported successfully and how many got failed.


If you want to see Other OAF Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2013/03/oaf_10.html


If you want to see  Oracle Fusion Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html
If you want to Learn Oracle Workflow Builder, Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html