SQL Query to get the Supplier Site Details - Oracle Fusion.

Here is the generic Supplier Site Details as per the FBDI template in the Oracle fusion. 

select

psv.vendor_name 

,pssam.vendor_site_id

, pssv.vendor_site_code 

,( select  bu_name from fun_all_business_units_v where bu_id=pssv.prc_bu_id) procurement_bu

, psv.vendor_name_alt 

, pssv.party_site_name 

, pssv.rfq_only_site_flag 

, pssv.purchasing_site_flag 

, pssv.pcard_site_flag 

, pssv.pay_site_flag 

, pssv.primary_pay_site_flag 

, pssam.fax_country_code 

, pssam.fax_area_code

, pssam.fax fax

, pssv.inactive_date

, pssv.customer_num 

, pssam.b2b_site_code 

, wcv.carrier_name  

, pssv.mode_of_transport 

, pssv.service_level 

, pssv.freight_terms_lookup_code 

, pssv.fob_lookup_code 

, pssv.terms_date_basis 

, pssv.pay_group_lookup_code 

, pssv.payment_priority 

, term.name 

, pssv.invoice_amount_limit 

, pssv.pay_date_basis_lookup_code 

, pssam.bank_charge_deduction_type 

, pssv.always_take_disc_flag 

, pssv.invoice_currency_code 

, pssv.payment_currency_code 

, pssv.hold_all_payments_flag 

, pssv.hold_future_payments_flag 

, pssv.hold_unmatched_invoices_flag 

, pssv.hold_reason 

, pssv.hold_by 

, pssv.payment_hold_date

, pssv.hold_flag 

, pssv.purchasing_hold_reason

, pssv.auto_calculate_interest_flag

, pssv.tax_reporting_site_flag 

, pssv.exclude_freight_from_discount

, pssv.pay_on_code

, pssam.default_pay_site_id

, pssv.pay_on_receipt_summary_code

, pssv.match_option 

, pssv.country_of_origin_code 

, pssv.pay_on_use_flag 

, pssv.aging_onset_point

, pssv.aging_period_days

, pssv.consumption_advice_frequency

, pssv.consumption_advice_summary

, pssv.create_debit_memo_flag

, pssv.supplier_notif_method

, pssv.email_address

, att.tolerance_name quantitytolerances

, at.tolerance_name amount_tolerance

, pssv.gapless_inv_num_flag

, pssv.selling_company_identifier 

, iepa.bank_charge_bearer 

, iepa.bank_instruction1_code

, iepa.bank_instruction2_code

, iepa.bank_instruction_details

, iepa.payment_reason_code

, iepa.payment_reason_comments

, iepa.delivery_channel_code

, iepa.settlement_priority

, iepa. payment_text_message1

, iepa.payment_text_message2

, iepa.payment_text_message3

, ieppm.payment_method_code

, pssv.allow_substitute_receipts_flag

, pssv.allow_unordered_receipts_flag 

, pssv.enforce_ship_to_location_code 

, pssv.qty_rcv_exception_code 

, pssv.receipt_days_exception_code 

, pssv.days_early_receipt_allowed

, pssv.days_late_receipt_allowed

, decode (pssv.receipt_required_flag,

              'y', decode(pssv.inspection_required_flag,

                          'y', '4-way',

                          'n', '3-way'),

                          'n','2-way',

                          null  

              )  matchapprovallevel

, pssam.exclude_tax_from_discount

, pssv.receiving_routing_id

, pssam.vat_code

, pssam.vat_registration_num

, iepa.remit_advice_delivery_method

, iepa. remit_advice_email 

from poz_suppliers_v psv,

poz_supplier_sites_v  pssv,

poz_supplier_sites_all_m pssam,

ap_tolerance_templates att,

ap_tolerance_templates at,

iby_external_payees_all iepa,

iby_ext_party_pmt_mthds ieppm,

wsh_carriers_v wcv,

ap_terms term,

fun_all_business_units_v fabv 

where pssv.vendor_id=psv.vendor_id

and pssam.vendor_site_id=pssv.vendor_site_id

and term.term_id(+) = pssv.terms_id

and pssam.vendor_site_id(+)= iepa.supplier_site_id

and att.tolerance_id(+) = pssam.tolerance_id

and at.tolerance_id (+) = pssam.services_tolerance_id

and ieppm.ext_pmt_party_id(+)=iepa.ext_payee_id

and wcv.carrier_id(+) = pssam.carrier_id

and pssam.prc_bu_id = fabv.bu_id

SQL Query to get the Suppliers Details - Oracle fusion.

Here is the generic SQL Query to fetch the basic Suppliers Details as per the Oracle FBDI template.  

SELECT pos.vendor_name                                          

      ,NULL                                                     

      ,pos.segment1                                             

      ,pos.vendor_name_alt                                      

      ,ps.organization_type_lookup_code                         

      ,pos.vendor_type_lookup_code                              

      ,pos.end_date_active

      ,pos.business_relationship

      ,hp1.party_name                                           

      ,alias.party_name                                         

      ,pos.duns_number_c                                        

      ,ps.one_time_flag                                         

      ,ps.customer_num                                         

      ,ps.standard_industry_class                             

      ,pS.ni_number                                             

      ,pos.corporate_website

      ,hop.ceo_title chief_executive_title_chk

      ,hp.ceo_name  chief_executive_name_chk

      ,ps.bc_not_applicable_flag

      ,ps.taxpayer_country

  ,psi.income_tax_id taxpayer_id

      ,ps.federal_reportable_flag                               

      ,ps.type_1099                                             

      ,ps.state_reportable_flag                                 

      ,ps.tax_reporting_name

      ,ps.name_control

      ,TO_CHAR(ps.tax_verification_date,'YYYY/MM/DD')           

      ,ps.allow_awt_flag                                        

      ,(select ZW.name from ZX_WHT_TAX_CLASSIFICATION_V ZW

      where   ZW.group_id = pos.awt_group_id)               

      ,ps.vat_code

      ,(select ZT.REP_REGISTRATION_NUMBER  from ZX_PARTY_TAX_PROFILE ZT  

           where   ZT.party_id= ps.party_id)              

      ,ps.auto_tax_calc_override

      ,ibext.payment_method_code                                

      ,ibp.delivery_channel_code                             

      ,ibp.bank_instruction1_code                             

      ,ibp.bank_instruction2_code                             

      ,ibp.bank_instruction_details                             

      ,ibp.settlement_priority

      ,ibp.payment_text_message1

      ,ibp.payment_text_message2

      ,ibp.payment_text_message3

      ,ibp.bank_charge_bearer

      ,ibp.payment_reason_code                                 

      ,ibp.payment_reason_comments                             

      ,ibp.payment_format_code                                 

  FROM poz_suppliers ps,

       poz_suppliers_v pos,

       poz_supplier_registrations psr,

   poz_suppliers_pii psi,

       hz_organization_profiles hop,

       iby_external_payees_all ibp,

       iby_ext_party_pmt_mthds ibext,

       hz_parties HP,

       hz_parties HP1,

       (SELECT party_name

              ,party_id 

          FROM hz_addtnl_party_names hap2

         WHERE hap2.party_name_type='ALIAS') ALIAS 

 WHERE 1 = 1 

   AND ps.vendor_id                                 = pos.vendor_id

   AND ps.vendor_id                                 = psr.vendor_id(+)

   and ps.vendor_id                                 = psi.vendor_id(+)

   AND hp.party_id                                  = alias.party_id(+)

   AND hp1.party_id(+)                              = ps.parent_party_id

   AND hop.party_id(+)                              = ps.party_id

   AND pos.party_id                                 = hp.party_id(+)

   AND ps.party_id                                  = hp.party_id(+) 

   AND ps.party_id                                  = ibp.payee_party_id(+)

   AND pos.party_id                                 = ibp.payee_party_id(+)

   AND ibp.ext_payee_id                             = ibext.ext_pmt_party_id(+)

   AND hp.party_id                                  = ibp.payee_party_id(+)

   AND ibp.party_site_id is null

SQL Query to get the Supplier Basic info in Oracle fusion.

Here is the SQL Query to get the Supplier Basic information such as Supplier Name, Supplier Site, Vendor Site ID.

Use this query, modify by adding additional columns as required and extract the data.

SQL Query

SELECT psv.vendor_name        supplierName
      ,pssam.vendor_site_code supplierSite
      ,hps.party_site_id      HZ_Party_Site_ID
      ,pssam.vendor_site_id   vendor_site_id
  FROM poz_suppliers_v psv,
      poz_supplier_sites_all_m pssam,
      hz_party_sites HPS
WHERE pssam.vendor_id=psv.vendor_id
  AND hps.party_site_id = pssam.party_site_id

SQL Query to get the Purchase Order(PO) FBDI Import Error details.

Here is the quick SQL Query to get the Purchase Order(PO) FBDI Import Error details for all the PO's which are errored out.

This is the sample query which can be used. Please use and modify accordingly for your convenience.

SQL Query

SELECT
POIE.BATCH_ID,
POIE.INTERFACE_TYPE,
PHI.DOCUMENT_NUM,
PLI.LINE_NUM,
PLLI.SHIPMENT_NUM,
PDI.DISTRIBUTION_NUM,
PHI.vendor_name,
phi.vendor_site_code,
phi.vendor_id,
phi.vendor_site_id,
PDI.PROJECT,
PDI.TASK,
PDI.EXPENDITURE,
pdi.PJC_EXPENDITURE_ITEM_DATE,
PDI.EXPENDITURE_ORGANIZATION,
POIE.COLUMN_NAME,
POIE.COLUMN_VALUE,
POIE.ERROR_MESSAGE,
POIE.ERROR_MESSAGE_NAME,
POIE.TABLE_NAME,
POIE.CREATED_BY,
poie.request_id
FROM
PO_INTERFACE_ERRORS POIE,
PO_HEADERS_INTERFACE PHI,
PO_LINES_INTERFACE PLI,
PO_LINE_LOCATIONS_INTERFACE PLLI,
PO_DISTRIBUTIONS_INTERFACE PDI
WHERE POIE.INTERFACE_HEADER_ID = PHI.INTERFACE_HEADER_ID(+)
AND POIE.INTERFACE_LINE_ID = PLI.INTERFACE_LINE_ID(+)
AND POIE.INTERFACE_LINE_LOCATION_ID = PLLI.INTERFACE_LINE_LOCATION_ID(+)
AND POIE.INTERFACE_DISTRIBUTION_ID = PDI.INTERFACE_DISTRIBUTION_ID(+) 

SQL Query to get the Legal entity in Oracle fusion.

Here is the SQL Query to get the Legal entity in Oracle fusion.

SQL Query

SELECT *
FROM XLE_ENTITY_PROFILES
where TRUNC (SYSDATE) BETWEEN NVL ( EFFECTIVE_FROM,TRUNC (SYSDATE) - 1)

                           AND NVL (EFFECTIVE_TO,TRUNC (SYSDATE) + 1)

Import Program to import Purchase Orders in Oracle fusion.

Here is the quick post to import the Purchase orders using the FBDI process.


1. Log in to the application and click on navigator icon and select Scheduled Process

2. Click on Schedule New Process. Search and select Load Interface File for Import Program.

3. Search and select the import process Import Orders. Upload the zipped FBDI data files.

4.  Once the program is submitted, click on done and check the status of submitted process in Process window. Make sure the status or submitted process id must be completed along with all the child process.

5.Once the Load Interface File for Import Program is completed successfully, click on Schedule New Process. Search and select Import Orders program

Key in the below parameters as follows

           Procurement BU: - Select the Procurement BU for which Orders needs to be loaded.

         Default Buyer: - Select the Default Buyer.

         Approval Action: - Keep the approval action blank

         Batch ID: - Give the Batch ID that is used in the file. 

    Once done submit the program.

    6.    Once the program is complete, download the program execution report form the process window.

Rest API to delete the UnProcessed Projects Costs.

Here is the Rest API for the Oracle fusion to delete the UnProcessed Projects Costs.
The input for this Rest API is the Transaction Reference ID from the Projects Interface table "PJC_TXN_XFACE_ALL"

Method: DELETE

Path: /fscmRestApi/resources/11.13.18.05/unprocessedProjectCosts/{UnprocessedTransactionReferenceId}

Also, Please do refer the latest Oracle documents for the same.
https://docs.oracle.com/en/cloud/saas/project-portfolio-management/19c/fapap/api-unprocessed-project-costs.html

TaxPayer ID - Supplier Profile database table to find the Tax Payer ID - Oracle fusion.

Here is the table name in the Oracle fusion cloud application, where the TaxPayer ID gets stored.

                             POZ_SUPPLIERS_PII.NCOME_TAX_ID   ( TableName. ColumnName)

VAT Code and Tax Registration Number at the Supplier Site - Oracle Fusion FBDI

VAT Code and Tax Registration Number at the Supplier Site FBDI upload.

These two columns are dummy columns and are not uploaded through the Supplier Site FBDI.

Both Vat Code and Tax Registration Number are not supported functionality via the Supplier Site import process.  Please see below the Doc ID provided

Supplier Site Import Process Not Populating The Tax Registration Number (VAT Registration Number) ( Doc ID 2034667.1 )  

Cause : "Populating the Tax Registration Number via the Supplier Site import process is currently not supported functionality."

Query to find the Supplier Site Tolerances configuration setups in Oracle fusion

Here is the query to find the Supplier Site Service Tolerance and Quantity Tolerances configuration setups in Oracle fusion.

SQL Query

SELECT *
   FROM AP_TOLERANCE_TEMPLATES

Import program to Purge the PO Receipts interface table in Oracle fusion.

Here is the import program to purge the PO receipts interface table :


"Purge Receiving Interface"

These are the steps to be followed :

1. Go to Tools -> Scheduled Process

2. Click on Schedule New Process.

3. Search for : "Purge Receiving Interface"

4. Select OK from the Search results.

5. Select and provide the appropriate input parameters value for 

    a. Purge Data Before.

    b. Status.

6. Click on Submit.

Where exclusive_payment_flag of Supplier Site is stored

Here is the table name where the exclusive_payment_flag of the supplier site is stored :

iby_external_payees_all

SQL Query to fetch the Project Contracts Billing Cycle setup details in Oracle cloud.

Here is the SQL Query to fetch the Project Contracts Billing Cycle setup details in Oracle cloud.

SELECT 'Billing Cycle' "SETUP_NAME",
            pbc.billing_cycle_name VALUE
  FROM  pjf_billing_cycles_vl pbc
 WHERE TRUNC(SYSDATE) BETWEEN NVL (start_date_active,TRUNC(SYSDATE) - 1) AND  NVL (end_date_active, TRUNC(SYSDATE) + 1);

SQL Query to fetch the Project Billing Currency Type code in Oracle cloud fusion.

Here is the SQL Query to fetch the Project Billing Currency Type codes in Oracle cloud fusion.


SELECT 'Billing Currency Type Code' "SETUP_NAME",
               flvl.lookup_code  VALUE
  FROM   fnd_lookup_values_vl flvl
 WHERE lookup_type = 'PJB_BILLING_CURRENCY_TYPE'
     AND enabled_flag = 'Y'
    AND TRUNC(SYSDATE) BETWEEN NVL (start_date_active,TRUNC(SYSDATE) - 1) AND NVL (end_date_active, TRUNC(SYSDATE) + 1) ;

SQL Query to fetch the payment terms setups in Oracle cloud fusion

Here is the SQL Query to fetch the payment terms setups in Oracle cloud.


SELECT 'PAYMENT TERM' "SETUP_NAME",
          rt.name VALUE
  FROM ra_terms rt
 WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active, TRUNC(SYSDATE) - 1)  AND NVL (end_date_active, TRUNC(SYSDATE) + 1) ;

SQL Query to get the Contract Party Role setup details in Oracle cloud fusion Contracts

Here is the SQL Query to get the Contract Party Role Setup details in Oracle cloud fusion.

SELECT  'Party Role Code' "SETUP_NAME",
                 'PARTY_ROLE_CODE'  SETUP_NAME_CODE,
                 flvl.lookup_code  VALUE
  FROM fnd_lookup_values_vl flvl
 WHERE lookup_type = 'OKC_PARTY_ROLE'
      AND enabled_flag = 'Y'
     AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active,TRUNC(SYSDATE) - 1) AND NVL (end_date_active, TRUNC(SYSDATE) + 1) ;

SQL Query to get the Organization ID details in Oracle cloud fusion.

Here is the SQL Query to get the Organization ID in Oracle cloud fusion. This query can be tweaked accordingly for the further requirements.

SELECT 'Business Unit ID' "SETUP_NAME",
                haot.name  VALUE,
                 hao.organization_id "Value ID"
FROM HR_ALL_ORGANIZATION_UNITS_F hao,
            HR_ORGANIZATION_UNITS_F_TL haot,
            HR_ORG_UNIT_CLASSIFICATIONS_F houcf
WHERE hao.organization_id = haot.organization_id
     AND hao.effective_start_date = haot.effective_start_date
    AND hao.effective_end_date = haot.effective_end_date
    AND TRUNC(SYSDATE) BETWEEN hao.effective_start_date AND hao.EFFECTIVE_END_DATE
   AND houcf.organization_id = hao.organization_id
   AND houcf.classification_code = 'BUSINESS_UNIT'
   AND haot.LANGUAGE='US';

How to create a folder in Oracle BI EE 11.

Here is the post on creating a folder in Oracle BI EE 11. I know this is very simple task, but sometimes if you are a new to this of creating a BI report, then you will spend quite sometime to locate the tool and how to create the same.

Here are the steps provided to create a folder in Oracle BI cloud.

1. Login to the Oracle cloud applications.

2. Click on the Reports and Analytics.

3. Now once on this page, navigate and click on the Browse Catalog button.

4. Oracle BI page opens up and now you can see the catalog and other stuffs. On the Left hand side, we see Folders.

5. In order to create a new folder, there is tool bar section.

6. Click on "New" tool.

7. We see the new folder icon. Click on that tool. Now it pops up the New folder window to create. Enter the desired name and click on the Ok button.

This creates new Folder successfully.

Oracle Cloud - Manage File Import Activities

Here are the navigation steps to access the "Manage File Import activities" in Oracle cloud applications.

1. Login to Oracle cloud application.

2. Click on the user, settings and action.

3. Navigate to "Setup and Maintenance"

4.  In the Search Tasks text box, type"Manage" and enter.

5. Now, you will see the list of options.

6. Click on the "Manage File Import Activities" task. You will be directed to the Manage Import Activities page, where you can submit the the individual jobs.

STATS Collection Oracle SQL Query

Here is the Sample Oracle SQL Query for the STATS collection.

select t.last_analyzed, nvl(num_rows,0) num_rows, t.* from all_tables t where last_analyzed is not null and owner in (<Oracle_Schema_name>)

order by 1 desc;