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