Supplier With Bank Details and Payment Details

SQL  Query
-----------------------------------
select asp.party_id,
       asp.vendor_id,
       ass.pay_site_flag,
       ass.vendor_site_id,
       hrl_ship.location_code,
       hp_supp.party_id,
       asp.VENDOR_NAME Supplier_name,
       asp.VENDOR_TYPE_LOOKUP_CODE type,
       asp.ONE_TIME_FLAG,
       asp.individual_1099,
       DECODE (
            UPPER (asp.vendor_type_lookup_code),
            'EMPLOYEE',
            papf.national_identifier,
            DECODE (asp.organization_type_lookup_code,
                    'INDIVIDUAL', asp.individual_1099,
                    'FOREIGN INDIVIDUAL', asp.individual_1099,
                    hp_supp.jgzz_fiscal_code)
         ) Taxpayer_ID,
         asp.VAT_REGISTRATION_NUM ,
         hp_supp.DUNS_Number_c  ,
         asp.start_date_active,
         asp.end_date_active,
         asp.hold_flag,
         asp.last_update_date,
         ass.address_line1,
         ass.address_line2,
         ass.address_line3 ,
         ass.city,
         ass.state,
         ass.zip
         , ass.country,
         ass.purchasing_site_flag ,
         ass.RFQ_ONLY_SITE_FLAG ,
         ass.PAY_SITE_FLAG
         , ass.Vendor_Site_Code
         , ass.ATTRIBUTE1 Supplier_Qualification_Status
         , ass.ATTRIBUTE15  Invoice_Approver --, ass.SHIP_TO_LOCATION_CODE
         , hrl_ship.location_code ship_to_location_code
         , hrl_bill.location_code bill_to_location_code
         , ass.inactive_date
         , ass.last_update_date site_last_update_date
         , ass.terms_id
         , att.name terms_name
         , ass.invoice_currency_code
         , ass.payment_currency_code
         , ass.hold_all_payments_flag
         , ass.hold_future_payments_flag
         , ass.vat_registration_num
         , hou.name OPERATING_UNIT_NAME
         , ass.email_address
         , ass.primary_pay_site_flag
         , ass.DUNS_NUMBER
         , ass.PAY_GROUP_LOOKUP_CODE
         , person.person_first_name first_name
         , person.person_last_name  last_name
         , pty_rel.email_address
         , pty_rel.primary_phone_number
         , apsc.creation_date contact_creation_date
         , apsc.last_update_date contact_last_update_date
          --hrl_ship.*
-- person.person_first_name ,
-- person.person_last_name ,pty_rel.primary_phone_number ,
-- pty_rel.email_address
-- , apsc.email_address , ass.*
,ass.duns_number site_duns_number
from ap_suppliers asp
   , ap_supplier_sites_all ass
   , ap_supplier_contacts apsc
   , hz_parties person
   , hz_parties pty_rel
   , hz_parties hp_supp
   , per_all_people_f papf
   , hr_locations hrl_ship
   , hr_locations hrl_bill  
   , ap_terms_tl att
   , hr_operating_units hou
   --, AND pv.party_id = hp.party_id
where 1 = 1
--and asp.vendor_id = 46006 -- 5001 -- 
and ass.vendor_id = asp.vendor_id
and asp.party_id = hp_supp.party_id
AND apsc.org_party_site_id(+) = ass.party_site_id
--and ass.org_id = 222 -- 123
AND apsc.per_party_id = person.party_id(+)
AND apsc.rel_party_id = pty_rel.party_id(+)
AND asp.employee_id = papf.person_id(+)
and hrl_ship.location_id(+) = ass.ship_to_location_id
and hrl_bill.location_id(+) = ass.bill_to_location_id
and att.term_id(+) = ass.terms_id
and ass.org_id = hou.organization_id
and TRUNC(asp.creation_date) between :P_FROM_CREATION_DATE AND :P_TO_CREATION_DATE
and TRUNC(asp.last_update_date) between NVL(:P_FROM_UPDATE_DATE,TRUNC(asp.last_update_date)) AND NVL(:P_TO_UPDATE_DATE,TRUNC(asp.last_update_date))
and asp.vendor_name = nvl(:p_supplier_name,asp.vendor_name)

Formula Column1(CF_BANK_DATA)
-----------------------------
function CF_BANK_DATAFormula return Number is
begin
 
 
     SELECT   eb.bank_name                 ,
              eb.bank_number ,
                  ebb.bank_branch_name         ,
              ebb.branch_number            ,
              eba.bank_account_num         ,
              eba.bank_account_name
       INTO :CP_BANK_NAME,
            :CP_BANK_NUMBER,
            :CP_BANK_BRANCH_NAME,
            :CP_BRANCH_NUMBER,
            :CP_BANK_ACCOUNT_NUM  ,
            :CP_bank_account_name
   FROM ap.ap_suppliers av         ,
        ap.ap_supplier_sites_all assa     ,
        apps.iby_ext_bank_accounts eba  ,
        apps.iby_account_owners ao      ,
        apps.iby_ext_banks_v eb         ,
        apps.iby_ext_bank_branches_v ebb,
            hr_operating_units hou
  WHERE av.vendor_id           = assa.vendor_id
AND ao.account_owner_party_id = av.party_id
AND eba.ext_bank_account_id   = ao.ext_bank_account_id
AND eb.bank_party_id          = ebb.bank_party_id
AND eba.branch_id             = ebb.branch_party_id
AND eba.bank_id               = eb.bank_party_id
AND assa.org_id                 =hou.organization_id
AND av.vendor_id               = :vendor_id
AND assa.vendor_site_id    = :vendor_site_id;

    RETURN(NULL);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(100,'No bank records for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Formula Column2(CF_PAYMENT_METHOD_CODE)
--------------------------------
function CF_PAYMENT_METHOD_CODEFormula return Char is
lv_payment_method_code   VARCHAR2(30);
begin
 
 
  SELECT ieppm.payment_method_code
   INTO lv_payment_method_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id;
 

    RETURN(lv_payment_method_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(101,'No payment_method_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

formula Column3(CF_FORMAT_CODE)
-----------------------
function CF_FORMAT_CODEFormula return Char is
lv_format_code   VARCHAR2(30);
begin
 
 
  SELECT ifv.format_code
   INTO lv_format_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA,
        IBY_FORMATS_VL IFV
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id
    AND ifv.format_code(+) = iepa.payment_format_code;
 

    RETURN(lv_format_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(102,'No format_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;