select --aida.*
distinct
'20'||SUBSTR(aia.invoice_date,8) as "Period Year",
aida.period_name,
hr.name as "Operating Unit",
aia.pay_group_lookup_code as "Pay Group",
aps.segment1 as "Supplier Number",
aps.vendor_name "Supplier Name",
aba.batch_id as "Batch ID",
aba.batch_name as "Batch Name",
aia.invoice_date as "Invoice Date",
aia.invoice_num as "Invoice Number",
aia.description as "Invoice Description",
aia.invoice_type_lookup_code "Invoice Type Code",
flv.description as "Invoice Type Description",
aba.invoice_currency_code as "Invoice Currency Code",
gcc.segment1 as "Company",
gcc.segment2 as "Account",
gcc.segment3 as "Department",
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 as "Account String",
aia.invoice_amount as "Invoice Amount",
aia.creation_date "Creation Date",
fu.user_name as "Created By",
aia.last_update_date as "Last Update Date",
fu2.user_name as "Last Updated By",
decode(fad.attached_document_id, null,'N','Y') as "Attachment Flag",
dtl.file_name as "File Name",
AP_INVOICES_PKG.GET_POSTING_STATUS(aia.INVOICE_ID) as "Posting Status"
from AP.AP_INVOICES_ALL aia,
ap.AP_SUPPLIERS aps,
hr_operating_units hr,
AP_INVOICE_DISTRIBUTIONS_ALL aida,
gl_code_combinations gcc,
AP_BATCHES_ALL aba,
fnd_user fu,
fnd_user fu2,
fnd_attached_documents fad,
fnd_documents_tl dtl,
fnd_lookup_values flv
where aia.VENDOR_ID = aps.VENDOR_ID
and aia.ORG_ID = hr.ORGANIZATION_ID
and aia.invoice_id = aida.invoice_id
and aida.dist_code_combination_id = gcc.code_combination_id
and aia.batch_id = aba.batch_id (+)
and aia.created_by = fu.user_id
and aia.invoice_id = fad.pk1_value (+)
AND fad.entity_name (+) = 'AP_INVOICES'
AND fad.document_id = dtl.document_id (+)
and dtl.SOURCE_LANG (+) = 'US'
and aia.last_updated_by = fu2.user_id
and aia.invoice_type_lookup_code = flv.lookup_code
and flv.lookup_type = 'INVOICE TYPE'
AND flv.SOURCE_LANG = 'US'
and aia.creation_date > to_date('01-SEP-18')
and aia.creation_date < to_date('01-JUL-19')
and aida.cancelled_flag is null
and aia.payment_status_flag = 'Y'
and aia.cancelled_by is null
-- and aia.invoice_id = 4992587
;
//cle-zorafile.amtrustservices.com/oracle/prod /work/GMAC cifs user=EBSProd_GMAC
//cle-zorafile.amtrustservices.com/CCP/Interfaces/Prod /work/CCP/Interfaces/Prod cifs user=OracleEBS_AMTU
//cle-zorafile.amtrustservices.com/AMT/ACS/Interfaces/PROD/ODSGL /work/AMT/ACS/Interfaces/amtp/ODSGL cifs user=OracleEBS_AMTU
\\cle-zorafile\AEL\Interfaces\prod /work/AEL/Interfaces/prod cifs user=OracleEBS_AMTU
//cle-zorafile.amtrustservices.com/AMT/VENDOR_LOAD/Prod /work/AMT/VENDOR_LOAD/Prod cifs
//cle-zorafile/AMT/KYRIBA/Interfaces /work/AMT/KYRIBA/Interfaces cifs
\\cle-zorafile\ATL\Interfaces\Prod\GGLData /work/ATL/Interfaces/Prod cifs
\\cle-zorafile\AMT\INSCO\Interfaces\Prod /work/INSCO/Interfaces/Prod cifs
\\cle-zorafile\GMIE\Interfaces\Prod /work/GMIE/Interfaces/Prod cifs
//cle-zorafile.amtrustservices.com/ATL/Interfaces/Prod/GGLData/History /work/ATL/Interfaces/Prod/GGLData/History cifs
//cle-zorafile.amtrustservices.com/ATL/Interfaces/Prod/GGLData/Monthly /work/ATL/Interfaces/Prod/GGLData/Monthly cifs
//cle-zorafile.amtrustservices.com/AEL/Interfaces/Prod/GGLData/History /work/AEL/Interfaces/Prod/GGLData/History cifs
//cle-zorafile.amtrustservices.com/AEL/Interfaces/Prod/GGLData/Monthly /work/AEL/Interfaces/Prod/GGLData/Monthly cifs
//cle-zorafile.amtrustservices.com/AMT/WS/Interfaces/Prod/GGLData/Monthly /work/AMT/WS/Interfaces/Prod/GGLData/Monthly cifs
//cle-zorafile.amtrustservices.com/AMT/WS/Interfaces/Prod/GGLData/Daily /work/AMT/WS/Interfaces/Prod/GGLData/Daily
\\cle-zorafile\AMT\Essbase /work/Essbase cifs user=OracleEBS_AMTU
//cle-zorafile/TECMO /work/TECMO cifs credentials=/etc/fstab.creds.OracleEBS_AMTU
mount --verbose /work/Test
mount.cifs kernel mount options: unc=//cle-backup\EBS_Test_SMB,ip=10.58.50.27,user=OracleEBS_AMTU,ver=1,rw,credentials=/etc/fstab.creds.OracleEBS_AMTU,domain=AMTRUSTSERVICES,uid=5521,gid=501,pass=********
mount error 112 = Host is down
Refer to the mount.cifs(8) manual page (e.g.man mount.cifs)
2) Name: 10.10.10.116 Domain: AMTRUSTSERVICESA Uses: 1 OS: Windows Server 2012 R2 Standard 9600
NOS: Windows Server 2012 R2 Standard 6.3 Capability: 0x1e3fc
SMB session status: 1 TCP status: 1
Local Users To Server: 1 SecMode: 0xf Req On Wire: 0
Shares:
1) \\cle-file03\OracleEBS$ Mounts: 2 Type: NTFS DevInfo: 0x20020 Attributes: 0xc700ff
SELECT 'Supplier Level' Supplier_Level
, sup.vendor_name "Supplier"
, sup.segment1 "Supplier Number"
, 'Suplier Level' "Site Number"
, eba.country_code bank_contry_code
, 'Suplier Level' "Operating Unit"
, 'Suplier Level' "Address ln1"
, 'Suplier Level' "Address ln3"
, 'Suplier Level' "Address ln3"
, 'Suplier Level' "City"
, 'Suplier Level' "Zip"
, pmthds.payment_method_name "Payment Method"
, hpbk3.party_name bank_name
, hpbr3.party_name bank_branch_name
, eba.bank_account_name
, substr(eba.bank_account_num,-4)
, eba.currency_code bank_currency_code
, eba.bank_account_type
, eba.foreign_payment_use_flag bank_foreign_payment_use_flag
, eba.attribute_category bank_attribute_category
FROM ap_suppliers sup
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
, hz_parties hpbk3
, hz_parties hpbr3
, iby_ext_party_pmt_mthds ieppm
, IBY_PAYMENT_METHODS_VL pmthds
WHERE sup.party_id = epa.payee_party_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND hpbk3.party_id = eba.bank_id
AND hpbr3.party_id = eba.branch_id
AND epa.ext_payee_id = ieppm.ext_pmt_party_id (+)
AND pmthds.payment_method_code (+) = ieppm.payment_method_code
--and eba.bank_account_num = '13006687'
AND epa.supplier_site_id IS NULL
AND epa.party_site_id IS NULL
--AND NVL (hpbr3.status, 'A') = 'A'
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
)
AND nvl(ieppm.primary_flag,'Y') = 'Y'
UNION ALL
SELECT DISTINCT 'Supplier Level' Supplier_Level
, sup.vendor_name "Supplier"
, sup.segment1 "Supplier Number"
, 'Suplier Level' "Site Number"
, eba.country_code bank_contry_code
, 'Suplier Level' "Operating Unit"
, 'Suplier Level' "Address ln1"
, 'Suplier Level' "Address ln3"
, 'Suplier Level' "Address ln3"
, 'Suplier Level' "City"
, 'Suplier Level' "Zip"
, NULL "Payment Method"
, hpbk3.party_name bank_name
, hpbr3.party_name bank_branch_name
, eba.bank_account_name
, substr(eba.bank_account_num,-4)
, eba.currency_code bank_currency_code
, eba.bank_account_type
, eba.foreign_payment_use_flag bank_foreign_payment_use_flag
, eba.attribute_category bank_attribute_category
FROM ap_suppliers sup
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
, hz_parties hpbk3
, hz_parties hpbr3
, iby_ext_party_pmt_mthds ieppm
, IBY_PAYMENT_METHODS_VL pmthds
WHERE sup.party_id = epa.payee_party_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND hpbk3.party_id = eba.bank_id
AND hpbr3.party_id = eba.branch_id
AND epa.ext_payee_id = ieppm.ext_pmt_party_id (+)
AND pmthds.payment_method_code (+) = ieppm.payment_method_code
AND epa.supplier_site_id IS NULL
AND epa.party_site_id IS NULL
-- and eba.bank_account_num = '13006687'
--AND NVL (hpbr3.status, 'A') = 'A'
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
)
AND ieppm.primary_flag = 'N'
AND sup.vendor_id not in (
SELECT sup.vendor_id
FROM ap_suppliers sup
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
, hz_parties hpbk3
, hz_parties hpbr3
, iby_ext_party_pmt_mthds ieppm
, IBY_PAYMENT_METHODS_VL pmthds
WHERE sup.party_id = epa.payee_party_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND hpbk3.party_id = eba.bank_id
AND hpbr3.party_id = eba.branch_id
AND epa.ext_payee_id = ieppm.ext_pmt_party_id (+)
AND pmthds.payment_method_code (+) = ieppm.payment_method_code
AND epa.supplier_site_id IS NULL
AND epa.party_site_id IS NULL
AND NVL (hpbr3.status, 'A') = 'A'
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
)
AND nvl(ieppm.primary_flag,'Y') = 'Y'
) UNION ALL SELECT 'Supplier Site Level' Supplier_Level
, sup.vendor_name "Supplier"
, sup.segment1 "Supplier Number"
, ss.vendor_site_CODE "Site Number"
--, eba.ext_bank_account_id bank_ext_bank_acct_id
, eba.country_code bank_contry_code --, decode(ieb3.country_code,'Brasil', 'BR', 'Brazil', 'BR', ieb3.country_code ) bank_contry_code
, hr.name "Operating Unit"
, ss.ADDRESS_LINE1 "Address ln1"
, ss.ADDRESS_LINE2 "Address ln3"
, ss.ADDRESS_LINE3 "Address ln3"
, ss.city "City"
, ss.zip "Zip"
, pmthds.payment_method_name "Payment Method"
, hpbk3.party_name bank_name
, hpbr3.party_name bank_branch_name
, eba.bank_account_name
, substr(eba.bank_account_num,-4)
, eba.currency_code bank_currency_code
, eba.bank_account_type
, eba.foreign_payment_use_flag bank_foreign_payment_use_flag
, eba.attribute_category bank_attribute_category
FROM ap_suppliers sup
, ap_supplier_sites_all ss
, iby_external_payees_all epa
, iby_pmt_instr_uses_all piu
, iby_ext_bank_accounts eba
, hz_parties hpbk3
, hz_parties hpbr3
, hr_operating_units hr
, iby_ext_party_pmt_mthds ieppm
, IBY_PAYMENT_METHODS_VL pmthds
WHERE sup.vendor_id = ss.vendor_id
AND ss.vendor_site_id = epa.supplier_site_id
AND epa.ext_payee_id = piu.ext_pmt_party_id
AND piu.instrument_id = eba.ext_bank_account_id
AND hpbk3.party_id = eba.bank_id
AND hpbr3.party_id = eba.branch_id
-- and eba.bank_account_num = '13006687'
and ss.org_id = hr.organization_id
AND epa.ext_payee_id = ieppm.ext_pmt_party_id (+)
and pmthds.payment_method_code (+) = ieppm.payment_method_code
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
)
AND nvl(ieppm.primary_flag,'Y') = 'Y'
--AND NVL (hpbr3.status, 'A') = 'A'
;
Add Comment