Forum Stats

  • 3,734,267 Users
  • 2,246,934 Discussions
  • 7,857,216 Comments

Discussions

Reconciled invoices are coming in the report

1050020
1050020 Member Posts: 5
edited Oct 24, 2013 3:32AM in SQL & PL/SQL
  • 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')

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    And what is the question?

  • 1050020
    1050020 Member Posts: 5

    Hi karthick,

    Reconciled invoices are coming in the Report , some  invoice are coming in the repot if i use the following condition the invoice are not comming in the report   please advice  wheather i can use this condition are not

    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

                                                   )

                      ) ----

    Thanks,

    Naresh

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    The is a SQL and PL/SQL forum. Technical question related to SQL and PL/SQL are answered. For business related questions this is not the appropriate forum. What is the application you are using? If its a Oracle supplied application then choose the appropriate forum to ask your question.

  • 1050020
    1050020 Member Posts: 5

    oracle apps techinacal

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Try a forum from

This discussion has been closed.