Import Program to Import PO receipts in Oracle fusion.

 Here is the quick post to import the Purchase orders(PO) Receipts 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 "Manage Receiving Transactions". 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 successfully.

5.Once the Load Interface File for Import Program is completed successfully, click on Schedule New Process. Search and select "Manage Receiving Transactions"

Ignore the Group ID parameters and Submit the process.

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

Supplier Interface error tables in Oracle Cloud Fusion.

In this post, I will provide you the list of the Interface tables for the Suppliers import in Oracle cloud fusion and also the corresponding Supplier Interface error table.

The Supplier rejection interface table will provide you the Status of the records and the corresponding rejection reason if there are any errors during the Import Process.

Here is the list of the Supplier Interface tables in the Oracle fusion :

1.  POZ_SUPPLIERS_INT

2. POZ_SUP_ADDRESSES_INT

3. POZ_SUPPLIER_SITES_INT

4. POZ_SITE_ASSIGNMENTS_INT

5. POZ_SUP_CONTACTS_INT

6. POZ_SUP_CONTACT_ADDRESSES_INT

These tables will hold the records to be imported into the Oracle fusion. Each of these tables have either "Status" or "Import_Status" column which provides the status of the records following the import process.

And say if there are any rejections or errors in the Interface records, the "Status" or the "Import_Status" column are updated with the "REJECTED" otherwise it will be updated with the "PROCESSED" status.

Following is the error/rejection table which will hold the error or the rejected reason for the interface records.

             POZ_SUPPLIER_INT_REJECTIONS

How do you link these interface tables with this rejections table ? See below for the connecting these tables.

1. Suppliers Import :

 SELECT psi.vendor_interface_id, psir.reject_lookup_code, psir.attribute

  FROM POZ_SUPPLIERS_INT psi,

       POZ_SUPPLIER_INT_REJECTIONS psir

 WHERE psir.parent_id = psi.vendor_interface_id

   AND psi.status = 'REJECTED' 

   AND psir.parent_table = 'POZ_SUPPLIERS_INT'

2. Suppliers Address Import

 SELECT psai.address_interface_id, psir.reject_lookup_code, psir.attribute

  FROM POZ_SUP_ADDRESSES_INT psai,

       POZ_SUPPLIER_INT_REJECTIONS psir

 WHERE psir.parent_id = psai.address_interface_id

   AND psai.import_status = 'REJECTED' 

   AND psir.parent_table = 'POZ_SUP_ADDRESSES_INT'

3. Suppliers Sites Import

SELECT pssi.vendor_site_interface_id,psir.reject_lookup_code, psir.attribute

  FROM POZ_SUPPLIER_SITES_INT pssi,

       POZ_SUPPLIER_INT_REJECTIONS psir

 WHERE psir.parent_id = pssi.vendor_site_interface_id

   AND pssi.status = 'REJECTED' 

   AND psir.parent_table = 'POZ_SUPPLIER_SITES_INT'

4.Supplier Site Assignment Import

SELECT psai.assignment_interface_id, psir.reject_lookup_code, psir.attribute

  FROM POZ_SITE_ASSIGNMENTS_INT psai,

       POZ_SUPPLIER_INT_REJECTIONS psir

 WHERE psir.parent_id = psai.assignment_interface_id

   AND psai.status = 'REJECTED' 

   AND psir.parent_table = 'POZ_SITE_ASSIGNMENTS_INT'

5. Supplier Contacts Import

SELECT psci.contact_interface_id, psir.reject_lookup_code, psir.attribute

  FROM POZ_SUP_CONTACTS_INT psci,

       POZ_SUPPLIER_INT_REJECTIONS psir

 WHERE psir.parent_id = psci.contact_interface_id

   AND psci.import_status = 'REJECTED' 

   AND psir.parent_table = 'POZ_SUP_CONTACTS_INT'

6. Supplier Contact Address Import

SELECT pscai.contact_address_interface_id,psir.reject_lookup_code, psir.attribute

  FROM POZ_SUP_CONTACT_ADDRESSES_INT pscai,

       POZ_SUPPLIER_INT_REJECTIONS psir

 WHERE psir.parent_id = pscai.contact_address_interface_id

   AND pscai.import_status = 'REJECTED' 

   AND psir.parent_table = 'POZ_SUP_CONTACT_ADDRESSES_INT'

Tables for the AP Invoice in Oracle fusion

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

• AP_INVOICES_ALL

• AP_INVOICE_LINES_ALL

• AP_TERMS

• AP_AWT_GROUPS

• FUN_ALL_BUSINESS_UNITS_V

• XLE_ENTITY_PROFILES

• AP_DISTRIBUTION_SETS_ALL

• AP_INVOICE_DISTRIBUTIONS_ALL

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 PO Receipts in Oracle fusion

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

1. RCV_SHIPMENT_HEADERS

2. RCV_SHIPMENT_LINES

3. RCV_TRANSACTIONS

4. PO_HEADERS_ALL

5. PO_LINES_ALL

6. PO_DISTRIBUTIONS_ALL

7. PO_LINE_LOCATIONS

8. INV_LICENSE_PLATE_NUMBERS

9. HR_LOCATIONS

10. INV_ORG_PARAMETERS

11. POZ_SUPPLIERS_V

12. POZ_SUPPLIER_SITES_V

13.WSH_CARRIERS_V

14.EGP_SYSTEM_ITEMS_B

15.HR_OPERATING_UNITS

16. PO_DISTRIBUTIONS_ALL

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 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'