FSQL 15

SQL QUERY FOR PO Details IN ORACLE CLOUD FUSION R13

SELECT pha.segment1                                 po_number

       ,pla.line_num                                line_number

       ,TO_CHAR(pha.creation_date, 'DD-MM-YYYY')    po_date

       ,psv.vendor_name                             supplier_name       

       ,psav.address1                               supplier_address_line1

       ,psav.address2                               supplier_address_line2

       ,psav.city                                   supplier_city

       ,psav.postal_code                            supplier_postal_code

       ,NVL(psav.state, psav.province)              supplier_state

       ,(SELECT ftv.territory_short_name

           FROM fnd_territories_vl     ftv

          WHERE psav.country  = ftv.territory_code) supplier_country

       ,pla.item_description                        item_description

       ,pla.quantity                                units_ordered

       ,pla.unit_price                              per_unit_price

       ,(pla.quantity * pla.unit_price)             total_price

       ,hla1.address_line_1                         ship_to_add_line1

       ,hla1.address_line_2                         ship_to_add_line2

       ,hla1.town_or_city                           ship_to_city

       ,hla1.postal_code                            ship_to_postal_code

       ,(SELECT ftv.territory_short_name

           FROM fnd_territories_vl     ftv

          WHERE hla1.country  = ftv.territory_code) ship_to_country

       ,hla1.telephone_number_1                     ship_to_telephone

       ,pbvv.full_name                              buyer_name

               ,CASE

         WHEN SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2) = 'Company 1'

           THEN  'Company 1'

         WHEN SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2) =  'Company 2'

           THEN  'Company 2'

         WHEN SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2) =  'Company 3'

           THEN  'Company 3'

         ELSE

           SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2)

         END                                        buyer_organization   

       ,hla2.address_line_1                         bill_to_add_line1

       ,hla2.address_line_2                         bill_to_add_line2

       ,hla2.town_or_city                           bill_to_city

       ,hla2.postal_code                            bill_to_postal_code

       ,(SELECT ftv.territory_short_name

           FROM fnd_territories_vl     ftv

          WHERE hla2.country  = ftv.territory_code) bill_to_country

       ,pbvv.work_telephone                         bill_to_telephone

       ,(CASE WHEN ppnf.full_name IS NULL

              THEN pbvv.full_name

              WHEN ppnf.full_name IS NULL AND pbvv.full_name IS NULL

              THEN ppnf.full_name

              ELSE ppnf.full_name

         END)                                       requestor

       ,CASE

         WHEN SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2) =  'Company 1'

           THEN  'Company 1'

         WHEN SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2) =  'Company 2'

           THEN  'Company 2'

         WHEN SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2) =  'Company 3'

           THEN  'Company 3'

         ELSE

           SUBSTR(haou.name, 1, INSTR(haou.name, 'BU')-2)

         END                                        requestor_organization

                        ,psv.vendor_id    

               ,(SELECT distinct zr1.registration_number

                   FROM zx_registrations zr1

                                ,zx_party_tax_profile zptp1

                        ,poz_supplier_sites_all_m  pssam

          WHERE zr1.party_tax_profile_id = zptp1.party_tax_profile_id

            AND pssam.party_site_id      = zptp1.party_id

            AND psv.vendor_id            = pssam.vendor_id

                                    AND rownum=1)    supplier_gst

               ,(select distinct psp.income_tax_id

                   from poz_suppliers_pii  psp

                          where psv.vendor_id = psp.vendor_id

                            and rownum = 1)      pan_reference  

  FROM po_headers_all                               pha

       ,per_all_assignments_f                       paaf

       ,hr_all_organization_units                   haou

       ,poz_suppliers_v                             psv

       ,poz_supplier_sites_all_x                    pssvv

       ,poz_supplier_address_v                      psav

       ,po_lines_all                                pla

       ,hr_locations_all                            hla1

       ,hr_locations_all                            hla2

       ,po_buyers_val_v                             pbvv

       ,per_person_names_f                          ppnf

       ,por_requisition_headers_all                 prh

       ,por_requisition_lines_all                   prl

       ,por_req_distributions_all                   prd

       ,po_distributions_all                        pd

       ,zx_registrations                            zr

       ,zx_party_tax_profile                        zptp

 WHERE pha.segment1                               = NVL(:p_number,pha.segment1)

   AND haou.name                                  = :p_buyer_org

   AND psv.vendor_name                            = NVL( :p_supplier_name, psv.vendor_name)

   AND pha.document_status                        NOT IN ('INCOMPLETE')

   AND paaf.organization_id(+)                    = haou.organization_id

   AND pha.agent_id                               = paaf.person_id(+)

   AND psv.vendor_id                              = pha.vendor_id

   AND pha.vendor_site_id                         = pssvv.vendor_site_id

   AND pssvv.party_site_id                        = psav.party_site_id

   AND pha.po_header_id                           = pla.po_header_id

   AND pha.ship_to_location_id                    = hla1.location_id(+)

   AND pha.bill_to_location_id                    = hla2.location_id(+)

   AND pha.agent_id                               = pbvv.person_id

   AND ppnf.person_id(+)                          = prh.preparer_id

   AND pla.po_line_id                             = pd.po_line_id

   AND pd.req_distribution_id                     = prd.distribution_id (+)

   AND prd.requisition_line_id                    = prl.requisition_line_id (+)

   AND prl.requisition_header_id                  = prh.requisition_header_id (+)

   AND psav.party_id                             = zptp.party_id(+)

   AND zptp.party_tax_profile_id                  = zr.party_tax_profile_id(+)