LEFT OUTER JOIN com varias tabelas e condições.

[weslley.castilho]

Tenho o código abaixo que traz informações sobre notas pagas e não e também valores pagos e data de pagamento em AP, porém existe um erro e preciso de ajuda para corrigir.

O código a seguir traz todas as notas lançadas na tabela AP_INVOICES_ALL e seus respectivos relacionamentos com as tabelas mencionadas na através do campo (INVOICE_ID).

Selecionar tudo

FROM AP_INVOICES_ALL AIA 
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA USING (INVOICE_ID)
LEFT OUTER JOIN AP_CHECKS_ALL ACA USING (CHECK_ID),

Traz todos os registros em AP_INVOICES_ALL AIA (esquerda) tendo ou não dados nas tabelas relacioandas abaixo com o campo invoice_id:

Selecionar tudo

AP_PAYMENT_SCHEDULES_ALL APSA
AP_INVOICE_PAYMENTS_ALL 
ap_invoice_distributions_all AIDA
AP_CHECKS_ALL ACA 

Até ai tudo certo o relacionamento é sempre 1 INVOICE_ID) para 01 ou mais (muitos):

Selecionar tudo

AP_PAYMENT_SCHEDULES_ALL APSA -- Agerndado
AP_INVOICE_PAYMENTS_ALL AIPA -- Pagamento
ap_invoice_distributions_all AIDA -- Centro de custos / Contas  e Aprovações
AP_CHECKS_ALL ACA - Dados do Pagamento

O problema é que na tabela AP_INVOICE_PAYMENTS_ALL AIPA existe pagamentos válidos e não validos (estornados) e não quero pegar os estornados. Mas se colocar esse comando na claúsula (WHERE)

Selecionar tudo

AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)  LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID)

só pego os pagamentos reais

Selecionar tudo

AIPA.REVERSAL_FLAG='Y'

e se na linha só tiver estornados não traz o registro (linha) em AP_INVOICE_ALL devido a tabela declarada

Resumidamente o campo AIPA.REVERSAL_FLAG tem 3 opções:
NULO, N ou Y, quero o Y, porem se não tiver nenhum Y não aparece a tebela devido ao LEFT OUTER JOIN:

Selecionar tudo

 LEFT (AP_INVOICE_ALL  CAMPO INVOICE_ID)

Como faço para corrigir ?


Abaixo o código

Selecionar tudo

SELECT DISTINCT 
INVOICE_ID,
TO_CHAR(AIA.creation_date,'DD/MM/YYYY')    AS Creation_date,
usr.user_name                              AS user_name,
TO_CHAR(AIA.Last_update_date,'DD/MM/YYYY') AS last_update_date,
usrup.user_name                            AS user_processor,
AIA.wfapproval_status                      AS approval_status,
DECODE (UPPER(AIDA.match_status_flag),'A','VALIDATION','N','NEEDS VALIDATION','NEVER VALIDATION')   AS inv_status,
DECODE (UPPER(AIA.invoice_type_lookup_code),'STANDARD', 'padrão', 'AWT', 'RETENCAO DE IMPOSTOS',
'INTEREST', 'JUROS', 'MIXED', 'MISTO', 'PREPAYMENT', 'PAGAMENTO ANTECIPADO',
'QUICKDEFAULT', 'DEFAULT PO', 'QUICKMATCH', 'VINCULACAO RAPIDA', 'CREDIT','AVISO DE CREDITO',
'DEBIT','AVISO DE DEBITO','EXPENSE REPORT','RELATORIO DE DESPESAS') AS invoice_type,
DECODE (UPPER(AIA.payment_status_flag),'Y','PAID','N','NOT PAID','P','PARTIAL PAID') AS Status,
GSOB.SHORT_NAME                                            AS CARRIER,
HOU.NAME                                                   AS ORG_NAME,
(PVSA.global_attribute10|| PVSA.global_attribute11|| PVSA.global_attribute12 )AS CNPJ,
PV.segment1                                        AS SUPPLIER_CODE,
PV.vendor_name                                     AS SUPPLIER_NAME,
PVSA.vendor_site_code                              AS SITE,
AIA.invoice_num                                     AS INVOICE_NUMBER,
TO_CHAR(AIA.invoice_date,'DD/MM/YYYY')              AS INVOICE_DATE,
TO_CHAR(AIA.terms_date, 'DD/MM/YYYY')               AS TERMS_DATE,
ATL.due_days                                        AS DAYS,
TO_CHAR(AIA.terms_date + ATL.due_days,'DD/MM/YYYY') AS DUE_DATE,
AIA.invoice_currency_code                           AS CURRENCY,
NVL (AIA.invoice_amount, 0)                         AS GROSS_AMOUNT,
NVL (AIA.amount_paid, 0)                            AS AMOUNT_PAID,
(NVL(AIA.invoice_amount, 0) - NVL (AIA.amount_paid, 0)) AS BALANCE,
APSA.PAYMENT_PRIORITY                               AS PRIORITY,
APSA.payment_method_lookup_code 					AS PAYMENT_METHOD,
AIA.pay_group_lookup_code 							AS PAY_GROUP,
ABA.BATCH_NAME                                      AS BATCH_NAME,
NVL(AIPA.AMOUNT,0)                                  AS PAGO,
TO_CHAR(aca.check_date,'DD/MM/YYYY')                AS payment_date,
AIPA.REVERSAL_FLAG, 
ACA.AMOUNT AS CHECK_AMOUNT, 
ACA.CHECK_NUMBER, 
ACA.BANK_ACCOUNT_NAME, 
ACA.BANK_ACCOUNT_NUM,  
ACA.STATUS_LOOKUP_CODE
FROM AP_INVOICES_ALL AIA 
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA USING (INVOICE_ID)
LEFT OUTER JOIN AP_CHECKS_ALL ACA USING (CHECK_ID),
FND_USER USR,
FND_USER USRUP,
GL_SETS_OF_BOOKS GSOB,
HR_ORGANIZATION_UNITS HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_TERMS ATE,
AP_TERMS_LINES ATL,
AP_BATCHES_ALL ABA
WHERE invoice_id in (3740338,3799833,3799838,3821310,3821311,4575435,4613524)
AND AIA.created_by      = USR.user_id
AND AIA.last_updated_by = USRUP.user_id
AND AIA.SET_OF_BOOKS_ID=GSOB.SET_OF_BOOKS_ID
AND AIA.ORG_ID=HOU.ORGANIZATION_ID
AND AIA.vendor_id       =PV.vendor_id
AND AIA.vendor_site_id  =PVSA.vendor_site_id
AND AIA.terms_id        =ATE.term_id
AND ATE.term_id         =ATL.term_id
AND AIA.batch_id        =ABA.batch_id
AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
AND AIA.pay_group_lookup_code IN ('NATIONAL', 'BOLETO','ZERO PAY')
ORDER BY AIA.invoice_num;

Veja os históricos que levaram a tal dúvida em um forum sobre ERP (Aprovação em AP).
http://glufke.net/oracle/viewtopic.php?p=29997#29997

[burga]

Quanto à sua dúvida, a solução é a seguinte:

Ao invés de jogar a condição

Selecionar tudo

AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL) 

na cláusula WHERE, use na cláusula FROM juntamente com a ON, desta forma:

Selecionar tudo

FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA 
  ON AIA.INVOICE_ID = AIPA.INVOICE_ID 
    AND (AIPA.REVERSAL_FLAG='N' 
      OR AIPA.REVERSAL_FLAG IS NULL)

Algo do tipo.





MAS, eu fiquei com uma dúvida na sua lógica:

Mas se colocar esse comando na claúsula (WHERE)

Selecionar tudo

AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)  LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID)

só pego os pagamentos reais

Selecionar tudo

AIPA.REVERSAL_FLAG='Y'

e se na linha só tiver estornados não traz o registro (linha) em AP_INVOICE_ALL devido a tabela declarada

Esta afirmação está bem estranha (pra não dizer incorreta)... Se você está usando essas condições:

Selecionar tudo

AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL

como você pega AIPA.REVERSAL_FLAG='Y'??

Se você quer os AIPA.REVERSAL_FLAG='Y', então coloque direto essa condição... Essa parte ficou um pouco confusa.

[weslley.castilho]

Selecionar tudo

FROM AP_INVOICES_ALL AIA 
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID) 
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA 
  ON AIA.INVOICE_ID = AIPA.INVOICE_ID 
    AND (AIPA.REVERSAL_FLAG='N' 
      OR AIPA.REVERSAL_FLAG IS NULL) 

Colega obrigado, não posso usar o ON devido os campos serem identicos, alterei para o using (invoice_id) a coloquei a condição da tabela AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL) me retorna erro :
ORA-00933: SQl command no properly ended.

Abaixo parte do código alterado:

Selecionar tudo

FROM AP_INVOICES_ALL AIA 
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID) AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA USING (INVOICE_ID)
LEFT OUTER JOIN AP_CHECKS_ALL ACA USING (CHECK_ID),

Lembrando que as condições de LEFT OUTER JOIN são com 4 tabelas.

quanto a citação abaixo, o erro foi em minha expressão:

Citação:
Mas se colocar o comando --AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL) na claúsula (WHERE) só pego os pagamentos reais AIPA.REVERSAL_FLAG='Y'

o Correto é:

Selecionar tudo

AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL) 

não traz 'Y' e AIPA.REVERSAL_FLAG='Y' não traz NULL e N.

Desculpe

[burga]

Colega obrigado, não posso usar o ON devido os campos serem identicos

O que é idêntico nos campos? O nome? Se sim, qual a relação disto com não usar a cláusula ON?

Não vejo problema algum que faça com que algo semelhante ao código abaixo não funcione.

Selecionar tudo

FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA ON AIA.INVOICE_ID = APSA.INVOICE_ID
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIPA.INVOICE_ID = AIA.INVOICE_ID AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA ON AIDA.INVOICE_ID = AIA.INVOICE_ID
LEFT OUTER JOIN AP_CHECKS_ALL ACA ON ACA.CHECK_ID = ...

[weslley.castilho]

Meu caro boa tarde,

Selecionar tudo

LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIPA.INVOICE_ID = AIA.INVOICE_ID AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)

não posso usar esse código devido ao erro :

ORA-25154: column part of USING clause cannot have qualifiter

O erro é devido ao campo invoice_id ser referenciado em várias tabelas e ser o mesno nome de campo em todas tabelas.

Porem também não vejo diferença entre usar ON ou USING ().

Portanto acredito que o

Selecionar tudo

AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)

no comando FROM que não seja viável.

Mas não posso afirmar, isso eu deduzo.

[burga]

O erro ORA-25154: column part of USING clause cannot have qualifiter ocorre quando você usa a cláusula USING, troque TODOS OS JOINS por ON.

No exemplo que postei, com TODOS os JOINS utilizando a cláusula ON, não deve ocorrer este erro... E, como postei, utilizando o ON, você consegue estipular as condições de junção que necessita, que não consegue usando o USING.

Outra coisa, com o ON, você consegue utilizar os ALIASES que colocou para os nomes das tabelas, e assim elimitar o problema de colunas com mesmo nome entre as tabelas. Justamente por causa disto não vejo problemas.

As diferenças de se utilizar USING ou ON existem, estas que coloquei são algumas delas.

[weslley.castilho]

Meu amigo funcionou. troquei todos os USING por ON e renomeiei as colunas via ALIASE , abaixo o código final.

Selecionar tudo

SELECT DISTINCT 
AIA.INVOICE_ID AS ID_AP,
TO_CHAR(AIA.creation_date,'DD/MM/YYYY')    AS CREATION_DATE,
usr.user_name                              AS USER_NAME,
TO_CHAR(AIA.Last_update_date,'DD/MM/YYYY') AS LAST_UPDATE_DATE,
usrup.user_name                            AS USER_PROCESSOR,
AIA.wfapproval_status                      AS APPROVAL_STATUS,
DECODE (UPPER(AIDA.match_status_flag),'A','VALIDATION','N','NEEDS VALIDATION','NEVER VALIDATION')   AS INVOICE_STATUS,
DECODE (UPPER(AIA.invoice_type_lookup_code),'STANDARD', 'padrão', 'AWT', 'RETENCAO DE IMPOSTOS',
'INTEREST', 'JUROS', 'MIXED', 'MISTO', 'PREPAYMENT', 'PAGAMENTO ANTECIPADO',
'QUICKDEFAULT', 'DEFAULT PO', 'QUICKMATCH', 'VINCULACAO RAPIDA', 'CREDIT','AVISO DE CREDITO',
'DEBIT','AVISO DE DEBITO','EXPENSE REPORT','RELATORIO DE DESPESAS') AS INVOICE_TYPE,
DECODE (UPPER(AIA.payment_status_flag),'Y','PAID','N','NOT PAID','P','PARTIAL PAID') AS STATUS,
GSOB.SHORT_NAME                                            AS CARRIER,
HOU.NAME                                                   AS ORG_NAME,
(PVSA.global_attribute10|| PVSA.global_attribute11|| PVSA.global_attribute12 )AS CNPJ,
PV.segment1                                        AS SUPPLIER_CODE,
PV.vendor_name                                     AS SUPPLIER_NAME,
PVSA.vendor_site_code                              AS SITE,
AIA.invoice_num                                     AS INVOICE_NUMBER,
TO_CHAR(AIA.invoice_date,'DD/MM/YYYY')              AS INVOICE_DATE,
TO_CHAR(AIA.terms_date, 'DD/MM/YYYY')               AS TERMS_DATE,
ATL.due_days                                        AS DAYS,
TO_CHAR(AIA.terms_date + ATL.due_days,'DD/MM/YYYY') AS DUE_DATE,
AIA.invoice_currency_code                           AS CURRENCY,
NVL (AIA.invoice_amount, 0)                         AS GROSS_AMOUNT,
NVL (AIA.amount_paid, 0)                            AS AMOUNT_PAID,
(NVL(AIA.invoice_amount, 0) - NVL (AIA.amount_paid, 0)) AS BALANCE,
APSA.PAYMENT_PRIORITY                               AS PRIORITY,
APSA.payment_method_lookup_code 					          AS PAYMENT_METHOD,
AIA.pay_group_lookup_code 							            AS PAY_GROUP,
ABA.BATCH_NAME                                      AS BATCH_NAME,
NVL(AIPA.AMOUNT,0)                                  AS PAGO,
nvl(TO_CHAR(aca.check_date,'DD/MM/YYYY'),'*')       AS payment_date,
ACA.AMOUNT                                          AS CHECK_AMOUNT,
AIPA.REVERSAL_FLAG,
ACA.CHECK_NUMBER 
FROM AP_INVOICES_ALL AIA 
LEFT OUTER JOIN ap_invoice_distributions_all AIDA ON AIA.INVOICE_ID=AIDA.INVOICE_ID
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA ON AIA.INVOICE_ID=APSA.INVOICE_ID
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIA.INVOICE_ID=AIPA.INVOICE_ID AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
LEFT OUTER JOIN AP_CHECKS_ALL ACA ON AIPA.CHECK_ID=ACA.CHECK_ID,
FND_USER USR,
FND_USER USRUP,
GL_SETS_OF_BOOKS GSOB,
HR_ORGANIZATION_UNITS HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_TERMS ATE,
AP_TERMS_LINES ATL,
AP_BATCHES_ALL ABA
WHERE 
AIA.created_by      = USR.user_id
AND AIA.last_updated_by = USRUP.user_id
AND AIA.SET_OF_BOOKS_ID=GSOB.SET_OF_BOOKS_ID
AND AIA.ORG_ID=HOU.ORGANIZATION_ID
AND AIA.vendor_id       =PV.vendor_id
AND AIA.vendor_site_id  =PVSA.vendor_site_id
AND AIA.terms_id        =ATE.term_id
AND ATE.term_id         =ATL.term_id
AND AIA.batch_id        =ABA.batch_id
AND AIA.pay_group_lookup_code IN ('NATIONAL', 'BOLETO','ZERO PAY')
--AND UPPER(AIA.payment_status_flag)='Y'
--AND AIA.creation_date BETWEEN '01-JAN-2010' AND SYSDATE
AND AIA.invoice_id in (4519715,3740338,3799833,3799838,3821310,3821311,4575435,4613524)
ORDER BY AIA.INVOICE_ID;

Abraço e obrigado