File transfer from Windows to Linux

---CSV file move

The PuTTY Secure Copy client(PSCP), is a tool for transferring files securely between computers using an SSH connection:

First of  Install from https://www.putty.org/
 
Putty installer least version. other wise Set PATH in environment variable
C:\Program Files\PuTTY\;


Open Command Prompt or CMD.

Run the below command :

pscp C:\Users\Documents\AR_INVOICE_UPLOAD.csv appldev@192.99.999.99:/u01/DEV/apps/apps_st/appl/xxcus/12.0.0/bin/AR_INVOICE_UPLOAD.csv

Then csv file transfer from windows to server .

Find the Operating Unit with AR and AP and Company Code and Location

---Find the Operating Unit with AR and AP and Company code and Location


SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.flex_segment_value company_code,
                (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = arsp.org_id) ar_ou,
               (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = apsp.org_id) ap_ou              
  FROM xle_entity_profiles          lep,
       xle_registrations            reg,
       hr_locations_all             hrl,
       hz_parties                   hzp,
       fnd_territories_vl           ter,
       hr_operating_units           hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units        gloperatingunitseo,
       gl_legal_entities_bsvs       glev,
       ar_system_parameters_all     arsp,
       ap_system_parameters_all     apsp
 WHERE lep.transacting_entity_flag        = 'Y'
   AND lep.party_id                       = hzp.party_id
   AND lep.legal_entity_id                = reg.source_id
   AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id                    = reg.location_id
   AND reg.identifying_flag               = 'Y'
   AND ter.territory_code                 = hrl.country
   AND lep.legal_entity_id                = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id          = hro.business_group_id
   AND hroutl_ou.organization_id          = hro.organization_id
   AND glev.legal_entity_id               = lep.legal_entity_id
   AND hro.set_of_books_id = arsp.set_of_books_id(+)
   AND hro.organization_id = arsp.org_id(+)
   AND hro.set_of_books_id = apsp.set_of_books_id(+)
   AND hro.organization_id = apsp.org_id(+)
Order by company_code  

How to find the Supplier extraction with account script for EBS Tax

SELECT     ap.segment1 vendor_code,
           ap.vendor_name Suppliers_Name,
           aps.vendor_site_code Supplier_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS,
           ap.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
           (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
           aps.vat_registration_num,
           aps.auto_tax_calc_flag,
           ap.offset_tax_flag offset_tax_flag_header,
           aps.offset_tax_flag offset_tax_flag_line,
           aps.attribute2 free_field,
           (Select LOCATION_CODE from hr_locations where location_id= aps.ship_to_location_id) ship_location,
           (Select LOCATION_CODE from hr_locations where location_id= aps.bill_to_location_id) bill_location,
           (SELECT terms.NAME
               From ap_terms_tl terms
               WHERE terms.term_id = ap.terms_id
               AND terms.LANGUAGE(+) = USERENV ('LANG')
               AND terms.enabled_flag(+) = 'Y')AS terms_header,
           (SELECT terms.NAME
               From ap_terms_tl terms
               WHERE terms.term_id = aps.terms_id
               AND terms.LANGUAGE(+) = USERENV ('LANG')
               AND terms.enabled_flag(+) = 'Y')AS terms_line,
           ap.attribute9 supplier_category,
            (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=aps.ACCTS_PAY_CODE_COMBINATION_ID) ACCTS_AC,
       (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=aps.PREPAY_CODE_COMBINATION_ID) PRPAY_AC
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           ap_supplier_contacts apc
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
           AND ap.VENDOR_TYPE_LOOKUP_CODE <>  'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND ap.enabled_flag ='Y'
           --AND ap.segment1='19173'
ORDER BY   ap.vendor_name,ap.segment1

Materialized views Example

SET DEFINE OFF;
DROP SYNONYM APPSRO.EMP_SNAPSHOT;


DROP MATERIALIZED VIEW SCOTT.EMP_SNAPSHOT;

CREATE MATERIALIZED VIEW SCOTT.EMP_SNAPSHOT
TABLESPACE SYSTEM
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE SYSTEM
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "EMP";

GRANT SELECT ON  SCOTT.EMP_SNAPSHOT TO APPSRO;

GRANT SELECT ON  SCOTT.EMP_SNAPSHOT TO PUBLIC;

CREATE SYNONYM APPSRO.EMP_SNAPSHOT FOR SCOTT.EMP_SNAPSHOT;

How to Find Currency enabled in Oracle R12

SELECT currency_code, name, description, issuing_territory_code
  FROM fnd_currencies_vl
  WHERE enabled_flag ='Y'