Tables for the Projects Module in Oracle fusion

Here are the tables for the Project modules in Oracle fusion. This tables will store the details of the  Project information and related data attributes.

1. PJF_PROJECTS_ALL_VL

2. PJF_PROJ_ELEMENTS_B

3. PJF_PROJ_ELEMENTS_TL

4. PJF_PROJECT_CLASSES

5. PJF_CLASS_CODES_VL

6. PJF_CLASS_CATEGORIES_VL

7. PJF_EXP_TYPES_TL

8. PJF_EXP_CATEGORIES_TL

9. PJC_TRANSACTION_CONTROLS

10. PJF_PROJECT_PARTIES

11.PJT_PROJECT_RESOURCE

12.PJT_PRJ_ENTERPRISE_RESOURCE_VL

13. PJT_PROJECT_ROLES_TL

14. PJF_TASKS_V

15. PJC_TRANSACTION_CONTROLS

Other modules related tables list here :

Tables for the PO(Purchase Order) in Oracle fusion

Tables for the Items in Oracle fusion

Tables for the Sales Contracts in Oracle fusion

Tables for the AP Invoice in Oracle fusion

Tables for the PO(Purchase Order) in Oracle fusion

Here are the tables for the PO(Purchase Order) in Oracle fusion. This tables will store the details of the  Purchase Order(PO) information and related tables.

1. PO_HEADERS_ALL

2. PO_LINES_ALL

3. PO_LINE_LOCATIONS_ALL

4. PO_DISTRIBUTIONS_ALL

5. PJF_PROJECTS_ALL_VL

6. WSH_CARRIERS_V

7. EGP_SYSTEM_ITEMS_B

8. GL_CODE_COMBINATIONS

Other modules related tables list here :

Tables for the Projects Module in Oracle fusion

Tables for the Items in Oracle fusion

Tables for the Sales Contracts in Oracle fusion

Tables for the AP Invoice in Oracle fusion

Tables for the Items in Oracle fusion

Here are the Oracle tables for the Items in Oracle fusion. This tables will store the details of the  Items and related Item attribute details.

1. EGP_SYSTEM_ITEMS_VL

2. EGP_SYSTEM_ITEMS_B

3. EGP_ITEM_REVISIONS_B

4. EGP_SYSTEM_ITEMS_TL

5. EGP_ITEM_CLASSES_TL

6. INV_ORG_PARAMETERS

7. INV_ITEM_LOCATIONS

Other modules related tables list here :

Tables for the Projects Module in Oracle fusion

Tables for the PO(Purchase Order) in Oracle fusion

Tables for the Sales Contracts in Oracle fusion

Tables for the AP Invoice in Oracle fusion

Tables for the Sales Contracts in Oracle fusion

Here are the tables for the Contract in Oracle fusion. This tables will store the details of the  Contract information.

1. OKC_K_HEADERS_VL

2. OKC_K_HEADERS_ALL_B

3. OKC_K_HEADERS_TL

4. OKC_CONTRACT_TYPES_TL

5. OKC_CONTRACT_TYPES_B

Other modules related tables list here :

Tables for the Projects Module in Oracle fusion

Tables for the PO(Purchase Order) in Oracle fusion

Tables for the Items in Oracle fusion

Tables for the AP Invoice in Oracle fusion

Oracle SQL - Interview Questions

 In this post, we provide the list of Basic SQL Interview questions.

1.       What is the difference between Delete and Truncate command in SQL

2.       What does UNION do? What is the difference between UNION and UNION ALL

3.       Define a synonym, how many types there are, and explain the difference between them.

4.       What is an index? What are diff type of indices?

5.       What’s the advantage of using an index?

6.       Difference between Primary Key and Unique key?

7.       What are different Joins? Explain outer join? how would you write outer join

8.       How to find duplicate rows in a table and then write SQL query to delete them

9.       Write a SQL Query to find second highest salary of Employee

10.   What is difference between Cartesian Join and Cross Join?

11.   What are the default packages provided by oracle

12.   What is group by function and where it is used

13.   What is the diff between Where and Having

14.   What is the use of Aggregate functions in Oracle?

15.   What are user defined data types?

16.   Difference between DECODE and CASE

17.   What is %Rowtype and %Type

18.   Explain the different cursor attributes

19.   Explain different types of cursors

20.   What are the different types of Exceptions?

21.   What is the basic difference between a Procedure and a Function?

22.   What are the modes of parameters in a PL/SQL Procedure?

23.   Explain different types of triggers

24.   Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?

25.   Describe the disadvantage of Database Trigger over Stored Procedures? 

26.   What is an Exception? What are types of Exception?

27.   When an exception is triggered in a loop, how do you continue to next iteration?

28.   What is Pragma EXECPTION_INIT? Explain the usage?

29.   How do you define public and private procedure in oracle?

30.   Enlist various types of PL/SQL Exceptions?

31.   Which is the Default Cursor in Oracle PL/SQL?

32.   Why is closing the Cursor required during explicit cursor development?

33.   Explain about SQLERRM and SQLCODE and their importance.

34.   What rules are to be taken care of when doing comparisons using NULL?

35.   What is the Difference between Runtime Errors and Syntax Errors?

36.   What are the different Loop Control Structures used in PL/SQL?

37.   How do you go about tuning your PL/SQL code?

38.   How can we create a table in PL/SQL block and insert records into it

39.   Explain the usage of WHERE CURRENT OF clause in cursors?

40.   What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?

41.   What is Overloading of procedures?

42.   What is the difference between a view and a materialized view?

43.   Name some Oracle's built-in packages?

44.   Explain autonomous transaction

45.   Can COMMIT statement be executed as part of trigger

46.   Explain how to debug PL/SQL program.

47.   How can we read and write files from PL/SQL program?

How to create an ESS Job in Oracle fusion for a BIP report

This post provides the details about how to create an ESS job for the Oracle BIP report. Below are the steps mentioned below. 

Before creating the ESS Job, get the details of the Report ID from the Oracle BIP catalogue.

Go to the OTBI catalog. Navigate to the Shared Folders -> Custom -> Required BIP Report.

In this case the path for the Test Report would be from Custom. You need to remove the Shared Folders.

Report ID would be : /Custom/TestFolder/Test Report.xdo

1. Navigate to the "Setup and Maintenance" in Settings Action.

2. Do the global search with the following value 

"Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications"

3. Once in the screen, click on '+' to create a New ESS Job

4. Provide the following details :

    a. Enter the Display Name  : Display Name

    b. Name 

    c. Path 

    d. Description

    e. Job Application Name

    f. Report ID :  Provide the BIP details with the above details.

  5. Enable submission from Scheduled Processes

  6  Enter the Input parameters if any for the BIP report

      Click on '+'

      Enter the Parameter Prompt

      Data Type

      

            

  7. Save and close.

This would create the ESS job, which you can submit it from the Scheduled Process

      

Oracle SQL Query to get the Contract Attachments/Documents Details in Oracle fusion.

Here is the SQL Query to get the Contract Attachments/Documents details in Oracle fusion. This query can be modified accordingly as per the requirements. 

These are the tables, refer here in this post. Oracle Tables for the Contract Attachment Documents

SELECT oha.contract_number,

       oha.cognomen,

       fad.document_id,

       fdv.file_name

  FROM fnd_attached_documents fad,

       fnd_documents_vl fdv,

       okc_k_headers_vl oha

 WHERE fad.entity_name = 'OKC_SUPPORTING_DOCS'

   AND fad.document_id = fdv.document_id

   AND fad.pk1_value = oha.id

  ORDER BY oha.contract_number

Tables for the Contract Documents/Attachments in Oracle fusion

Here are the tables for the Contract Documents/Attachments in Oracle fusion. This tables will store the primary and secondary Contract attachments/documents. 

1.  FND_ATTACHED_DOCUMENTS 

2.  FND_DOCUMENTS_VL 

3.  FND_DOCUMENT_ENTITIES_VL 

4. FND_DOCUMENT_CATEGORIES_VL

Please refer the SQL Query to fetch the Contract Attachment details here.

IN and NVL Function in Oracle BIP Data model SQL Query.

Here is the sample query where you can use IN clause and NVL Function in the SQL Query - where clause in the Oracle BIP report.

Ex : Say you have an input parameter with the Comma separated values, and the report should pull all the required values as per the input parameter or it should pull all the records when no value is provided.

Input Parameter -   :P_NAME

Properties -   Comma separated values

Sample Query

SELECT * FROM <TABLE>

WHERE ( 1 = DECODE(:P_Name,null,1,0) OR name IN (:P_Name))

This way it will pull all the records if no input parameter is specified or if any input parameter is specified it will retrieve only those records.

Sample General Ledger ( GL ) Journal - SQL Query - Oracle Cloud Fusion.

 

Here is the sample SQL Query to fetch the basic General Ledger( GL ) - SQL Query in the Oracle Cloud fusion. This query may be modified accordingly as per the requirements.

SELECT src.user_je_source_name,    

       cat.user_je_category_name,  

       hdr.name,                   

       batch.name,                 

       hdr.date_created,

       hdr.default_effective_date,

       hdr.status,                 

       hdr.currency_code,          

       line.je_line_num,           

       line.description,           

       line.entered_dr,

       line.entered_cr,

       line.accounted_dr,

       line.accounted_cr,

       line.currency_conversion_rate,

       line.currency_conversion_type,

       line.currency_conversion_date,

       gcc.segment1,

       gcc.segment2,

       gcc.segment3,

       gcc.segment4,

       gcc.segment5,

       gcc.segment6,

       gcc.segment7,

       gcc.segment8,

       gcc.segment9,

       gcc.segment10,

       batch.name                 Reference_1,

       batch.description          Reference_2,

       NULL                       Reference_3,

       hdr.name                   Reference_4,

       hdr.description            Reference_5,

       hdr.external_reference     Reference_6,

       hdr.accrual_rev_flag       Reference_7,

       hdr.accrual_rev_period_name       Reference_8,

       hdr.accrual_rev_change_sign_flag  Reference_9,

       line.description           Reference_10,

       ledger.name                

       gp.period_name             

  FROM gl_je_headers       hdr,

       gl_je_lines         line,

       gl_code_combinations gcc,

       gl_ledgers          ledger,

       gl_je_sources       src,

       gl_je_categories    cat,

       gl_je_batches       batch, 

       gl_periods          gp

 WHERE 1 = 1

   AND hdr.je_header_id = line.je_header_id

   AND line.code_combination_id = gcc.code_combination_id

   AND hdr.ledger_id = ledger.ledger_id

   AND line.ledger_id = ledger.ledger_id

   AND hdr.je_source = src.je_source_name

   AND hdr.je_category = cat.je_category_name

   AND hdr.je_batch_id = batch.je_batch_id

   AND hdr.period_name = gp.period_name

   AND ledger.period_set_name = gp.period_set_name

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

 Here is the generic SQL Query to get the Supplier Site Assignment details as per the FBDI template in the Oracle Fusion.

select

  psv.vendor_name,

  pssv.vendor_site_code,

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

  ( select  bu_name from fun_all_business_units_v where bu_id=psaam.bu_id) client_bu,

  ( select  bu_name from fun_all_business_units_v where bu_id=psaam.bill_to_bu_id) bill_tobu,

  hla_ship.location_code,

  hla_bill.location_code,

  psaam.allow_awt_flag,

  psaam.awt_group_id,

  gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 liability_distribution,

  gcc2.segment1||'.'||gcc2.segment2||'.'||gcc2.segment3||'.'||gcc2.segment4||'.'||gcc2.segment5||'.'||gcc2.segment6||'.'||gcc2.segment7||'.'||gcc2.segment8  prepayment_distribution,

  gcc1.segment1||'.'||gcc1.segment2||'.'||gcc1.segment3||'.'||gcc1.segment4||'.'||gcc1.segment5||'.'||gcc1.segment6||'.'||gcc1.segment7||'.'||gcc1.segment8 bill_payable_distribution,

  psaam.inactive_date,

  psaam.distribution_set_id distribution_name

 from   poz_suppliers_v psv,

        poz_supplier_sites_v pssv,

poz_site_assignments_all_m psaam,

poz_supplier_sites_all_m pssam,

hr_locations   hla_ship,

hr_locations   hla_bill,

gl_code_combinations  gcc,

gl_code_combinations  gcc1,

gl_code_combinations  gcc2,

fun_all_business_units_v fabuv

where psv.vendor_id             = pssv.vendor_id

and   psaam.vendor_site_id      = pssv.vendor_site_id

and   pssam.vendor_site_id      = psaam.vendor_site_id

and   hla_ship.location_id(+)   = psaam.ship_to_location_id

and   hla_bill.location_id (+)  = psaam.bill_to_location_id

and   gcc.code_combination_id(+)  = psaam.accts_pay_code_combination_id

and   gcc1.code_combination_id(+) = psaam.future_dated_payment_ccid

and   gcc2.code_combination_id(+) = psaam.prepay_code_combination_id

and   fabuv.bu_id                 = pssam.prc_bu_id

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

 Here is the generic SQL Query to get the Supplier Contact details as per the FBDI template in Oracle fusion.

select 

hpo.party_name  supplier_name,

hpp.person_pre_name_adjunct,

hpp.person_first_name,

hpp.person_middle_name,

hpp.person_last_name,

hc.job_title,

hps.party_site_name,

hor.role_type,

email.email_address,

phone.phone_country_code,

phone.phone_area_code,

phone.phone_number,

phone.phone_extension,

fax.fax_country_code,

fax.fax_area_code,

fax.fax,

mobile.mobile_country_code,

mobile.mobile_area_code,

mobile.mobile,

poc.inactive_date

FROM

hz_parties hpo,

hz_relationships hzr,

hz_parties hpp,

hz_org_contacts hc,

hz_org_contact_roles hor,

poz_supplier_contacts poc,

hz_party_sites hps,

poz_suppliers ps,

(select email_address, owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='email'

and hcp.owner_table_name='hz_parties') email,

(select phone_country_code,phone_area_code,phone_number,phone_extension,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'gen') phone,

(select phone_country_code fax_country_code,phone_area_code fax_area_code,phone_number fax,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'fax') fax,

(select phone_country_code mobile_country_code,phone_area_code mobile_area_code, phone_number mobile,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'mobile') mobile

where

hpo.party_type='organization'

and hzr.object_id=hpo.party_id

and hpp.party_type='person'

and hpp.party_id=hzr.subject_id

and hc.party_relationship_id=hzr.relationship_id

and hps.party_site_id(+) =  poc.party_site_id

and hzr.relationship_id = poc.relationship_id(+)

and hor.org_contact_id(+) = hc.org_contact_id

and ps.party_id=hpo.party_id

and email.owner_table_id(+)=hpp.party_id

and phone.owner_table_id(+)=hpp.party_id

and fax.owner_table_id(+)=hpp.party_id

and mobile.owner_table_id(+)=hpp.party_id

and hzr.relationship_code = 'contact_of'

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

Here is the generic Suppliers Address Details as per the FBDI Template.

SELECT  

  hp.party_name 

         ,psa.party_site_name AddressName

  ,psa.country

  ,psa.address1

  ,psa.address2

  ,psa.address3

  ,psa.address4

  ,psa.address_lines_phonetic

  ,psa.addr_element_attribute1

  ,psa.addr_element_attribute2

  ,psa.addr_element_attribute3

  ,psa.addr_element_attribute4

  ,psa.addr_element_attribute5

  ,psa.building

  ,psa.floor_number

  ,psa.city

  ,psa.state

  ,psa.province

  ,psa.county

  ,psa.postal_code

  ,psa.postal_plus4_code

  ,psa.location_language

  ,psa.inactive_date

  ,psa.phone_country_code

  ,psa.phone_area_code

  ,psa.phone_number

  ,psa.phone_extension

  ,psa.fax_country_code

  ,psa.fax_phone_area_code

  ,psa.fax_phone_number

  ,psa.address_purpose_rfq_or_bidding

  ,psa.address_purpose_ordering

  ,psa.address_purpose_remit_to

,psa.email_address  email

FROM  poz_suppliers pos

     ,hz_parties hp

     ,poz_supplier_address_v psa 

, hz_party_sites hps

WHERE pos.party_id        = hp.party_id

  AND pos.vendor_id       = psa.vendor_id

  AND psa.party_id        = pos.party_id

  and hps.party_site_id = psa.party_site_id

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