Consulta aprovaação

Perguntas relacionadas a questões técnicas do Oracle EBS. Criação de Concorrentes, Value Sets, Alerts, Forms Personalizations, Configurações, etc
Responder
weslley.castilho
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 18 Jan 2011 11:50 am
Localização: Itajaí - SC
Weslley Castilho
Skype: weslley.castilho
Msn: weslley.castilho@hotmail.com

Pessoal boa tarde, estou com uma dúvida sobre a aprovação, alguém pode me ajudar ?

O que preciso é pegar a aprovação na tabela distribution (ap_invoice_distributions_all ) e relacionar com a linha do registro em invoices (AP_ INVOICES_ALL).

A tabela de DISTRIBUTION tem um ou mais registros em INVOICES

Selecionar tudo

INVOICES         CAMPO           RELACIONAMENTO
                        INVOICE_ID    01

Selecionar tudo

DISTRIBUTION CAMPO           RELACIONAMENTO
                       INVOICE_ID    N
Ou seja existe um ID da Invoice para Muitos em Distribution e preciso o retornar apenas 01 registro por ID e asim o campo MATCH_STATUS_FLAG em distribution.

Pensei em usar a clausula EXISTS mais esta dando erro vejam abaixo o
código :

Selecionar tudo

SELECT 
AIA.INVOICE_ID AS ID,
CASE 
WHEN UPPER (EXISTS (SELECT XA.MATCH_STATUS_FLAG FROM ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID)='N' THEN 'Never Validated'
WHEN UPPER (EXISTS (SELECT XA.MATCH_STATUS_FLAG FROM ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID) IS NULL THEN 'Never Validated'
WHEN UPPER (EXISTS (SELECT XA.MATCH_STATUS_FLAG FROM ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID))='T' THEN 'Needs Revalidation'
WHEN UPPER (EXISTS (SELECT XA.MATCH_STATUS_FLAG FROM ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID.XA.INVOICE_ID))='A' THEN 'Validated' ELSE 'Other'
END AS Status
FROM 
AP_INVOICES_ALL AIA;

Alguém tem outra solução, sugere algo ?
gfkauer
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 389
Registrado em: Ter, 27 Jul 2010 1:34 pm
Localização: Sapiranga - RS
Contato:
Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

A maneira que você construiu seu case não funcionara.

o comando exists sempre retorna boleando não aceitando upper.

Teste algo assim:

Selecionar tudo

Select aia.invoice_id As Id
       Case
         When upper(nvl(xa.match_status_flag, '-1') In ('N', 'T', '-1') Then
           'Never Validated'
         When upper(xa.match_status_flag) = 'A' Then
           'Validated'
         Else
           'Other'
       End status
  From ap_invoices_all aia,
       ap_invoice_distributions_all xa
 Where xa.invoice_id = aia.invoice_id
poste os resultados após testar.
weslley.castilho
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 18 Jan 2011 11:50 am
Localização: Itajaí - SC
Weslley Castilho
Skype: weslley.castilho
Msn: weslley.castilho@hotmail.com

Meu caro a sua lógica esta correta e ao acrescentar uma virgula "," na linha um e mais um parentese ")" na linha 3 parcialmente resolveria.

Selecionar tudo

Select aia.invoice_id As Id, 
       Case 
         When upper(nvl(xa.match_status_flag, '-1')) In ('N', 'T', '-1') Then 
           'Never Validated' 
         When upper(xa.match_status_flag) = 'A' Then 
           'Validated' 
         Else 
           'Other' 
       End status 
From ap_invoices_all aia, 
       ap_invoice_distributions_all xa 
Where xa.invoice_id = aia.invoice_id
Porem estamos falando do relacionamento de 01 invoice para diversas linhas de distribuição, por isso estou tentando usar o comando exists que retornar apenas 01 linha de registro da tabela distribuição.

Veja abaixo o resultado com a sua Query:

Selecionar tudo

ROW |ID     |STATUS    |
1    418820  Validated
2    426550  Validated
3    382379  Validated
4    382379  Validated
5    382379  Validated

Observe que a linha 3,4 e 5 vem repetida, devido a obter 03 linhas de distribuição se relacionando com 01 linha de invoices.

A função DISTINCT não me ajudaria pois tenhos muitos outros relacionamentos com outras tabelas, o relacionamento mostrado é apenas um exemplo.
gfkauer
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 389
Registrado em: Ter, 27 Jul 2010 1:34 pm
Localização: Sapiranga - RS
Contato:
Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

Desculpe os erros apresentados.
Entendo o motivo de sua vontade e ou nescessidade de usar o exists, mas este comando não funcionaria da forma que estas a tentar, "Motivo?": Exists retorna boleano ou seja, não retornar o valor da consulta que tu aplica, então consequentemente não aceita upper ou comparações.

Estou a pensar em uma solução, mas antes precisaria saber se um unico invoice poderá ter mais de um status de distribuição? Se sim, o resultado esperado é qual status?

Caso a resposta ao questionamento anterior seja não, tente algo assim:

Selecionar tudo

Select aia.invoice_id As Id,
       Case
         When upper(nvl(tab.match_status_flag, '-1')) In ('N', 'T', '-1') Then
           'Never Validated'
         When upper(tab.match_status_flag) = 'A' Then
           'Validated'
         Else
           'Other'
       End status
  From ap_invoices_all aia,
       (Select Distinct
               xa.match_status_lag,
               xa.invoice_id
          From ap_invoice_distributions_all xa) tab
 Where tab.invoice_id = aia.invoice_id 
weslley.castilho
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 18 Jan 2011 11:50 am
Localização: Itajaí - SC
Weslley Castilho
Skype: weslley.castilho
Msn: weslley.castilho@hotmail.com

Corrigido a linha 12 de xa.match_status_lag, para xa.match_status_flag, (apenas adcionado a letra 'f').

E respondendo as perguntas:
Sim 01 invoice para N distribuições.
O resultado esperaro é apenas 01 linha da distribuição (match_status_flag) e o id do relacionamento para comparação.

Testei o código beleza funciona mais o tempo de executação deste script é muito longo. O tempo de resposta é de 1064.723 segundos.

Partindo de sua ideia e orientações criei o código abaixo funciona e o tempo de resposta é de 0,375 segundos

Selecionar tudo

CASE
  WHEN (SELECT DISTINCT XA.MATCH_STATUS_FLAG FROM
  ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID)='N' THEN
  'Never Validated'
  WHEN (SELECT DISTINCT XA.MATCH_STATUS_FLAG FROM
  ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID) IS NULL
  THEN 'Never Validated'
  WHEN (SELECT DISTINCT XA.MATCH_STATUS_FLAG FROM
  ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID)='T' THEN
  'Needs Revalidation'
  WHEN (SELECT DISTINCT XA.MATCH_STATUS_FLAG FROM
  ap_invoice_distributions_all XA WHERE AIA.INVOICE_ID=XA.INVOICE_ID)='A' THEN
  'Validated' ELSE 'Other'
  END AS Status
Vou juntar a minha aplicação e volto postando o código todo.
weslley.castilho
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 18 Jan 2011 11:50 am
Localização: Itajaí - SC
Weslley Castilho
Skype: weslley.castilho
Msn: weslley.castilho@hotmail.com

Conforme prometido segue abaixo a seguir o resultado final.

Menhuma das consultas deram certo no relacionamento 01 para N (muitos) entre Invoices e Distributions considerando o código todo ( com mais tabelas).

O que deu certo foi o código abaixo:

Selecionar tudo

case
  when exists(select nvl(xa.match_status_flag,'N') from ap_invoice_distributions_all xa where xa.invoice_id=invoice_id and match_status_flag='A') then
       case when exists(select nvl(xa.match_status_flag,'N') from ap_invoice_distributions_all xa where xa.invoice_id=invoice_id and match_status_flag='N') then 'Needs'
       else 'Validated'
       end
 end inv_status,

Porém não remos a diferenciação entre o Needs (T) e Never Validation (IS NULL).

Abaixo o resultado final.

Selecionar tudo

SELECT
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,
case
  when exists(select nvl(xa.match_status_flag,'N') from ap_invoice_distributions_all xa where xa.invoice_id=invoice_id and match_status_flag='A') then
       case when exists(select nvl(xa.match_status_flag,'N') from ap_invoice_distributions_all xa where xa.invoice_id=invoice_id and match_status_flag='N') then 'Needs'
       else 'Validated'
       end
 end 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,
AIPA.REVERSAL_FLAG, 
CHECK_ID,
TO_CHAR(aca.check_date,'DD/MM/YYYY') AS payment_date,
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_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
ORDER BY AIA.invoice_num;
weslley.castilho
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Ter, 18 Jan 2011 11:50 am
Localização: Itajaí - SC
Weslley Castilho
Skype: weslley.castilho
Msn: weslley.castilho@hotmail.com

Pessoal o código para aprovação mencionado anteriormente executa sem erro se não tiver tantos relacionamentos outer join nas tabelas que referenciam o campo Invoice_Id.


Para corrigir inclui a tabela ap_invoice_all left outer join invoice_distribution_all (invoice_id) e no inicio do Script usei DISTINCT.

Este Script é usado para se obter os lançamentos (registros) em AP no Oracle ERP.

Se executar este scrip vera que se existes pagamentos estornados eles aparecerão duas vezes devido ao valor amount se relacionado um debito e um crédito (- aplicado em + ou vice versa) que não é tratado no DISTINCT.

Se usar a condição --AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL) não pegara as notas estornadas sem nenhum pagamento válido.

Irei postar maiores detalhes na seção SQL pois este já não é mais um problema da seção EBS Apllications/ERP (aprovação) e sim uma questão do SQL.



Veja abaixo o código.

Agora esta certo com referencia a aprovação

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;
gfkauer
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 389
Registrado em: Ter, 27 Jul 2010 1:34 pm
Localização: Sapiranga - RS
Contato:
Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

Muito bem!

Se ainda precisar de alguma coisa só falar.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 5 visitantes