Wednesday 28 February 2018

How to Manage Auto Invoice Import Interface from Fusion application


How to Manage Auto Invoice Import Interface from Fusion application

 
Hi friends, in this post , we will discuss Auto Invoice interface in Oracle Fusion. In fusion , oracle has given functionality to download all the records available in AR Auto Invoice interface tables in an excel file and make them changes or corrections in these records from this excel and then process these records again directly from the downloaded Excel File.
 
Step1:- :- Navigate to Mange AutoInvoice Lines.
Navigator-->Receivables>Billing-->Tasks/ Transactions/ Manage AutoInvoice Lines
 
How to Manage Auto Invoice Import Interface from Fusion application
 
Step2:-This below Template will be download in your system.
Enter the Required Details in the template as per the requirement.
One you fixed the Search Criteria then Click on Search Button as below.
 
Search Button will fetch all the records from AR interface to this Excel Sheet as per your Search Criteria.
 
You can do the changes in the records directly in this Excel Template and you can also see the error reasons for the rejected records. You can either delete these records from this template by click on Clear.
How to Manage Auto Invoice Import Interface from Fusion application
 
 

 
Step3:- Submit the Final changes again in AR Interface to import in Fusion Receivables.
 
You can see the Manage Auto Invoice Tab in your Excel Template.
click on Submit button as below to process the changed records in AR.
 
How to Manage Auto Invoice Import Interface from Fusion application
 
 
 
 
How to Manage Auto Invoice Import Interface from Fusion application

Fusion Receivables : How to create AR Transaction in Fusion


Fusion Receivables : How to create AR Transaction in Fusion

 
Here we will discuss , how we can create AR Transaction in Fusion Receivables. I will share you the complete steps in that.
 
Step1:- Navigate to the Create Transaction.
Navigator-->Receivables>Billing-->Tasks/ Transactions/ Create Transaction
 
Fusion Receivables : How to create AR Transaction in Fusion
 
Step2:-In the general information section of the window, select the Transaction Class and the Business Unit for the transaction
 
In the General Information Section entered the below mandatory Information. 
Transaction Source, Transaction Type ,Transaction Date, Accounting Date and Functional Currency details can be entered
 
Enter a customer in the Ship To (optional) and Bill To fields on the Customer tab and Location.  The Ship To and Bill To sites will default.
 

Choose the Payment Terms.

The Payment due date will be calculated and appears beside the Payment Terms drop-down menu.
 
Fusion Receivables : How to create AR Transaction in Fusion
 
 
Step3:-Navigate to the line items section of the window and enter Description or a Memo Line, Quantity and Price. 
 
Fusion Receivables : How to create AR Transaction in Fusion
 
Once Save the Transactions , "Actions" button will get on the invoice Screen and Transaction Number.
 
 

Step4:- To review/ modify the distributions

To review/ modify the distributions, click actions and select edit distributions.
Actions/ Edit Distribution
Fusion Receivables : How to create AR Transaction in Fusion
 
 
 
Step5:- Complete transaction Click on Complete and Review as below.
 
Fusion Receivables : How to create AR Transaction in Fusion
 
 
Step6:- Create Accounting for the Invoice . This will be active once you will save the Transactions complete information.
 


Fusion Receivables : How to create AR Transaction in Fusion
 
 
Fusion Receivables : How to create AR Transaction in Fusion

File based data import for oracle financials cloud guide: How To Upload Customers Through Spread Sheet in Oracle Fusion


File based data import for oracle financials cloud guide: How To Upload Customers Through Spread Sheet in Oracle Fusion

 
Hi Friends , in this post we will discuss about how we can upload customers in Fusion through Spread Sheet. I have already shared customer import through Oracle Fusion File Baes Import but this is some thing different from FBDI.
 
 
Step1:-
Navigate to the Manage Customer Upload page
Navigator >  Receivables > Billing
 
 
Oracle Fusion File Based Import
 
 
 
Step2:- From Tasks List: Customer > Upload Customers from Spreadsheet
Oracle Fusion File Based Import
 

Step3:- Download Customers Spreadsheet Template

 Manage Customer Uploads Page appears
Click on 'Download Customer Spreadsheet Template'
 
Oracle Fusion File Based Import

 
 
Step4:- Enter the Customer Information  in the Template
- The spreadsheet template is composed of four worksheets: Customers, Contacts, Reference  Accounts and Customer Bank Accounts, where you can enter:
- customer accounts, sites and business purposes
- customer site receipt methods
- customer account and site contacts
- customer bill-to site reference accounts
- customer bank accounts
Oracle Fusion File Based Import

 

Step5:- Generate the CSV Zip File

Once the data maintained in the Excel Template,
In the ‘Instructions’ tab, click the Generate CSV File button
Save the Zip in your system
 

Step6:- Upload Customers From Spread Sheet

 
How To Upload Customers Through Spread Sheet in Oracle Fusion
 
 
Step7:- Upload Customer form Spreadsheet

 From Spreadsheet dialog window, enter a Batch Name for identification purposes.
Use the Browse button to locate your Zip file.
Submit
 
How To Upload Customers Through Spread Sheet in Oracle Fusion
 
 
Monitor progress of the Batch ID in the Search Results region of the page.
 

Step8 :- Review  the Customer Records created

Navigator >  Receivables > Billing
From Tasks List: Customer > Manage Customers
 
 
 
Oracle Fusion File Based Import : How To Upload Customers Through Spread Sheet in Oracle Fusion

Tuesday 27 February 2018

Creating Customer in Oracle Fusion


Creating Customers in Oracle Fusion

Hi Friends, in this post we will discuss how to create Customers in Oracle Fusion. I will show you step by step navigations and steps to create Customer.
 
Creating Customers in Oracle Fusion
To create a customer, you must specify at least a customer name, Account Address Set, account site address and business purpose.
 
Step1:-
Log in Billing responsibility.  Navigate to the Create Customer window.
 
Navigator >  Receivables > Billing
 
Creating Customer in Oracle Fusion
Creating Customer in Oracle Fusion


Step2:- Enter Customer Name in the Organization Name field
The default Customer Type will be Organization. You can also change to Person.

In the Account Information section of the window, optionally enter and account description, account type and customer class.

 
 
Creating Customer in Oracle Fusion



Step3:-
Navigate to the Account Address area.  Select the  Account Address Set from the drop down list
Enter the site address information including Country, Address Line 1 – 3, City, State.


Creating Customer in Oracle Fusion

Step4:- Creating Bill to Ship To Sites

Scroll down to the Address Purposes section of the window.  Click the plus + sign to add address purpose 'Bill To' or 'Ship To'


Step5:-Click the plus + sign again to add a second address purpose 'Ship To' purpose and select the Bill To Site Address you just created.

Creating Customer in Oracle Fusion



Step6:- Now Save and Close your Details.


Step7:- Now next step is to Add or Assign customer Sites to Business Unit. For this you need to go back to the Task list again and select Manage Customers.
Enter customer name in the Organization Name filed click on search

Creating Customer in Oracle Fusion


Step8:- Assign Customer Site to a Business Unit 

Click on the Site Number at the end of the search result page       

Under Reference Accounts click on Create icon to add Business Unit to Customer Site.
 Enter the GL Accounts
(B) Save at the top of the page

Creating Customer in Oracle Fusion

Step9:- Defining Customer Banking Details

Click on payment details tab
Under Payment Instruments click on Bank Accounts tab.
You can either create a new one or use an Existing Bank Accounts.                              


Creating Customer in Oracle Fusion

 

Monday 26 February 2018

Part2 OTBI Reports in Oracle Fusion.How to Create OTBI Reports in Oracle Fusion

OTBI Reports in Oracle Fusion. How to Create OTBI Reports in Oracle Fusion

This is the second post of OTBI Reports in Oracle Fusion. How to Create OTBI Reports in Oracle Fusion. In this first post , I have shared you all the steps to create OTBI reports and now in this post we will start from create filters and where condition in OTBI reports.
 
I will recommend to go to the first post , If you want to start this second post.
 
 

How to Create OTBI Reports in Oracle Fusion.OTBI user guide

Step10:-

Creating Filters in OTBI report
 
Click on below icon under filters tab as below.
 
How to Create OTBI Reports in Oracle Fusion

 
 
 
 Step11:-

Oracle otbi documentation

 
Click on Filters and More columns as below.
How to Create OTBI Reports in Oracle Fusion

Step12:-

Oracle OTBI documentation

 
 You can put filters on any columns as per the columns available in the Payables Invoice subject area sub-folder. In this example I will put where condition on Business unit. So I will choose Business unit sub-folder as below.
 
 
How to Create OTBI Reports in Oracle Fusion.otbi training
How to Create OTBI Reports in Oracle Fusion
 

Step13:-

OTBI reports in oracle fusion

 
Now select the values as it will show you all the distinct value available in the data base for this column.
How to Create OTBI Reports in Oracle Fusion
 
Now you can see below OTBI reports filters as below.
 
How to Create OTBI Reports in Oracle Fusion
 
 
Step14:- Suppose , you want to show only top 10 invoices in your OTBI reports for this how you put condition in this OTBI report.
For that you have to click on Filters again , when you will click on filters first you will always see the columns of your OTBI reports , so now you want to see top 10 invoices amount do in this filter you will select Invoice amount column.
otbi reports in oracle fusion
 
Step15:- Select below condition for your Invoice Amount column.
 
otbi reports in oracle fusion
 
Step16:- Now your OTBI Reports filters will be two as below.
oracle otbi documentation
 
 
Step17:- Now click on Results tab to see the output.
 
Part2 OTBI Reports in Oracle Fusion.How to Create OTBI Reports in Oracle Fusion
 
 
 
 
 
 

OTBI Reports in Oracle Fusion. How to Create OTBI Reports in Oracle Fusion

Part 1:- How to Create OTBI Reports.OTBI reports tutorial

How to Create OTBI Reports. OTBI reports tutorial


In this post , we will discuss How to Create OTBI Reports in Oracle Fusion. OTBI is real time self-service bases reporting tool given in Oracle fusion. you can do more others things in this toll except reports. You can design graphical reports. you can create dashboards. You can create Business MIS reports with out writing any codes. The reason is because Oracle fusion has already given you pre-build subject areas/View to fetch the data from different -different modules in Oracle Fusion. You just to design the layout and do some final calculation for its final output.

This post is in two parts.

Part1 :- https://rpforacle.blogspot.com/2018/02/how-to-create-otbi-reports-otbi-reports-tutorial.html
Part2:- https://rpforacle.blogspot.com/2018/02/part2-otbi-reports-in-oracle-fusion-how-to-create-otbi-report-in-oracle-fusion.html

Complete Tax Setups in Oracle Fusion

 How to Create OTBI Reports in Oracle Fusion

Step1:-

Go to Oracle fusion Application and Then go to Navigator. Select Report and analytics.
How to Create OTBI Reports.OTBI reports tutorial
 

Step2:-

This below will appear after the Reports and Analytics as below.
How to Create OTBI Reports.OTBI reports tutorial
 

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.
 
 

Step3:-

Click on browse Catalog as below.
oracle fusion transactional business intelligence for financials
 

Step4:-

Now click on the Home Icon as below and this will navigate to Home Screen.
oracle fusion transactional business intelligence for financials
 
 

Step5:-

Now click on Analysis as below.
otbi training
 
 
Step6:-
In this example I am going to Create Analysis on Payables AP invoice data for that I will select Payables Invoices Subject Area.
 
oracle otbi documentation
 

Step7:-

This is the Development window of OTBI reports.

Selected columns:

'Selected columns' represents Columns which you want to include in your Report output.
 

Filters:

Filters represents where conditions in this OTBI report.
 
You will drop the Columns from the subject areas in the left side.
 
You can see in the left side subject area and the sub folders. Each subfolders has information related to AP invoices. Like Accounting folder have accounting information. Invoice Details folder has Invoice number , invoice description kind of information.
 
oracle otbi documentation
 
 
 
How to Create OTBI Reports in Oracle Fusion.oracle otbi documentation
 
 
Step8:- I have Drag Invoice Number and Invoice Amount from Subject area to the Selected Columns as below.
Then these are two columns will come in this report output.
How to Create OTBI Reports in Oracle Fusion.oracle otbi documentation
 
 

 Step9:- If you want to change the name of columns you can do by that way. click on the column Circle.
How to Create OTBI Reports in Oracle Fusion

Now we will create Filters in the OTBI reports.
 
Rest of the steps is in Second Post as below
 
 


 

How to Create OTBI Reports in Oracle Fusion



How to create OTBI Reports.OTBI REPORTS IN ORACLE FUSION

How to create OTBI Reports. OTBI REPORTS IN ORACLE FUSION

OTBI is a new reporting feature in Oracle Fusion. OTBI is a Oracle Transactional Business Intelligence reporting tool. Its provide you real time and Self-service reporting solution in Fusion. Self-Service means that , business users can design their own reports based on their needs. OTBI in oracle fusion gives business solution to create real time analyses that support decision-makings. In OTBI , fusion has already given the predefined views which fetch different -different data from Oracle Applications. Users just need to select the appropriate view/Subject area on which they wants to Design the OTBI reports.
Business users with appropriate roles can use Oracle Fusion Transactional Business Intelligence to create analyses that support decision-making. Business users can perform current-state analysis of their business applications using a variety of tools. These include Oracle Business Intelligence Enterprise Edition (Oracle BI EE) as the standard query and reporting tool, Oracle Business Intelligence Answers (Oracle BI Answers), and Oracle Business Intelligence Dashboard end-user tools.
I have already explained the OTBI Subject folder and My Folder in Details in my last post. You can refer these posts to get those details from Folder in OTBI.
 

OTBI REPORTS IN ORACLE FUSION DETAIL DECRIPTION

 

OTBI Reports Security:-

In this , we will discuss how we can implement security in OTBI reports which will prevent to run and create reports in Oracle fusion , as per the rights and responsibility of the business users. It means we can control the subject areas/views of OTBI and also the OTBI reports as per the responsibility levels and roles level in Fusion. We will discuss in details these security details.

Subject Area:-

 Subject areas are functionally secured using duty roles. The names of duty roles that grant access to subject areas include the words Transaction Analysis Duty (for example, Workforce Transaction Analysis Duty).

OTBI REPORTS IN ORACLE FUSION
OTBI REPORTS IN ORACLE FUSION
This table identifies the subject areas that predefined HCM job roles can access.

Job Role

Subject Areas
Payroll Manager
All Payroll

Business Intelligence Catalog Folders:-

Business Intelligence Catalog Folders secured like the same as like Subject areas. One can access these folders as per their duty roles.
A user who inherits the Payroll Manager Duty can access both the All Payroll folder in the Business Intelligence Catalog and the All Payroll  subject areas.

Business Intelligence Reports

Analyses are secured based on the folders in which they're stored. If you haven't secured Business Intelligence reports using the report privileges, then they're secured at the folder level by default. You can set permissions against folders and reports for Application Roles, Catalog Groups, or Users.
You can set permissions to:
  • Read, Execute, Write, or Delete
  • Change Permissions
  • Set Ownership
  • Run Publisher Report
  • Schedule Publisher Report
  • View Publisher Output

I have discussed Reports security in Details in my recent Post Security in OTBI



OTBI REPORTS IN ORACLE FUSION

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.
 
 

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

 
Step1:- Go to Your Report Output screen in Oracle BI and then go to Action as below
 
 
 
How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export
 
Step2:- Click on the Action Icon and select the 'Schedule' Option.
How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export
 Step3:- click on the Output Tab as below
How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export


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.


How to Send BIP Reports Output automatically to UCM Content server Without Doing File Import and Export
 
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.
 
 

Oracle Apps SQL Query to Fetch GST TDS Calculation Report

 

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



 

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *