This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,859 Users
  • 2,269,775 Discussions
  • 7,916,823 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 IndiaMember 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 IndiaMember 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 IndiaMember Posts: 13,711 Bronze Badge

    Try a forum from

This discussion has been closed.