Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Reconciled invoices are coming in the report

1050020Oct 24 2013 — edited Oct 24 2013
  • Before changes

SELECT 'FINANCE' detdepartment, 'FINANCE' detsub_dept, 'FINANCE' detuser_name,

       ai.unique_remittance_identifier, ai.invoice_num, ai.invoice_date,

       ai.description, ai.invoice_amount, av.vendor_name, av.vendor_type_disp,

       ai.invoice_received_date, TRUNC (aipa.creation_date) last_forward_date,

       TRUNC (xvd.creation_date + NVL (atl.due_days, 0)) due_date,

       trunc(sysdate)-(TRUNC(xvd.CREATION_DATE)+NVL(atl.DUE_DAYS,0))due_days,

       'Validated - Oracle Paid: Unclear' status, ai.invoice_id,

       (SELECT RTRIM

                  (XMLAGG (XMLELEMENT (e, pha.segment1 || '|')).EXTRACT

                                                                   ('//text()'),

                   '|'

                  )

          FROM po_headers_all pha, ap_invoice_lines_all aila

         WHERE pha.po_header_id = aila.po_header_id

           AND aila.invoice_id = ai.invoice_id) po_number,

       (SELECT RTRIM

                  (XMLAGG (XMLELEMENT (e, pha.type_lookup_code || '|')).EXTRACT

                                                                   ('//text()'),

                   '|'

                  )

          FROM po_headers_all pha, ap_invoice_lines_all aila

         WHERE pha.po_header_id = aila.po_header_id

           AND aila.invoice_id = ai.invoice_id) po_type,

       (SELECT RTRIM

                  (XMLAGG (XMLELEMENT (e, pra.release_num || '|')).EXTRACT

                                                                   ('//text()'),

                   '|'

                  )

          FROM po_releases_all pra, ap_invoice_lines_all aila

         WHERE pra.po_release_id = aila.po_release_id

           AND aila.invoice_id = ai.invoice_id) release_number

  FROM apps.ap_terms apt,

       ap_invoices_all ai,

       ap_terms_lines atl,

       org_organization_definitions ood,

       fnd_user fu,

       xxindus_vhd_dynamic_h xvdh,

       xxindus_vhd_dynamic xvd,

       ap_lookup_codes alc,

       ap_invoice_payments_all aipa,

       ap_payment_history_all apha,

       ap_vendors_v av

WHERE xvd.invoice_id = ai.invoice_id

   AND apt.term_id = ai.terms_id

   AND apt.term_id = atl.term_id

   AND atl.sequence_num = 1

   AND ai.terms_id = apt.term_id

   AND ai.org_id = ood.organization_id

   AND ai.created_by = fu.user_id

   AND ai.invoice_id = xvdh.invoice_id

   AND ai.invoice_id = xvd.invoice_id

   AND alc.lookup_code = ai.invoice_type_lookup_code

   AND ai.invoice_id = aipa.invoice_id

   AND aipa.check_id = apha.check_id

   AND aipa.invoice_payment_id = (SELECT MAX (invoice_payment_id)

                                    FROM ap_invoice_payments_all a

                                   WHERE a.invoice_id = ai.invoice_id)

   AND apha.accounting_event_id = (SELECT MAX (accounting_event_id)

                                     FROM ap_payment_history_all aph1

                                    WHERE aph1.check_id = apha.check_id)

   AND ai.vendor_id = av.vendor_id

   AND apha.transaction_type IN ('PAYMENT CREATED', 'PAYMENT UNCLEARING')

   AND ai.invoice_type_lookup_code = 'STANDARD'

   AND alc.lookup_type = 'INVOICE TYPE'

   AND alc.enabled_flag = 'Y'

   AND ai.wfapproval_status IN ('WFAPPROVED', 'MANUALLY APPROVED')

   AND xvdh.checked = 'Y'

   AND xvd.status = 'Sent to Finance'

   AND ai.cancelled_date IS NULL

   --- AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE)

   AND ai.invoice_amount =

                NVL ((ai.amount_paid + NVL (ai.discount_amount_taken, 0)), 0)

--   AND ood.organization_name = :p_org_name

   AND ood.organization_id = :p_org_id

   AND ai.invoice_received_date

          BETWEEN NVL (TRUNC (TO_DATE (:p_from_date, 'RRRR/MM/DD HH24:MI:SS')),

                       ai.invoice_received_date

                      )

              AND NVL (TRUNC (TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS')),

                       SYSDATE

                      )

   AND (fu.user_name LIKE '%VHD%' OR fu.description = 'VHD')

after changes

SELECT 'FINANCE' detdepartment, 'FINANCE' detsub_dept, 'FINANCE' detuser_name,

       ai.unique_remittance_identifier, ai.invoice_num, ai.invoice_date,

       ai.description, ai.invoice_amount, av.vendor_name, av.vendor_type_disp,

       ai.invoice_received_date, TRUNC (aipa.creation_date) last_forward_date,

       TRUNC (xvd.creation_date + NVL (atl.due_days, 0)) due_date,

       trunc(sysdate)-(TRUNC(xvd.CREATION_DATE)+NVL(atl.DUE_DAYS,0))due_days,

       'Validated - Oracle Paid: Unclear' status, ai.invoice_id,

       (SELECT RTRIM

                  (XMLAGG (XMLELEMENT (e, pha.segment1 || '|')).EXTRACT

                                                                   ('//text()'),

                   '|'

                  )

          FROM po_headers_all pha, ap_invoice_lines_all aila

         WHERE pha.po_header_id = aila.po_header_id

           AND aila.invoice_id = ai.invoice_id) po_number,

       (SELECT RTRIM

                  (XMLAGG (XMLELEMENT (e, pha.type_lookup_code || '|')).EXTRACT

                                                                   ('//text()'),

                   '|'

                  )

          FROM po_headers_all pha, ap_invoice_lines_all aila

         WHERE pha.po_header_id = aila.po_header_id

           AND aila.invoice_id = ai.invoice_id) po_type,

       (SELECT RTRIM

                  (XMLAGG (XMLELEMENT (e, pra.release_num || '|')).EXTRACT

                                                                   ('//text()'),

                   '|'

                  )

          FROM po_releases_all pra, ap_invoice_lines_all aila

         WHERE pra.po_release_id = aila.po_release_id

           AND aila.invoice_id = ai.invoice_id) release_number

  FROM apps.ap_terms apt,

       ap_invoices_all ai,

       ap_terms_lines atl,

       org_organization_definitions ood,

       fnd_user fu,

       xxindus_vhd_dynamic_h xvdh,

       xxindus_vhd_dynamic xvd,

       ap_lookup_codes alc,

       ap_invoice_payments_all aipa,

       ap_payment_history_all apha,

       ap_vendors_v av

WHERE xvd.invoice_id = ai.invoice_id

   AND apt.term_id = ai.terms_id

   AND apt.term_id = atl.term_id

   AND atl.sequence_num = 1

   AND ai.terms_id = apt.term_id

   AND ai.org_id = ood.organization_id

   AND ai.created_by = fu.user_id

   AND ai.invoice_id = xvdh.invoice_id

   AND ai.invoice_id = xvd.invoice_id

   AND alc.lookup_code = ai.invoice_type_lookup_code

   AND ai.invoice_id = aipa.invoice_id

   AND aipa.check_id = apha.check_id

    AND aipa.invoice_payment_id = (SELECT MAX (invoice_payment_id)

                                    FROM ap_invoice_payments_all a

                                   WHERE a.invoice_id = ai.invoice_id)

                                    group by a.transaction_type)                                  

   AND apha.accounting_event_id = (SELECT MAX (accounting_event_id)

                                     FROM ap_payment_history_all aph1

                                    WHERE aph1.check_id = apha.check_id)

   AND ai.vendor_id = av.vendor_id

   AND apha.transaction_type IN ('PAYMENT CREATED', 'PAYMENT UNCLEARIN\G')

   and not exists ( select 1

                    from   apps.ap_payment_history_all apha

                         ,apps.ap_invoice_payments_all aipa

                         ,apps.ap_invoices_all  ai1

                    where aipa.check_id = apha.check_id

                    and ai1.invoice_id = aipa.invoice_id

                    and ai1.INVOICE_ID=ai.invoice_id

                    and apha.transaction_type IN ('PAYMENT CLEARING')

                    and  apha.CREATION_DATE = ( select max(apha.CREATION_DATE)

                                                from apps.ap_payment_history_all apha

                                                    ,apps.ap_invoice_payments_all aipa

                                                    ,apps.ap_invoices_all  ai2

                                                where aipa.check_id = apha.check_id

                                                and ai2.invoice_id = aipa.invoice_id

                                                and ai2.INVOICE_ID=ai.INVOICE_ID

                                               )

                  ) ---- newly added

   AND ai.invoice_type_lookup_code = 'STANDARD'

   AND alc.lookup_type = 'INVOICE TYPE'

   AND alc.enabled_flag = 'Y'

   AND ai.wfapproval_status IN ('WFAPPROVED', 'MANUALLY APPROVED')

   AND xvdh.checked = 'Y'

   AND xvd.status = 'Sent to Finance'

   AND ai.cancelled_date IS NULL

   --- AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE)

   AND ai.invoice_amount =

                NVL ((ai.amount_paid + NVL (ai.discount_amount_taken, 0)), 0)

--   AND ood.organization_name = :p_org_name

   AND ood.organization_id = :p_org_id

  - AND AI.INVOICE_NUM='A302689/EXM/OSR/03-2012'

   AND ai.invoice_received_date

          BETWEEN NVL (TRUNC (TO_DATE (:p_from_date, 'RRRR/MM/DD HH24:MI:SS')),

                       ai.invoice_received_date

                      )

              AND NVL (TRUNC (TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS')),

                       SYSDATE

                      )

   AND (fu.user_name LIKE '%VHD%' OR fu.description = 'VHD')

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 21 2013
Added on Oct 24 2013
6 comments
420 views