Important Tables in Oracle Financials

Important Tables in Oracle Financials 

Account Receivables (AR) Transactions Tables

RA_CUSTOMER_TRX_ALL 

It is a Transaction Header table

RA_CUSTOMER_TRX_LINES_ALL 

It is a Transaction Lines table along with Tax lines.

RA_CUST_TRX_LINE_GL_DIST_ALL 

Distribution table  for Transaction Lines

RA_CUST_TRX_LINE_SALESREPS_ALL 

Salesrep information table for Transaction Lines

Transaction Interface Tables 

RA_INTERFACE_LINES_ALL Transaction 

It stores Lines interface

RA_INTERFACE_SALESCREDITS_ALL 

It stores Transaction Sales credit information

RA_INTERFACE_DISTRIBUTIONS_ALL 

It stores Transaction Distribution information

RA_INTERFACE_ERRORS_ALL 

It stores Transaction errors details.

AR_PAYMENTS_INTERFACE_ALL 

Interface table to import receipts

AR_INTERIM_CASH_RECEIPTS_ALL 

Lockbox transfers the receipts that pass validation to the interim tables.

AR_INTERIM_CASH_RCPT_LINES_ALL 

Lockbox transfers the receipts that pass validation to the interim tables.

Account Receivable(AR) Receipts tables

AR_CASH_RECEIPTS_ALL Cash Receipt

It is a Receitp Header tables

AR_RECEIVABLE_APPLICATIONS_ALL 

It stores Receipt Application details

AR_PAYMENT_SCHEDULES_ALL 

This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable

AR_CASH_RECEIPT_HISTORY_ALL

This table stores all of the activity that is contained for the life cycle of a receipt.

• Each row represents one step.

• The status field for that row tells you which step the receipt has reached.

• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.

Customer Tables 

HZ_PARTIES 

A party is an entity that can enter into a business relationship.

HZ_CUST_ACCOUNTS 

This table stores information about customer/financial relationships established between a Party and the deploying company.

HZ_PARTY_SITES 

This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.

HZ_CUST_ACCT_SITES_ALL 

This table stores information about customer/financial account sites information.

HZ_CUST_SITE_USES_ALL 

This table stores information about the business purposes assigned to a customer account site

HZ_LOCATIONS 

A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.

Setup tables

RA_CUST_TRX_TYPES_ALL 

This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.

AR_RECEIPT_CLASSES 

This table stores the different receipt classes that you define.

AR_RECEIPT_METHODS 

This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Integration Versioning in Oracle Integration Cloud

 Integration Versioning in Oracle Integration Cloud(OIC)

When we create any integration, the version number will be defaulted to 01.00.0000 for all the styles of integration(AppDriven, Schedule and Publisher/Subscriber Integrations)

Detailed Structure of Version in OIC:

01àMajor(two characters long)

00àMinor(two characters long)

0000àPatch(patch characters long)

With two ‘.’ In between, a total of 10 characters long.

Note: Version Number cannot be more than 10 characters.

[Major: 2 chars].

[Minor: 2 chars].

[Patch: 4 chars]

(e.g., 01.00.0000).=10 characters including ‘.’

Q:

Why does activating a Minor or Patch version (like 01.01.0000) automatically deactivate my previous version, while activating a Major version (2.00.0000) allows both to run side-by-side?

For the incremental changes in the Integration, instead of deactivating the existing integration, we can create a new version

I want to make major/minor/patch changes in the integration

                           I can create it as patch(01.00.0001)   à1.0.1;1.0.2;1.0.3…etc

                          I can create it as minor(01.01.0000)à1.1.0;1.2.0;1.3.0—etc

                         I can create it as a major(2.00.0000)à1.0.0;2.0.0;3.0.0..etc

When we try to activate the new version with a patch/minor, it will force us to deactivate the older version. Because in the Gate Way URL or  endpoint URL  we can see (1.0) at the end

When we try to activate minor or patch version, we will get prompt to deactivate the previous version of Integration as the as URL ends with (1.0) for both

Example URL looks like:  

Default Integration endpoint URL(.01.00.0000)

https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/

integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO WORLD REST_API/1.0/

sayHello/{firstName}

For Patch/Min Version Endpoint URL(01.01.0000/01.00.0001)

https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/

integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO WORLD REST_API/1.0/

sayHello/{firstName}

When we activate new version 1.1.0/1.0.1 it will deactivate 1.0.0 to avoid collisions.

-------------------------

Now, for the major version

When I try to activate new version with major changes(2.0),  it will not ask or force us to deactivate the old version

Default Integration endpoint URL(.01.00.0000)

https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/

integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO WORLD REST_API/1.0/

sayHello/{firstName}

For Major versions(02.00.0000)

https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/

integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO WORLD REST_API/2.0/

sayHello/{firstName}

https://a2cfuatoic-idnr9es0pp6w-ia.integration.us-ashburn-1.ocp.oraclecloud.com/ic/api/

integration/v2/flows/rest/project/a2cf_DEMO_PKG/a2cf78 HELLO WORLD REST_API/3.0/

sayHello/{firstName}

 

 

 

 

Supplier Sync Report from Cloud to EBS - Delta Data using parameters Date range with timestamp

Supplier Sync Report from Cloud to EBS - Delta Data using parameters Date range with timestamp

Supplier Master Data

SELECT DISTINCT *
FROM   (SELECT 'SupplierMaster',
               suppMaster.party_id,
               parties.party_name,
               suppMaster.segment1,
               suppMaster.vendor_type_lookup_code,
               suppMaster.vendor_id,
               suppMaster.one_time_flag,
               (SELECT DISTINCT apTerms.name
                FROM   poz_supplier_sites_all_m termNameSuppSitesAll,
                       ap_terms apTerms
                WHERE  suppMaster.vendor_id = termNameSuppSitesAll.vendor_id
                       AND termNameSuppSitesAll.terms_id = apTerms.term_id
                       AND ROWNUM = 1)
               TermName,
               (SELECT DISTINCT invoiceSuppSitesAll.pay_date_basis_lookup_code
                FROM   poz_supplier_sites_all_m invoiceSuppSitesAll
                WHERE  suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
                       AND ROWNUM = 1)
               PayDate,
               (SELECT DISTINCT invoiceSuppSitesAll.payment_priority
                FROM   poz_supplier_sites_all_m invoiceSuppSitesAll
                WHERE  suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
                       AND ROWNUM = 1)
                      PaymentPriority1,
               ' '                                                           AS
                      PaymentCurrency,
               suppMaster.minority_group_lookup_code,
               suppMaster.women_owned_flag,
               (SELECT DISTINCT invoiceSuppSitesAll.terms_date_basis
                FROM   poz_supplier_sites_all_m invoiceSuppSitesAll
                WHERE  suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
                       AND ROWNUM = 1)
                      TERMS_DATE_BASIS,
               ''
                      "_INSPECTION_REQUIRED_FLAG_",
               ''
                      "_RECEIPT_REQUIRED_FLAG_",
               (SELECT DISTINCT
               invoiceSuppSitesAll.allow_substitute_receipts_flag
                FROM   poz_supplier_sites_all_m invoiceSuppSitesAll
                WHERE  suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
                       AND ROWNUM = 1)
                      ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
               (SELECT DISTINCT
               invoiceSuppSitesAll.allow_unordered_receipts_flag
                FROM   poz_supplier_sites_all_m invoiceSuppSitesAll
                WHERE  suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
                       AND ROWNUM = 1)
                      ALLOW_UNORDERED_RECEIPTS_FLAG,
               suppMaster.federal_reportable_flag,
               suppMaster.vat_registration_num
                      VAT_REGISTRATION_NUM,
               suppMaster.tax_reporting_name
                      TaxReportingCode,
               ''
                      "_MATCH_OPTION_",
               ''
                      "_EMPLOYEE_ID_",
               ''
                      "_EMPLOYEE_NAME_",
               To_char(suppMaster.last_update_date, 'DD-MM-YYYY HH24:MI:SS')
                      LAST_UPDATE_DATE,
               regist.registration_status,
               suppMasterPII.income_tax_id
               TaxPayerID
                      ,
               suppMaster.taxpayer_country
                      Tax_Payer_Country,
               suppMaster.organization_type_lookup_code
                      Tax_Organization_Type,
               To_char(suppMaster.end_date_active, 'DD-MM-YYYY HH24:MI:SS')
                      Inactive_Date,
               suppMaster.customer_num
                      Customer_Number,
               suppMaster.corporate_website
                      CORPORATE_WEBSITE,
               suppMaster.vat_code
               VAT_CODE,
               suppMaster.attribute1
               ATTRIBUTE1
                      ,
               suppMaster.bc_not_applicable_flag
                      BUS_CLASS_NOT_APPLICABLE,
               suppMaster.auto_tax_calc_override
                      AUTO_TAX_CALC_OVERRIDE,
               suppOrg.ceo_name
                      Chief_Executive_Name,
               suppOrg.ceo_title
                      Chief_Executive_Title,
               suppMaster.allow_awt_flag
                      Use_Withholding_Tax,
               (SELECT person_first_name
                FROM   hz_parties
                WHERE  busClass.provided_by_contact_id = party_id)
                      Provided_By_First_Name,
               (SELECT person_last_name
                FROM   hz_parties
                WHERE  busClass.provided_by_contact_id = party_id)
                      Provided_By_Last_Name,
               (SELECT email_address
                FROM   hz_parties
                WHERE  busClass.provided_by_contact_id = party_id)
                      Provided_By_Email,
               suppOrg.year_established
                      YearEstablished,
               suppOrg.mission_statement
                      MissionStatement,
               suppOrg.incorp_year
                      YearIncorporated,
               suppOrg.principal_title
                      PrincipalTitle,
               suppOrg.principal_name
                      PrincipalName,
               suppOrg.party_number
               PartyCode,
               (SELECT party_name
                FROM   hz_addtnl_party_names
                WHERE  party_id = suppMaster.party_id
                       AND status_flag = 'A')
               Alias,
               suppOrg.duns_number_c
               DUNSNumber
                      ,
               suppOrg.sic_code
                      SIC,
               suppOrg.fiscal_yearend_month
                      FiscalYearEndM,
               suppOrg.curr_fy_potential_revenue
                      CurrFYPotRevenue,
               suppOrg.pref_functional_currency
                      PrefFuncCurrency,
               suppMaster.business_relationship
                      Relationships
               /*,suppOrg.PARTY_ID                  CustomerNumber
               ,suppOrg.
               ,suppOrg.
               National Insurance Number
               Regional Information 
               */
               ,
               suppMaster.attribute2,
               suppMaster.attribute3,
               suppMaster.attribute4,
               suppMaster.attribute5,
               suppMaster.attribute6,
               suppMaster.attribute7,
               suppMaster.attribute8,
               suppMaster.attribute9,
               suppMaster.attribute10,
               suppMaster.attribute11,
               suppMaster.attribute12,
               suppMaster.attribute13,
               suppMaster.attribute14,
               suppMaster.attribute15,
               suppMaster.attribute16
                      GLOBAL_ATTRIBUTE1,
               suppMaster.attribute17
                      GLOBAL_ATTRIBUTE2,
               suppMaster.attribute18
                      GLOBAL_ATTRIBUTE3,
               suppMaster.attribute19
                      GLOBAL_ATTRIBUTE4,
               suppMaster.attribute20
                      GLOBAL_ATTRIBUTE5,
               (SELECT party_name
                FROM   hz_parties
                WHERE  party_id = suppMaster.parent_party_id)
                      PARENT_SUPPLIER,
               (SELECT vendor_name_alt
                FROM   poz_suppliers_v
                WHERE  party_id = suppMaster.party_id)
                      ALTERNATE_NAME,
               Nvl((SELECT Listagg(
                   Concat(Concat(suppMaster.vendor_id, '_'), Concat(file_name,
                               Concat('#', fdt.description))), ';')
                             within GROUP(ORDER BY url) AS the_list
                    FROM   fnd_documents_tl fdt,
                           fnd_attached_documents fad,
                           fnd_documents fd
                    WHERE  fad.document_id = fdt.document_id
                           AND fad.pk1_value = To_char(suppMaster.vendor_id)
                           AND fad.entity_name = 'POZ_SUPPLIERS'
                           AND fd.document_id = fdt.document_id
                           AND Upper(fd.datatype_code) = 'FILE')
                   ||Decode((SELECT prr.response_repository_id
                             FROM   poq_response_repository prr,
                                    poq_questions pq
                             WHERE  supplier_id = To_char(suppMaster.vendor_id)
                                    AND prr.question_id = pq.question_id
                                    AND pq.question_name = 'Cancelled cheque'),
                     NULL,
                     NULL,
                     ';')
                   || (SELECT Listagg(
       Concat(Concat(suppMaster.vendor_id, '_'),
          Concat(file_name,
                  Concat('#', Nvl(fdt.description, 'Cancelled cheque')))), ';')
                 within GROUP(ORDER BY url) AS the_list
        FROM   fnd_documents_tl fdt,
               fnd_attached_documents fad,
               fnd_documents fd
        WHERE  fad.document_id = fdt.document_id
               AND fad.pk1_value IN
                   (SELECT prrv.resp_repository_value_id
                    FROM   poq_response_repository prr,
                           poq_questions pq,
                           poq_resp_repository_values prrv
                    WHERE  supplier_id = To_char(
                           suppMaster.vendor_id)
                           AND prr.response_repository_id =
                               prrv.response_repository_id
                           AND prr.question_id = pq.question_id
                           AND pq.question_name =
                               'Cancelled cheque')
               AND fad.entity_name = 'POQ_RESP_REPOSITORY_VALUES'
               AND fd.document_id = fdt.document_id
               AND Upper(fd.datatype_code) = 'FILE'), 'XXX')
       Header_AttachmentsFileName,
               Nvl((SELECT Listagg(fdc.user_name, ';')
                             within GROUP(ORDER BY url) AS the_list
                    FROM   fnd_documents_tl fdt,
                           fnd_attached_documents fad,
                           fnd_documents fd,
                           fnd_document_categories_vl fdc
                    WHERE  fad.document_id = fdt.document_id
                           AND pk1_value = To_char(suppMaster.vendor_id)
                           AND entity_name = 'POZ_SUPPLIERS'
                           AND fd.document_id = fdt.document_id
                           AND Upper(fd.datatype_code) = 'FILE'
                           AND fad.category_name = fdc.category_name)
                   || Decode((SELECT prr.response_repository_id
                              FROM   poq_response_repository prr,
                                     poq_questions pq
                              WHERE  supplier_id = To_char(suppMaster.vendor_id)
                                     AND prr.question_id = pq.question_id
                                     AND pq.question_name = 'Cancelled cheque'),
                      NULL,
                      NULL,
                      ';')
                   || (SELECT Listagg(fdc.user_name, ';')
                                within GROUP(ORDER BY url) AS the_list
                       FROM   fnd_documents_tl fdt,
                              fnd_attached_documents fad,
                              fnd_documents fd,
                              fnd_document_categories_vl fdc
                       WHERE  fad.document_id = fdt.document_id
                              AND pk1_value IN
                                  (SELECT prrv.resp_repository_value_id
                                   FROM   poq_response_repository prr,
                                          poq_questions pq,
                                          poq_resp_repository_values prrv
                                   WHERE  supplier_id = To_char(
                                          suppMaster.vendor_id)
                                          AND prr.response_repository_id =
                                              prrv.response_repository_id
                                          AND prr.question_id =
                                              pq.question_id
                                          AND pq.question_name =
                                              'Cancelled cheque')
                              AND entity_name = 'POQ_RESP_REPOSITORY_VALUES'
                              AND fd.document_id = fdt.document_id
                              AND Upper(fd.datatype_code) = 'FILE'
                              AND fad.category_name = fdc.category_name), 'XXX')
                      HEADERSCATEGORY,
               Nvl((SELECT prr.response_repository_id
                    FROM   poq_response_repository prr,
                           poq_questions pq
                    WHERE  supplier_id = To_char(suppMaster.vendor_id)
                           AND prr.question_id = pq.question_id
                           AND pq.question_name = 'Cancelled cheque'), '0')
                      RESPONSE_REPOSITORY_ID,
               Nvl((SELECT prrv.resp_repository_value_id
                    FROM   poq_response_repository prr,
                           poq_questions pq,
                           poq_resp_repository_values prrv
                    WHERE  supplier_id = To_char(suppMaster.vendor_id)
                           AND prr.response_repository_id =
                               prrv.response_repository_id
                           AND prr.question_id = pq.question_id
                           AND pq.question_name = 'Cancelled cheque'), '0')
                      RESP_REPOSITORY_VALUE_ID
        FROM   poz_suppliers suppMaster,
               hz_parties parties,
               hz_party_sites partySite,
               poz_supplier_registrations regist,
               poz_suppliers_pii suppMasterPII,
               hz_organization_profiles suppOrg,
               /*poz_sup_products_services suppProdServ,*/
               poz_bus_classifications busClass
        WHERE  suppMaster.party_id = parties.party_id (+)
               AND suppMaster.party_id = partySite.party_id (+)
               AND suppMaster.party_id = suppOrg.party_id (+)
               AND suppMaster.segment1 = regist.supplier_number (+)
               AND suppMaster.vendor_id = suppMasterPII.vendor_id(+)
               /* AND suppMaster.vendor_id = suppProdServ.vendor_id(+) */
               AND suppMaster.party_id = busClass.party_id(+)
               --AND regist.registration_status = 'APPROVED'
               AND suppMaster.business_relationship = 'SPEND_AUTHORIZED'
               AND ( partySite.attribute2 = 'ACTIVE'
                      OR partySite.attribute2 = 'GSTIN NOT AVAILABLE' )
               AND ( partySite.attribute4 = 'ACTIVE'
                      OR partySite.attribute4 = 'PAN NOT AVAILABLE' )
               AND ( partySite.attribute17 IS NOT NULL
                     AND partySite.attribute18 IS NOT NULL )
               --   and parties.party_name='AKASH TECHNO ENGINEERS'
               --AND parties.country='US'
               AND To_date(To_char(suppMaster.last_update_date,
                           'DD-MM-YYYY HH24:MI:SS'
                           ),
                       'DD-MM-YYYY HH24:MI:SS') BETWEEN
                   To_date(:p_fromdatetime, 'DD-MM-YYYY HH24:MI:SS') AND
                   To_date(:p_todatetime, 'DD-MM-YYYY HH24:MI:SS'
                   )
               AND suppMaster.attribute1 != 'TO BE VALIDATED'
               /* AND suppMaster.ATTRIBUTE3 = 'Y' */
               AND suppMaster.segment1 ! = '10026999'
               AND suppMaster.segment1 NOT IN ( '30006135', '30007587' )
       /*,'12300001','10022482','10005676','10008799', '10028530')*/
       --AND suppMaster.segment1 NOT IN ('30006151')
       /*AND suppMaster.segment1 IN ('10004572', '10000788')*/
       /*and (SELECT DISTINCT
                      invoiceSuppSitesAll.PRC_BU_ID
                       FROM   poz_supplier_sites_all_m invoiceSuppSitesAll
                       WHERE  suppMaster.vendor_id = invoiceSuppSitesAll.vendor_id
                              AND ROWNUM = 1) not in (300000002166391,300000002166404)*/
       /*and suppMaster.segment1 IN ('10001026')
       and parties.party_name = 'A1 CLAMPS INDIA PVT LTD.'*/
       )b 

------------------------------------------

SupplierSiteData

SELECT 'SupplierSite',
       suppMaster.party_id,
       suppMaster.vendor_id,
       suppMaster.segment1,
       partySite.party_site_id,
       partySite.party_site_name,
       parties.party_name,
       supplierSite.vendor_site_id,
       supplierSite.vendor_site_code,
       partySite.attribute2                                           GST_STATUS
       ,
       partySite.attribute4,
       partySite.attribute3
       STATE_JURISDICTION_CODE,
       partySite.attribute9
       CLASSIFICATION,
       partySite.attribute7                                           GSTN_Reg,
       partySite.attribute10
       ATTRIBUTE10,
       partySite.attribute11                                          VAT,
       partySite.attribute12                                          CST,
       partySite.attribute13                                          TIN,
       partySite.attribute14
       ATTRIBUTE14,
       partySite.attribute15                                          EMAIL,
       partySite.attribute16
       ATTRIBUTE16,
       (SELECT operatingUnit.name
        FROM   poz_site_assignments_all_m assALL,
               hr_operating_units operatingUnit
        WHERE  suppMaster.party_id = partySite.party_id
               AND partySite.party_site_id = supplierSite.party_site_id
               AND supplierSite.vendor_site_id = assALL.vendor_site_id
               AND assALL.bu_id = operatingUnit.organization_id)
       Operating_Unit,
       addressDetail.address1,
       addressDetail.address2,
       addressDetail.address3,
       addressDetail.address4,
       addressDetail.city,
       addressDetail.state,
       addressDetail.postal_code,
       addressDetail.province,
       addressDetail.country,
       (SELECT territory_short_name
        FROM   fnd_territories_vl
        WHERE  territory_code = addressDetail.country)
       CountryName,
       ''
       "_SITE_USE_TYPE_",
       (SELECT loc.location_name
        FROM   poz_site_assignments_all_m assALL,
               hr_locations_all loc
        WHERE  assALL.vendor_site_id = supplierSite.vendor_site_id
               AND assALL.ship_to_location_id = loc.location_id)
       SHIP_TO_LOCATION_ID,
       (SELECT loc1.location_name
        FROM   poz_site_assignments_all_m assALL,
               hr_locations_all loc1
        WHERE  assALL.vendor_site_id = supplierSite.vendor_site_id
               AND assALL.bill_to_location_id = loc1.location_id)
       BILL_TO_LOCATION_ID
       /*,(SELECT APPOZ1.FREIGHT_TERMS_LOOKUP_CODE                                          
           FROM AP_POZ_SITE_ADDR_ASSIGNMENT_V APPOZ1                                      
          WHERE APPOZ1.VENDOR_SITE_ID = supplierSite.VENDOR_SITE_ID)                      FREIGHT_TERMS*/
       ,
       supplierSite.freight_terms_lookup_code
       FREIGHT_TERMS,
       (SELECT APPOZ2.fob_lookup_code
        FROM   ap_poz_site_addr_assignment_v APPOZ2
        WHERE  APPOZ2.vendor_site_id = supplierSite.vendor_site_id)
       FOB_LOOKUP_CODE,
       (SELECT ( GCC1.segment1
                 ||'.'
                 ||GCC1.segment2
                 ||'.'
                 ||GCC1.segment3
                 ||'.'
                 ||GCC1.segment4
                 ||'.'
                 ||GCC1.segment5
                 ||'.'
                 ||GCC1.segment6
                 ||'.'
                 ||GCC1.segment7
                 ||'.'
                 ||GCC1.segment8
                 ||'.'
                 ||GCC1.segment9 )
        FROM   poz_site_assignments_all_m billpayable,
               gl_code_combinations GCC1
        WHERE  supplierSite.vendor_site_id = billpayable.vendor_site_id
               AND billpayable.accts_pay_code_combination_id =
                   GCC1.code_combination_id
       )
       ACCT_PAY_CODE_COMBINATION_ID,
       (SELECT ( GCC1.segment1
                 ||'.'
                 ||GCC1.segment2
                 ||'.'
                 ||GCC1.segment3
                 ||'.'
                 ||GCC1.segment4
                 ||'.'
                 ||GCC1.segment5
                 ||'.'
                 ||GCC1.segment6
                 ||'.'
                 ||GCC1.segment7
                 ||'.'
                 ||GCC1.segment8
                 ||'.'
                 ||GCC1.segment9 )
        FROM   poz_site_assignments_all_m billpayable,
               gl_code_combinations GCC1
        WHERE  supplierSite.vendor_site_id = billpayable.vendor_site_id
               AND billpayable.prepay_code_combination_id =
                   GCC1.code_combination_id)
                             PREPAY_CODE_COMBINATION_ID,
       (SELECT term.name
        FROM   ap_terms_tl term
        WHERE  term.term_id = supplierSite.terms_id)                  TERMS_ID,
       supplierSite.payment_currency_code,
       supplierSite.hold_all_payments_flag,
       supplierSite.hold_reason,
       supplierSite.tax_reporting_site_flag
       TAX_REPORTING_SITE_FLAG,
       supplierSite.match_option,
       ''
       "_TOLERANCE_NAME_",
       (SELECT APPOZ2.allow_awt_flag
        FROM   ap_poz_site_addr_assignment_v APPOZ2
        WHERE  APPOZ2.vendor_site_id = supplierSite.vendor_site_id)
       USE_WITHHOLDING_TAX
       /*,(SELECT psp.income_tax_id  
       FROM poz_suppliers_pii psp
          WHERE psp.vendor_id  = suppMaster.vendor_id)                                    PAN_NUMBER*/
       ,
       partySite.attribute18                                          PAN_NUMBER
       ,
       partySite.attribute17
       GSTIN_NO,
       partySite.attribute8
       TDS_SECTION,
       supplierSite.purchasing_site_flag,
       supplierSite.pcard_site_flag,
       supplierSite.pay_site_flag,
       supplierSite.primary_pay_site_flag,
       supplierSite.phone_country_code
       PHONE_COUNTRY_CODE,
       supplierSite.area_code
       PHONE_AREA_CODE,
       supplierSite.phone
       PHONE_NUMBER,
       supplierSite.phone_extension
       PHONE_EXTENSION,
       supplierSite.fax_country_code
       FAX_COUNTRY_CODE,
       supplierSite.fax                                               FAX,
       supplierSite.fax_area_code
       FAX_AREA_CODE,
       addressDetail.addr_element_attribute2,
       To_char(supplierSite.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
       INACTIVE_DATE,
       supplierSite.rfq_only_site_flag
       SOURCING_ONLY,
       supplierSite.customer_num
       CUSTOMER_NUM,
       supplierSite.supplier_notif_method
       Communication_Method,
       supplierSite.email_address
       EMAIL_ADDRESS,
       supplierSite.hold_flag                                         HOLD_FLAG,
       supplierSite.purchasing_hold_reason
       PURCHASING_HOLD_REASON,
       supplierSite.pay_on_code
       PAY_ON_RECEIPT,
       supplierSite.pay_on_use_flag                                   PAY_ON_USE
       ,
       supplierSite.po_ack_reqd_days
       PO_ACK_REQD_DAYS,
       supplierSite.pay_on_receipt_summary_code
       Invoice_Summary_Level,
       supplierSite.gapless_inv_num_flag
       GAPLESS_INV_NUMBERING,
       supplierSite.selling_company_identifier
       Selling_Company_Identifier,
       supplierSite.create_debit_memo_flag
       CREATE_DEBIT_MEMO_FROM_RETURN,
       supplierSite.enforce_ship_to_location_code
       Ship_To_Exception_Action,
       supplierSite.receiving_routing_id
       Receipt_Routing,
       supplierSite.qty_rcv_tolerance
       Over_Receipt_Tolerance,
       supplierSite.qty_rcv_exception_code
       Over_Receipt_Action,
       supplierSite.days_early_receipt_allowed
       Early_Receipt_Tolerance_Days,
       supplierSite.days_late_receipt_allowed
       Late_Receipt_Tolerance_Days,
       supplierSite.receipt_days_exception_code
       Receipt_Date_Exception,
       supplierSite.payment_priority
       Payment_Priority,
       supplierSite.pay_group_lookup_code                             Pay_Group,
       supplierSite.hold_unmatched_invoices_flag
       Hold_Unmatched_Invoices,
       supplierSite.hold_future_payments_flag
       Hold_Unvalidated_Invoices,
       supplierSite.hold_by
       Payment_Hold_By,
       To_char(supplierSite.hold_date, 'DD-MM-YYYY HH24:MI:SS')
       Payment_Hold_Date,
       supplierSite.bank_charge_deduction_type
       Bank_Charge_Deduction_Type,
       supplierSite.always_take_disc_flag
       Always_Take_Discount,
       supplierSite.exclude_freight_from_discount
       Exclude_Freight_From_Discoun,
       supplierSite.exclude_tax_from_discount
       Exclude_Tax_From_Discount,
       supplierSite.auto_calculate_interest_flag
       Create_Interest_Invoices,
       supplierSite.attribute_category
       Attribute_Category,
       supplierSite.pay_group_lookup_code,
       (SELECT ( GCC1.segment1
                 ||'.'
                 ||GCC1.segment2
                 ||'.'
                 ||GCC1.segment3
                 ||'.'
                 ||GCC1.segment4
                 ||'.'
                 ||GCC1.segment5
                 ||'.'
                 ||GCC1.segment6
                 ||'.'
                 ||GCC1.segment7
                 ||'.'
                 ||GCC1.segment8
                 ||'.'
                 ||GCC1.segment9 )
        FROM   poz_site_assignments_all_m billpayable,
               gl_code_combinations GCC1
        WHERE  supplierSite.vendor_site_id = billpayable.vendor_site_id
               AND billpayable.accts_pay_code_combination_id =
                   GCC1.code_combination_id
       )
       LIABILITY_DISTRIBUTION,
       (SELECT ( GCC1.segment1
                 ||'.'
                 ||GCC1.segment2
                 ||'.'
                 ||GCC1.segment3
                 ||'.'
                 ||GCC1.segment4
                 ||'.'
                 ||GCC1.segment5
                 ||'.'
                 ||GCC1.segment6
                 ||'.'
                 ||GCC1.segment7
                 ||'.'
                 ||GCC1.segment8
                 ||'.'
                 ||GCC1.segment9 )
        FROM   poz_site_assignments_all_m billpayable,
               gl_code_combinations GCC1
        WHERE  supplierSite.vendor_site_id = billpayable.vendor_site_id
               AND billpayable.future_dated_payment_ccid =
                   GCC1.code_combination_id)
                              BILLS_PAYABLE_DISTRIBUTION,
       (SELECT default_relationship_flag
        FROM   poz_sup_thirdparty_payment_rel
        WHERE  vendor_site_id = supplierSite.vendor_site_id)
       Default_Relationship_Flag,
       supplierSite.invoice_amount_limit
       INVOICE_AMOUNT_LIMIT,
       supplierSite.invoice_currency_code
       INVOICE_CURRENCY,
       supplierSite.terms_date_basis
       TERMS_DATE_BASIS,
       supplierSite.pay_date_basis_lookup_code
       PAY_DATE_BASIS,
       supplierSite.qty_rcv_tolerance
       QTY_TOLERANCE,
       supplierSite.exclude_freight_from_discount
       EXCLUDE_FREIGHT_FROM_DISCOUNT,
       supplierSite.always_take_disc_flag
       ALWAYS_TAKE_DISC,
       (SELECT assALL.awt_group_id
        FROM   poz_site_assignments_all_m assALL
        WHERE  suppMaster.party_id = partySite.party_id
               AND partySite.party_site_id = supplierSite.party_site_id
               AND supplierSite.vendor_site_id = assALL.vendor_site_id)
       WITHHOLDING_TAX_GROUP,
       (SELECT distSet.distribution_set_name
        FROM   poz_site_assignments_all_m assALL,
               ap_distribution_sets_all distSet
        WHERE  suppMaster.party_id = partySite.party_id
               AND partySite.party_site_id = supplierSite.party_site_id
               AND supplierSite.vendor_site_id = assALL.vendor_site_id
               AND assALL.distribution_set_id = distSet.distribution_set_id)
       DISTRIBUTION_SET,
       (SELECT location_language
        FROM   poz_supplier_address_v
        WHERE  suppMaster.party_id = partySite.party_id
               AND partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       LOCATION_LANGUAGE,
       partySite.attribute1,
       partySite.attribute5,
       partySite.attribute17
       GLOBAL_ATTRIBUTE2,
       partySite.attribute18
       GLOBAL_ATTRIBUTE3,
       partySite.attribute19
       GLOBAL_ATTRIBUTE4,
       partySite.attribute20
       GLOBAL_ATTRIBUTE5,
       To_char(partySite.attribute_date1, 'DD-MM-YYYY HH24:MI:SS')
       GLOBAL_ATTRIBUTE6,
       To_char(partySite.attribute_date2, 'DD-MM-YYYY HH24:MI:SS')
       GLOBAL_ATTRIBUTE7,
       To_char(partySite.attribute_date3, 'DD-MM-YYYY HH24:MI:SS')
       GLOBAL_ATTRIBUTE8,
       To_char(partySite.end_date_active, 'DD-MM-YYYY HH24:MI:SS')
       ADDRESS_INACTIVE_DATE,
       partySite.status
       ADDRESS_STATUS,
       supplierSite.attribute1
       TRANSPORTER,
       Nvl((SELECT Listagg(
           Concat(Concat(suppMaster.vendor_id, '_'), Concat(file_name,
                       Concat('#', fdt.description))), ';')
                     within GROUP(ORDER BY url) AS the_list
            FROM   fnd_documents_tl fdt,
                   fnd_attached_documents fad,
                   fnd_documents fd
            WHERE  fad.document_id = fdt.document_id
                   AND fad.pk1_value = To_char(supplierSite.vendor_site_id)
                   AND fad.entity_name = 'POZ_SUPPLIER_SITES_ALL_M'
                   AND fd.document_id = fdt.document_id
                   AND Upper(fd.datatype_code) = 'FILE'), 'XXX')
       Site_AttachmentsFileName,
       Nvl((SELECT Listagg(fdc.user_name, ';')
                     within GROUP(ORDER BY url) AS the_list
            FROM   fnd_documents_tl fdt,
                   fnd_attached_documents fad,
                   fnd_documents fd,
                   fnd_document_categories_vl fdc
            WHERE  fad.document_id = fdt.document_id
                   AND pk1_value = To_char(supplierSite.vendor_site_id)
                   AND entity_name = 'POZ_SUPPLIER_SITES_ALL_M'
                   AND fd.document_id = fdt.document_id
                   AND Upper(fd.datatype_code) = 'FILE'
                   AND fad.category_name = fdc.category_name), 'XXX')
       SITESCATEGORY,
       (SELECT address_purpose_ordering
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_ORDERING,
       (SELECT address_purpose_remit_to
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_REMITTO,
       (SELECT address_purpose_rfq_or_bidding
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_RFQ_BIDDING,
       (SELECT status
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_STATUS,
       (SELECT email_address
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_EMAIL,
       (SELECT phone_area_code
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_PHONE_AREACODE,
       (SELECT phone_number
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_PHONE,
       (SELECT fax_phone_area_code
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_FAX_AREACODE,
       (SELECT fax_phone_number
        FROM   poz_supplier_address_v
        WHERE  partySite.party_site_id = party_site_id
               AND ROWNUM = 1)
       ADDRESSPUR_FAX
FROM   poz_suppliers suppMaster,
       hz_party_sites partySite,
       poz_supplier_sites_all_m supplierSite,
       hz_parties parties,
       hz_locations addressDetail
WHERE  suppMaster.party_id = parties.party_id
       AND partySite.location_id = addressDetail.location_id
       AND ( partySite.attribute2 = 'ACTIVE'
              OR partySite.attribute2 = 'GSTIN NOT AVAILABLE' )
       AND ( partySite.attribute4 = 'ACTIVE'
              OR partySite.attribute4 = 'PAN NOT AVAILABLE' )
       AND ( partySite.attribute17 IS NOT NULL
             AND partySite.attribute18 IS NOT NULL )
       AND partySite.party_site_id = supplierSite.party_site_id
       AND suppMaster.party_id = partySite.party_id 

---------------------------------

SupplierContactData

SELECT 'SupplierContact',
       suppMaster.segment1                                       SUPPLIER_NUMBER
       ,
       suppMaster.party_id
       PARTY_ID,
       partySite.party_site_id                                   PARTY_SITE_ID,
       suppMaster.vendor_id                                      VENDOR_ID,
       supplierSite.vendor_site_id                               VENDOR_SITE_ID,
       suppliersite.vendor_site_code
       VENDOR_SITE_CODE,
       parties.person_title                                      NAME_PREFIX,
       parties.person_first_name
       PERSON_FIRST_NAME,
       parties.person_middle_name
       PERSON_MIDDLE_NAME,
       parties.person_last_name
       PERSON_LAST_NAME,
       orgContact.job_title                                      JOB_TITLE,
       parties.phone_country_code
       PHONE_COUNTRY_CODE,
       parties.phone_area_code                                   PHONE_AREA_CODE
       ,
       parties.phone_number                                      PHONE,
       parties.phone_extension                                   PHONE_EXTENSION
       ,
       parties.mobile_country_code
       MOBILE_COUNTRY_CODE,
       parties.mobile_area_code
       MOBILE_AREA_CODE,
       parties.mobile_number                                     MOBILE,
       parties.email_address                                     EMAIL,
       parties.fax_number                                        FAX,
       parties.status                                            STATUS,
       (SELECT primary_flag
        FROM   hz_contact_points
        WHERE  suppContacts.per_party_id = owner_table_id
               AND contact_point_type = 'EMAIL'
               AND parties.relationship_id = relationship_id
               AND rownum = 1)
       Administrative_Contact,
       (SELECT operatingUnit.NAME
        FROM   poz_site_assignments_all_m assALL,
               hr_operating_units operatingUnit
        WHERE  suppMaster.party_id = partySite.party_id
               AND partySite.party_site_id = supplierSite.party_site_id
               AND supplierSite.vendor_site_id = assALL.vendor_site_id
               AND assALL.bu_id = operatingUnit.organization_id) OPERATING_UNIT,
       (SELECT Decode(user_guid, NULL, 'N',
                                 'Y')
        FROM   per_users
        WHERE  parties.per_party_id = party_id)
       HAS_USER_ACCOUNT,
       CASE
         WHEN Upper(parties.status) = 'ACTIVE' THEN
         To_char(suppContacts.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
         ELSE To_char(parties.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
       END                                                       INACTIVE_DATE
FROM   poz_all_supplier_contacts_v parties,
       poz_supplier_contacts suppContacts,
       poz_suppliers suppMaster,
       hz_party_sites partySite,
       poz_supplier_sites_all_m supplierSite,
       hz_org_contacts orgContact
WHERE  suppMaster.party_id = parties.sup_party_id
       AND suppMaster.party_id = partySite.party_id
       AND partySite.party_site_id = supplierSite.party_site_id
       AND partySite.party_site_id = suppContacts.party_site_id
       AND parties.per_party_id = suppContacts.per_party_id
       AND suppContacts.org_contact_id = orgContact.org_contact_id 

-----------------------------

Supplier Payment

SELECT 'SupplierPayments',
       suppMaster.party_id,
       suppMaster.segment1,
       externalPayeeBank.party_site_id
       PARTY_SITE_ID,
       (SELECT vendor_site_code
        FROM   poz_supplier_sites_all_m
        WHERE  externalPayeeBank.supplier_site_id = vendor_site_id)
       VENDOR_SITE_CODE,
       Decode (externalPayeeBank.party_site_id, NULL, NULL,
       (SELECT DISTINCT vendor_site_code
        FROM   poz_supplier_sites_all_m
        WHERE
       party_site_id = externalPayeeBank.party_site_id
       AND rownum = 1
       AND externalPayeeBank.supplier_site_id IS NULL))
       PARTY_SITE_CODE
       /*,(SELECT operatingUnit.NAME 
        FROM POZ_SITE_ASSIGNMENTS_ALL_M assALL
             ,HR_OPERATING_UNITS        operatingUnit
       ,HZ_PARTY_SITES            partySite
       ,POZ_SUPPLIER_SITES_ALL_M        supplierSite
             WHERE suppMaster.PARTY_ID         = partySite.PARTY_ID 
         AND partySite.PARTY_SITE_ID     = supplierSite.PARTY_SITE_ID 
       AND supplierSite.VENDOR_SITE_ID = assALL.VENDOR_SITE_ID  
       AND assALL.BU_ID = operatingUnit.ORGANIZATION_ID )                             OPERATING_UNIT*/
       ,
       (SELECT NAME
        FROM   hr_operating_units operUnit,
               poz_supplier_sites_all_m suppSites
        WHERE  externalPayeeBank.supplier_site_id = suppSites.vendor_site_id
               AND suppSites.prc_bu_id = organization_id)
       OPERATING_UNIT,
       externalPayeeBank.ext_payee_id
       PAYEE_IDENTIFIER,
       externalPayeeBank.delivery_channel_code
       DELIVERY_CHANNEL_CODE,
       externalPayeeBank.bank_instruction1_code
       BANK_INSTRUCTION1_CODE,
       externalPayeeBank.bank_instruction2_code
       BANK_INSTRUCTION2_CODE,
       externalPayeeBank.bank_instruction_details
       BANK_INSTRUCTION_DETAILS,
       externalPayeeBank.settlement_priority
       SETTLEMENT_PRIORITY,
       externalPayeeBank.payment_text_message1
       PAYMENT_TEXT_MESSAGE1,
       externalPayeeBank.payment_text_message2
       PAYMENT_TEXT_MESSAGE2,
       externalPayeeBank.payment_text_message3
       PAYMENT_TEXT_MESSAGE3,
       externalPayeeBank.service_level_code
       SERVICE_LEVEL_CODE,
       externalPayeeBank.exclusive_payment_flag
       PAY_EACH_DOCUMENT_ALONE,
       externalPayeeBank.bank_charge_bearer
       BANK_CHARGE_BEARER,
       externalPayeeBank.payment_reason_code
       PAYMENT_REASON_CODE,
       externalPayeeBank.payment_reason_comments
       PAYMENT_REASON_COMMENTS,
       externalPayeeBank.remit_advice_delivery_method
       DELIVERY_METHOD,
       externalPayeeBank.remit_advice_email
       REMITTANCE_MAIL,
       externalPayeeBank.remit_advice_fax
       REMITTANCE_FAX,
       (SELECT payment_method_code
        FROM   iby_ext_party_pmt_mthds
        WHERE  externalPayeeBank.ext_payee_id = ext_pmt_party_id
               AND primary_flag = 'Y')
       PAYMENT_METHOD_CODE,
       To_char(externalPayeeBank.end_date, 'DD-MM-YYYY HH24:MI:SS') END_DATE
FROM   poz_suppliers suppMaster,
       iby_external_payees_all externalPayeeBank
WHERE  suppMaster.party_id = externalPayeeBank.payee_party_id 

-------------------------------------

SupplierProductServices

SELECT 'SupplierProductServices',
       suppMaster.party_id,
       suppMaster.segment1,
       (SELECT UNIQUE(category_name)
        FROM   poz_prod_serv_category_v
        WHERE  suppProdServ.category_id = category_id
               AND category_name IS NOT NULL)Category_Name,
       suppProdServ.status                   Status
FROM   poz_suppliers suppMaster,
       poz_sup_products_services suppProdServ
WHERE  suppMaster.vendor_id = suppProdServ.vendor_id 

----------------------------

SupplierBusinessClassification

SELECT 'SupplierBusinessClassification',
       suppMaster.segment1,
       suppMaster.party_id,
       busClass.lookup_code
       Classification_Lookup_Code,
       busClass.ext_attr_1
       SubClassification,
       (SELECT name
        FROM   poz_certifying_agencies
        WHERE  busClass.certifying_agency_id = agency_id)
       Certifying_Agency,
       busClass.certificate_number
       Certificate_Number,
       To_char(busClass.start_date, 'DD-MM-YYYY HH24:MI:SS')          Start_Date
       ,
       To_char(busClass.expiration_date, 'DD-MM-YYYY HH24:MI:SS')
       Expiration_Date
       /*,To_char(busClass.confirmed_on, 'DD-MM-YYYY HH24:MI:SS')
                           Confirmed_On*/
       ,
       busClass.notes                                                 Notes,
       busClass.status                                                Status,
       busClass.classification_id
       CLASSIFICATION_ID,
       Nvl((SELECT Listagg(
           Concat(Concat(suppMaster.vendor_id, '_'), Concat(file_name,
                       Concat('#', fdt.description))), ';')
                     within GROUP(ORDER BY url) AS the_list
            FROM   fnd_documents_tl fdt,
                   fnd_attached_documents fad,
                   fnd_documents fd
            WHERE  fad.document_id = fdt.document_id
                   AND fad.pk1_value = To_char(busClass.classification_id)
                   AND fad.entity_name = 'POZ_BUS_CLASSIFICATIONS'
                   AND fd.document_id = fdt.document_id
                   AND Upper(fd.datatype_code) = 'FILE'), 'XXX')
       BUSCLASS_ATTACHMENTSFILENAME,
       Nvl((SELECT Listagg(fdc.user_name, ';')
                     within GROUP(ORDER BY url) AS the_list
            FROM   fnd_documents_tl fdt,
                   fnd_attached_documents fad,
                   fnd_documents fd,
                   fnd_document_categories_vl fdc
            WHERE  fad.document_id = fdt.document_id
                   AND pk1_value = To_char(busClass.classification_id)
                   AND entity_name = 'POZ_BUS_CLASSIFICATIONS'
                   AND fd.document_id = fdt.document_id
                   AND Upper(fd.datatype_code) = 'FILE'
                   AND fad.category_name = fdc.category_name), 'XXX')
       BUSCLASSCATEGORY
FROM   poz_suppliers suppMaster,
       poz_bus_classifications busClass
WHERE  suppMaster.party_id = busClass.party_id
       AND busClass.deleted = 'N' 

-------------------------------------

SupplierBanks

SELECT 'SupplierBank',
       suppMaster.party_id,
       suppMaster.segment1
       SUPPLIER_NUMBER,
       externalPayeeBank.party_site_id
       PARTY_SITE_ID,
       (SELECT vendor_site_code
        FROM   poz_supplier_sites_all_m
        WHERE  externalPayeeBank.supplier_site_id = vendor_site_id)
       VENDOR_SITE_CODE,
       Decode (externalPayeeBank.party_site_id, NULL, NULL,
       (SELECT DISTINCT vendor_site_code
        FROM   poz_supplier_sites_all_m
        WHERE
       party_site_id = externalPayeeBank.party_site_id
       AND rownum = 1
       AND externalPayeeBank.supplier_site_id IS NULL))
       PARTY_SITE_CODE,
       externalPayeeBank.ext_payee_id
       PAYEE_IDENTIFIER,
       bankAccount.country_code                                     COUNTRY_CODE
       ,
       banks.bank_name
       BANK_NAME,
       branch.bank_branch_name                                      BRANCH_NAME,
       branch.branch_number
       BRANCH_NUMBER,
       (SELECT NAME
        FROM   hr_operating_units operUnit,
               poz_supplier_sites_all_m suppSites
        WHERE  externalPayeeBank.supplier_site_id = suppSites.vendor_site_id
               AND suppSites.prc_bu_id = organization_id)
       BUSINESS_UNIT_NAME,
       branch.eft_swift_code
       /*,account.MASKED_IBAN*/
       ,
       To_char(bankAccount.start_date, 'DD-MM-YYYY HH24:MI:SS')
       ACCOUNT_START_DATE,
       To_char(bankAccount.end_date, 'DD-MM-YYYY HH24:MI:SS')
       ACCOUNT_END_DATE,
       bankAccount.bank_account_name                                ACCOUNT_NAME
       ,
       bankAccount.bank_account_name_alt
       ACCOUNT_ALTERNATE_NAME,
       bankAccount.bank_account_num                                 ACCOUNT_NUM,
       bankAccount.currency_code
       CURRENCY_CODE,
       bankAccount.iban                                             IBAN,
       bankAccount.bank_account_type
       ACCOUNT_TYPE_CODE,
       bankAccount.ext_bank_account_id
       Payee_Bank_Account_Identifier,
       bankAccount.description
       Account_Description,
       pmtInstrBank.instrument_payment_use_id
       PAYEE_BANK_ACCOUNT_ASSI_ID,
       pmtInstrBank.primary_flag                                    PRIMARY_FLAG
       ,
       To_char(pmtInstrBank.start_date, 'DD-MM-YYYY HH24:MI:SS')
       ACCOUNT_ASSIGNMENT_START_DATE,
       To_char(pmtInstrBank.end_date, 'DD-MM-YYYY HH24:MI:SS')
       ACCOUNT_ASSIGNMENT_END_DATE,
       bankAccount.agency_location_code
       AGENCY_LOCATION_CODE
FROM   poz_suppliers suppMaster,
       iby_external_payees_all externalPayeeBank,
       iby_pmt_instr_uses_all pmtInstrBank,
       iby_ext_bank_accounts bankAccount,
       ce_index_banks banks,
       ce_index_bank_branches branch
WHERE  externalPayeeBank.ext_payee_id = pmtInstrBank.ext_pmt_party_id
       AND pmtInstrBank.instrument_id = bankAccount.ext_bank_account_id
       AND bankAccount.bank_id = banks.bank_party_id
       AND bankAccount.branch_id = branch.branch_party_id 

----------------------------------------------------

SupplierContacts

SELECT 'SupplierContacts',
       suppMaster.segment1
       SUPPLIER_NUMBER,
       suppMaster.party_id
       PARTY_ID,
       suppMaster.vendor_id
       VENDOR_ID,
       parties.person_title
       NAME_PREFIX,
       parties.person_first_name
       PERSON_FIRST_NAME,
       parties.person_middle_name
       PERSON_MIDDLE_NAME,
       parties.person_last_name
       PERSON_LAST_NAME,
       (SELECT orgContact.job_title
        FROM   hz_org_contacts orgContact
        WHERE  parties.relationship_id = orgContact.party_relationship_id)
       JOB_TITLE,
       parties.phone_country_code
       PHONE_COUNTRY_CODE,
       parties.phone_area_code
       PHONE_AREA_CODE,
       parties.phone_number                                                PHONE
       ,
       parties.phone_extension
       PHONE_EXTENSION,
       parties.mobile_country_code
       MOBILE_COUNTRY_CODE,
       parties.mobile_area_code
       MOBILE_AREA_CODE,
       parties.mobile_number
       MOBILE,
       parties.email_address                                               EMAIL
       ,
       parties.fax_number
       FAX,
       parties.status
       STATUS,
       (SELECT contcPoints.primary_flag
        FROM   hz_contact_points contcPoints
        WHERE  parties.per_party_id = contcPoints.owner_table_id
               AND contcPoints.contact_point_type = 'EMAIL'
               AND parties.relationship_id = relationship_id)
       Administrative_Contact,
       (SELECT Decode(user_guid, NULL, 'N',
                                 'Y')
        FROM   per_users
        WHERE  parties.per_party_id = party_id)
       HAS_USER_ACCOUNT,
       'Y'
       VENDOR_CONTACT_FLAG,
       To_char(parties.inactive_date, 'DD-MM-YYYY HH24:MI:SS')
       INACTIVE_DATE
FROM   poz_suppliers suppMaster,
       poz_all_supplier_contacts_v parties
WHERE  suppMaster.party_id = parties.sup_party_id
       AND parties.per_party_id NOT IN (SELECT per_party_id
                                        FROM   poz_supplier_contacts
                                        WHERE  party_id = parties.sup_party_id)