Consultation Approval

[weslley.castilho]

Good afternoon, I have a question about approval, can anyone help me?

What precise is to get the approval in the Distribution table ap_invoice_distributions_all) and relate to the registry line in Invoices AP_ INVOICES_ALL.

The distribution table has one or more records in invoices

Select all

INVOICES         CAMPO           RELACIONAMENTO 
                        INVOICE_ID    01

Select all

DISTRIBUTION CAMPO           RELACIONAMENTO 
                       INVOICE_ID    N

Ie an invoice ID for many in distribution and I need to return only 01 record per ID and Asim the MATCH_STATUS_FLAG field in Distribution.

I thought of using the clause exists more is error see below the
code:

Select all

 
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; 

Someone has another solution, Do you suggest something?

[gfkauer]

The way you built your case did not work.

The command always returns Boleando not accepting Upper .

Test Something like this:

Select all

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

Post the results after testing.

[weslley.castilho]

My dear logic is correct and adding a virgule "," on line one and another parentheses ")" in line 3 partially would solve.



However, we are talking about the relationship of 01 invoice for several distribution lines, so I'm trying to use the existing command that returns only 01 record line Distribution table.

Below is the result with your query:

Select all

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

Observe That line 3.4 and 5 comes repeated, due to obtaining 03 distribution lines relating to 01 line of invoices.

The distinct function would not help me because I have many other relationships with other tables, the relationship shown is just one example.

[gfkauer]

Sorry for the errors presented.
I understand the reason for your will and or in the need to use , but this command would not work in the way you try, "reason?": exists [/ i ] Boleean Returns ie it does not return the query value that you apply, so consequently does not accept Upper or comparisons.

I am thinking of a solution, but first would you need to know if a single invoice can have more than a distribution status? If yes, the expected result is what status?

If the answer to the previous question is not, try something like this:

Select all

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]

Fixed line 12 xa.match_status_lag to xa.match_status_flag (App to the letter 'f').

and answering questions:
Yes 01 Invoice for n Distributions.
The waiter wait is only 01 line of distribution (match_status_flag) and the relationship ID for comparison.

I tested the Beauty code works plus the execution time of this script is too long. The response time is 1064.723 seconds.

Based on your idea and guidelines I created the code below works and the response time is 0.375 seconds

Select all

 
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 

I'll join my application and come back by posting the whole code.

[weslley.castilho]

As promised follows below the end result.

Methm of the consultations were right in the relationship 01 to N (many) between invoices and distributions considering the whole code (with more tables).

What worked on was the code below:

Select all

 
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, 

But we do not rose the differentiation between Needs (T) and Never Validation (IS NULL).

below the end result.

Select all

 
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; 

Code] [/ Code]

[weslley.castilho]

Personal The previously mentioned approval code runs without error if you do not have so many Outer Join relationships in the tables that refer to the Invoice_ID field.


To fix includes the table ap_invoice_all left outer join invoice_distribution_all (invoice_id) and at the beginning of the script I used Distinct.

This script is used to obtain the releases (records) in AP in Oracle ERP.

If you run this script you will see that if you exist when you exist .

If you use the condition --AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL), it does not pick up the reversed notes without any valid payment.

I will post more details in the SQL section because this is no longer a problem in the EBS Appllications / ERP section, but a question of SQL.



See below the code.

now is right with reference to approval

Select all

 
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]

Very good!

If you still need anything just talk.