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.
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.
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.
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;