Skip to main content

Do you know how to fetch Oracle Cloud Supplier data using SQL

In this session, we are going to see how to fetch Oracle Cloud Supplier data using SQL.

SQL Text:
select sup.vendor_name Supplier_Name ,sup.segment1 Supplier_Number ,sup_addr.party_site_name Address_Name ,sup_addr.address1 Address_Line_1 ,sup_addr.city City ,sup_addr.state State ,sup_addr.postal_code Postal_Code ,case when (sup_addr.phone_country_code is not null and sup_addr.phone_area_code is not null and sup_addr.phone_number is not null) then '+' || sup_addr.phone_country_code || ' (' || sup_addr.phone_area_code || ') ' || substr(sup_addr.phone_number,1,3) || '-' || substr(sup_addr.phone_number,4,4) else '' end Phone ,case when (sup_addr.address_purpose_ordering = 'Y' and sup_addr.address_purpose_remit_to = 'Y' and sup_addr.address_purpose_rfq_or_bidding = 'Y') then 'Ordering; Remit to; RFQ or Bidding' when (sup_addr.address_purpose_ordering = 'Y' and sup_addr.address_purpose_remit_to = 'Y' and sup_addr.address_purpose_rfq_or_bidding = 'N') then 'Ordering; Remit to' when (sup_addr.address_purpose_ordering = 'Y' and sup_addr.address_purpose_remit_to = 'N' and sup_addr.address_purpose_rfq_or_bidding = 'Y') then 'Ordering; RFQ or Bidding' when (sup_addr.address_purpose_ordering = 'N' and sup_addr.address_purpose_remit_to = 'Y' and sup_addr.address_purpose_rfq_or_bidding = 'Y') then 'Remit to; RFQ or Bidding' when (sup_addr.address_purpose_ordering = 'Y' and sup_addr.address_purpose_remit_to = 'N' and sup_addr.address_purpose_rfq_or_bidding = 'N') then 'Ordering' when (sup_addr.address_purpose_ordering = 'N' and sup_addr.address_purpose_remit_to = 'Y' and sup_addr.address_purpose_rfq_or_bidding = 'N') then 'Remit to' when (sup_addr.address_purpose_ordering = 'N' and sup_addr.address_purpose_remit_to = 'N' and sup_addr.address_purpose_rfq_or_bidding = 'Y') then 'RFQ or Bidding' else '' end Address_Purpose ,case when sup_site.inactive_date is null then 'Active' else 'Inactive' end Status ,sup_site.vendor_site_code Site ,(select bu_name from fun_bu_usages_v where 1 = 1 and module_key = 'PROCUREMENT_BF' and business_unit_id = sup_site.prc_bu_id) Procurement_BU ,case when (sup_site.pay_site_flag = 'Y' and sup_site.purchasing_site_flag = 'Y' and sup_site.primary_pay_site_flag = 'Y') then 'Purchasing; Pay; Primary Pay' when (sup_site.pay_site_flag = 'Y' and sup_site.primary_pay_site_flag = 'Y') then 'Pay; Primary Pay' when (sup_site.pay_site_flag = 'Y' and sup_site.purchasing_site_flag = 'Y') then 'Purchasing; Pay' when (sup_site.purchasing_site_flag = 'Y' and sup_site.primary_pay_site_flag = 'Y') then 'Purchasing; Primary Pay' when (sup_site.purchasing_site_flag = 'Y') then 'Purchasing' when (sup_site.pay_site_flag = 'Y') then 'Pay' when (sup_site.primary_pay_site_flag = 'Y') then 'Primary Pay' when (sup_site.rfq_only_site_flag = 'Y') then 'Sourcing Only' else '' end Site_Purpose from poz_suppliers_v sup ,poz_supplier_address_v sup_addr ,poz_supplier_sites_v sup_site where 1 = 1 and sup_site.vendor_id = sup.vendor_id and sup_addr.vendor_id = sup_site.vendor_id and sup_addr.party_site_id = sup_site.party_site_id order by sup.vendor_name
Labels:

Comments

Post a Comment

Popular posts from this blog

Do you know how to cancel the Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to cancel the Work Relationship using HDL (HCM Data Loader) Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship To cancel the Work Relationship, we use HDL Source Key method. Use the below sample template for reference SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y In the above template, we do not know the values of the source keys SourceSystemOwner SourceSystemId PersonId(SourceSystemId) Use below query to fetch the SourceSystemOwner Query: select source_system_owner from hrc_integration_key_map where surrogate_id = period_of_service_id   -- pass period_of_service_id corresponding to Work Relationship Use below query to fetch the SourceSy...
3 comments
Read more

Do you know how to generate output using eText Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Word Document and save it as type "Rich Text Format" Add a format setup which is given below <TEMPLATE TYPE> FIXED_POSITION_BASED <OUTPUT CHARACTER SET> UTF-8 <NEW RECORD CHARACTER> Carriage Return Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. For headers, use the level DATA_DS which is the top level and appears only once in the XML <LEVEL> DATA_DS <POSITION> <LENGTH> <FORMAT> <PAD> <DATA> <COMMENTS> <NEW RECORD> DATA_DS Alpha ‘Emplid’ ...
20 comments
Read more

Do you know how to fetch Oracle Cloud HCM Performance Questionnaire data using SQL

In this session, we are going to see how to how to fetch Oracle Cloud HCM Performance Questionnaire data using SQL SQL Text: with response_view as ( select ep . evaluation_id , ep . person_id , ep . role_type_code , pq . qstnr_question_id , pq . question_type , pq . question_text question , coalesce ( qr . answer_text , qa . long_text ) answer from hra_eval_participants ep , hrq_qstnr_participants qp , hrq_eval_ptcpnt_questions_v epq , hrq_ptcpnt_questions_v pq , hrq_qstnr_latest_resps_v qnlr , hrq_qstn_responses qr , hrq_all_qstn_answers_v qa where 1 = 1 and qp . participant_id = to_char ( ep . eval_participant_id ) and epq . participant_id = qp . participant_id and epq . subject_id = qp . subject_id and pq . qstnr_section_id = epq . qstnr_section_id and pq . qstnr_question_id = epq . qstnr_question_id and qnlr . subject_id = to_char...
Post a Comment
Read more