Oracle DB 12.1.0.2
Hello Team,
I am rewriting the sql code below to remove old join style (+) to new join format.
Can you please help me how to remove the '+'?
SELECT sysdate, apps.xxmtg_get_org_name (aip.org_id) operating_unit,
ai.creation_date inv_creation_date, ai.invoice_date,
ai.invoice_received_date Inv_RecvDte,
aba.batch_name, ai.invoice_num,
ai.description invoice_desc, ai.invoice_amount,
aps.payment_priority, aps.due_date,
ac.currency_code, ac.creation_date pmt_creation_date,
apps.X_MTG_get_user_name(ac.created_by) created_by, ac.status_lookup_code,
ac.check_date document_date, ac.check_number,
aip.amount pmt_doc_amount, pd.payment_document_name,
ac.bank_account_name,
ac.VENDOR_NAME Supplier_Name, pov.segment1 SupplierNum,
b.bank_name Supplier_Bank, to_char(b.bank_number) Supplier_Banknum,
br.bank_branch_name Supplier_BankBranch, iby_ac.bank_account_num Supplier_BankAccount,
apps.xxmt_getvendor_emailadd (ai.vendor_site_id) supplier_email,trunc(ac.CREATION_DATE) Payment_Creation_Date,
decode(ac.CURRENCY_CODE, 'MUR',1,ac.exchange_rate) Exchange_Rate,
decode(ac.CURRENCY_CODE,'MUR',ac.AMOUNT, ac.BASE_AMOUNT) Functional_Pmt_Amt
FROM apps.ap_invoice_payments_all aip,
apps.ap_invoices_all ai,
apps.ap_batches_all aba,
apps.ap_checks_all ac, apps.po_vendors pov,
apps.iby_payment_methods_vl iby1,
apps.ap_lookup_codes alc2,
apps.ap_lookup_codes alc3,
apps.ap_payment_schedules_all aps,
apps.ce_payment_documents pd,
apps.iby_ext_banks_v b,
apps.iby_ext_bank_branches_v br,
apps.iby_ext_bank_accounts iby_ac
left join <-- is the left join ok?
(select VEND.vendor_name from apps.po_vendors VEND,apps.po_vendors pov where VEND.vendor_id = pov.PARENT_VENDOR_ID) ParentName on ParentName.vendor_name=ac.VENDOR_NAME
WHERE br.bank_party_id = b.bank_party_id
AND br.bank_party_id = iby_ac.bank_id
AND br.branch_party_id = iby_ac.branch_id
AND iby_ac.EXT_BANK_ACCOUNT_ID = aps.EXTERNAL_BANK_ACCOUNT_ID
AND aip.invoice_id = ai.invoice_id
AND ac.payment_document_id = pd.payment_document_id(+) <-- is this right join?
AND aip.check_id = ac.check_id
AND iby1.payment_method_code(+) = ac.payment_method_code
AND alc2.lookup_type(+) = 'CHECK STATE'
AND alc2.lookup_code(+) = ac.status_lookup_code
AND alc3.lookup_type(+) = 'NLS TRANSLATION'
AND alc3.lookup_code(+) = aip.invoice_payment_type
AND aps.invoice_id = aip.invoice_id
AND aps.payment_num = aip.payment_num
-- AND trunc(ac.CREATION_DATE) >= '&&1'
-- AND trunc(ac.CREATION_DATE) <='&&2'
AND aps.external_bank_account_id IS NOT NULL
AND ai.BATCH_ID = aba.batch_id
AND ac.vendor_id = pov.vendor_id
AND ac.bank_account_name <> 'AP/AR INVOICE SETTLEMENT'
AND status_lookup_code <> 'VOIDED'
Thanks,
Roshan