India - Creditor Trial Balance Report in Query View

Main View:
APPS.XXCUSTOM_CREDITOR_TRIAL_V

Inner View:
APPS.XXCUSTOM_CREDITOR_TRIAL


CREATE OR REPLACE FORCE VIEW APPS.XXCUSTOM_CREDITOR_TRIAL_V
(DEBIT_AMOUNT_TOTAL,
CREDIT_AMOUNT_TOTAL,
SUPPLIER_NAME,
SUPPLIER_NUMBER,
SUPPLIER_ID,
ACCOUNTING_DATE,
ORG_ID
) AS
SELECT sum(DEBIT_AMOUNT_DR), sum(CREDIT_AMOUNT_CR),VENDOR_NAME,SEGMENT1,VENDOR_ID,ACCOUNTING_DATE,ORG_ID  FROM XXTEST_CREDITOR_BALANCES
GROUP BY VENDOR_NAME,SEGMENT1,VENDOR_ID,ACCOUNTING_DATE,ORG_ID


SELECT SUM(DEBIT_AMOUNT_TOTAL),SUM(CREDIT_AMOUNT_TOTAL) FROM XXTEST_CREDITOR_TRIAL
WHERE SUPPLIER_NUMBER = 'PUU1005'
AND ORG_ID = 102


CREATE OR REPLACE FORCE VIEW APPS.XXCUSTOM_CREDITOR_TRIAL_V
(VENDOR_SITE_CODE,
VENDOR_ID ,
INVOICE_TYPE_LOOKUP_CODE ,
INVOICE_NUM ,
INVOICE_DATE,
INVOICE_DESCRIPTION ,
CCID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE ,
DEBIT_AMOUNT_DR ,
CREDIT_AMOUNT_CR ,
ACCT_DR ,
ACCT_CR ,
PAYMENT_NUM ,
PAY_ACCOUNTING_DATE ,
CHECK_NUMBER ,
SEGMENT1 ,
VENDOR_NAME ,
VENDOR_TYPE_LOOKUP_CODE ,
PO_DISTRIBUTION_ID ,
EXCHANGE_RATE_TYPE ,
ORG_ID ,
BATCH_ID ,
EXCHANGE_DATE ,
INVOICE_ID ,
ACCOUNTING_DATE ,
VOUCHER_NUM ,
LIABILITY_ACC ,
LIABILITY_DESC ,
SRT ) AS
SELECT povs.vendor_site_code,
                 pov.Vendor_id,
                 api.invoice_type_lookup_code,
                 api.invoice_num,
                 api.invoice_date,
                 api.description description,
                 apd.dist_code_combination_id ccid,
                 api.invoice_currency_code,
                 api.exchange_rate,
                 /*Decode(api.invoice_type_lookup_code,
                        'CREDIT',
                        abs(z.amt_val),
                        0) DR_VAL,
                 Decode(api.invoice_type_lookup_code, 'CREDIT', 0, z.amt_val) CR_VAL, */
                 --Removed discount amount taken from CR and DR for bug#7689858
                 DECODE (api.invoice_type_lookup_code,
                         'CREDIT', ABS (z.amt_val),
                         0, api.invoice_type_lookup_code,
                         'DEBIT', ABS (z.amt_val),
                         0)
                    DR_VAL,
                 DECODE (api.invoice_type_lookup_code,
                         'CREDIT', 0,
                         z.amt_val, api.invoice_type_lookup_code,
                         'DEBIT', 0,
                         z.amt_val)
                    CR_VAL,
                 0 acct_dr,
                 0 acct_cr,
                 NULL payment_num,
                 TO_CHAR (apd.accounting_date, 'dd-MON-yyyy')
                    pay_accounting_date,
                 NULL check_number,
                 pov.segment1,
                 pov.vendor_name,
                 pov.vendor_type_lookup_code,
                 apd.po_distribution_id,
                 api.exchange_rate_type,
                 api.org_id,
                 api.batch_id,
                 api.exchange_date,
                 api.invoice_id,
                 apd.accounting_date,
                 api.DOC_SEQUENCE_VALUE voucher_num,
                 (SELECT yy.SEGMENT3
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_ACC,
                 (SELECT xx.DESCRIPTION
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_DESC,
                 TO_NUMBER (TO_CHAR (apd.accounting_date, 'YYYYMMDD')) srt
            FROM ap_invoices_all api,
                 ap_invoice_lines_all apil, /* Added by Ramananda for bug#4454818  */
                 ap_invoice_distributions_all apd,
                 po_vendors pov,
                 po_vendor_sites_all povs,
                 (  SELECT NVL (SUM (apd.amount), 0) amt_val, api.invoice_id
                      FROM ap_invoices_all api,
                           ap_invoice_lines_all apil, /* Added by Ramananda for bug#4454818  */
                           ap_invoice_distributions_all apd,
                           po_vendors pov,
                           po_vendor_sites_all povs
                     WHERE     api.invoice_id = apd.invoice_id
                           AND apil.invoice_id = api.invoice_id /* Added by Ramananda for bug#4454818, start*/
                           AND apil.line_number = apd.invoice_line_number /* Added by Ramananda for bug#4454818, end*/
                           AND api.vendor_id = pov.vendor_id
--                           AND (   api.vendor_id = :p_vendor_id
--                                OR :p_vendor_id IS NULL)
--                           AND (   NVL (pov.Vendor_Type_Lookup_Code, 'NULL') =
--                                      :P_Vendor_Type_Lookup_Code
--                                OR :P_Vendor_Type_Lookup_Code IS NULL) /*Added by nprashar for bug # 7207441*/
                           /*AND     pov.vendor_type_lookup_code = NVL(:p_vendor_type_lookup_code, pov.vendor_type_lookup_code) Commented by nprashar for bug # 7154601*/
                           AND api.invoice_type_lookup_code <> 'PREPAYMENT'
--                           AND (api.org_id = :p_org_id OR api.org_id IS NULL)
                           AND api.vendor_site_id = povs.vendor_site_id
--                           AND (   api.vendor_site_id = :p_vendor_site_id
--                                OR :p_vendor_site_id IS NULL)
--                           AND TRUNC (apd.accounting_date) BETWEEN :p_from_date
--                                                               AND :p_to_date
                           AND apd.match_status_flag = 'A'
                           AND apil.line_type_lookup_code <> 'PREPAY'
                  GROUP BY api.invoice_id) z
           WHERE     api.invoice_id = z.invoice_id
                 AND api.invoice_id = apd.invoice_id
                 AND apil.invoice_id = api.invoice_id /* Added by Ramananda for bug#4454818, start*/
                 AND apil.line_number = apd.invoice_line_number /* Added by Ramananda for bug#4454818, end*/
                 AND apd.ROWID =
                        (SELECT ROWID
                           FROM ap_invoice_distributions_all
                          WHERE     ROWNUM = 1
                                AND invoice_id = apd.invoice_id
--                                AND TRUNC (accounting_date) BETWEEN :p_from_date
--                                                                AND :p_to_date
                                AND match_status_flag = 'A')
                 AND api.vendor_id = pov.vendor_id
--                 AND (api.vendor_id = :p_vendor_id OR :p_vendor_id IS NULL)
--                 AND (   NVL (pov.Vendor_Type_Lookup_Code, 'NULL') =
--                            :P_Vendor_Type_Lookup_Code
--                      OR :P_Vendor_Type_Lookup_Code IS NULL) /*Added by nprashar for bug # 7207441*/
                 /*AND     pov.vendor_type_lookup_code = NVL(:p_vendor_type_lookup_code, pov.vendor_type_lookup_code) Commented by nprashar for bug # 7154601*/
                  -- on 22-07-01 comments removed on 07-12-2001 to print the report for all the vendor types
                 AND api.invoice_type_lookup_code <> 'PREPAYMENT'
                 AND apd.match_status_flag = 'A'
--                 AND api.org_id = :p_org_id
                 --and 1<>1
                 AND api.vendor_site_id = povs.vendor_site_id
--                 AND (   api.vendor_site_id = :p_vendor_site_id
--                      OR :p_vendor_site_id IS NULL)
                 AND (   (api.invoice_type_lookup_code <> 'DEBIT')
                      OR (    (api.invoice_type_lookup_code = 'DEBIT')
                          AND --or
                              (NOT EXISTS
                                      (SELECT '1'
                                         FROM ap_invoice_payments_all app,
                                              ap_checks_all apc
                                        WHERE     app.check_id = apc.check_id
                                              AND app.invoice_id =
                                                     api.invoice_id
                                              AND apc.payment_type_flag = 'R'))))
UNION ALL
SELECT povs.vendor_site_code,
                 pov.Vendor_id,
                 CASE
                    WHEN api.invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       'PREPAYMENT'
                    ELSE
                       'PAYMENT'
                 END
                    invoice_type_lookup_code,
                 CASE
                    WHEN api.invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       api.invoice_num
                    ELSE
                       NULL
                 END
                    invoice_num,
                 CASE
                    WHEN api.invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       API.INVOICE_DATE
                    ELSE
                       APC.CHECK_DATE
                 END
                    INVOICE_DATE,
                 app.description                           /*APC.description*/
                                                           /*apd.description*/
                                description, --By nprashar for bug 8307469 added by sridhar
                 app.accts_pay_code_combination_id ccid,
                 api.payment_currency_code,
                 --    api.invoice_type_lookup_code,
                 apc.exchange_rate,
                 CASE
                    WHEN REVERSAL_INV_PMT_ID IS NOT NULL
                    THEN
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code,
                                            'VOIDED', app.amount,
                                            ABS (app.amount)),
                          0)
                    ELSE
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code, 'VOIDED', 0, 0),
                          app.amount)
                 END
                    dr_val,
                 CASE
                    WHEN REVERSAL_INV_PMT_ID IS NOT NULL
                    THEN
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code, 'VOIDED', 0, 0),
                          app.amount)
                    ELSE
                       DECODE (
                          api.invoice_type_lookup_code,
                          'CREDIT', DECODE (status_lookup_code,
                                            'VOIDED', app.amount,
                                            ABS (app.amount)),
                          0)
                 END
                    cr_val, --Added discount amount taken in cr and dr for bug#7889858
                 0 acct_dr,
                 0 acct_cr,
                 DECODE (api.payment_status_flag,
                         'Y', TO_CHAR (apc.doc_sequence_value),
                         'P', TO_CHAR (apc.doc_sequence_value),
                         TO_CHAR (apc.doc_sequence_value), 'N',
                         NULL)
                    payment_num,
                 DECODE (api.payment_status_flag,
                         'Y', TO_CHAR (app.accounting_date, 'dd-MON-yyyy'),
                         'P', TO_CHAR (app.accounting_date, 'dd-MON-yyyy'))
                    pay_accounting_date,
                 DECODE (api.payment_status_flag,
                         'Y', TO_CHAR (apc.check_number),
                         'P', TO_CHAR (apc.check_number))
                    check_number,
                 pov.segment1,
                 pov.vendor_name,
                 pov.vendor_type_lookup_code,
                 apil.po_distribution_id            /*apd.po_distribution_id*/
                                        ,        --By nprashar for bug 8307469
                 apc.exchange_rate_type,
                 api.org_id,
                 api.batch_id,
                 apc.exchange_date,
                 api.invoice_id,
                 app.accounting_date,
                 CASE
                    WHEN invoice_type_lookup_code = 'PREPAYMENT'
                    THEN
                       apI.DOC_SEQUENCE_VALUE
                    ELSE
                       apC.DOC_SEQUENCE_VALUE
                 END
                    voucher_num,
                 (SELECT yy.SEGMENT3
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_ACC,
                 (SELECT xx.DESCRIPTION
                    FROM fnd_flex_values_vl xx, GL_CODE_COMBINATIONS_KFV yy
                   WHERE     xx.FLEX_VALUE = yy.SEGMENT3
                         AND yy.CODE_COMBINATION_ID =
                                api.ACCTS_PAY_CODE_COMBINATION_ID
                         AND ROWNUM <= 1)
                    LIABILITY_DESC,
                 TO_NUMBER (TO_CHAR (app.accounting_date, 'YYYYMMDD')) srt
            FROM ap_invoices_all api,
                 ap_invoice_lines_all apil, /* Added by Ramananda for bug#4454818  */
                 --ap_invoice_distributions_all apd, by nprashar for bug 8307469
                 po_vendors pov,
                 --ap_invoice_payments_all app,
                 ap_invoice_payments_v app,
                 ap_checks_all apc,
                 po_vendor_sites_all povs
           WHERE /*api.invoice_id = apd.invoice_id  By nprashar for bug 8307469
          and*/
                apil .invoice_id = api.invoice_id /* Added by Ramananda for bug#4454818, start*/
                 --and     apil.line_number = apd.invoice_line_number /* Added by Ramananda for bug#4454818, end*/ by nprashar for bug 8307469
                 --AND    apd.rowid = (select rowid from ap_invoice_distributions_all where rownum=1 and invoice_id=apd.invoice_id and match_status_flag='A') by nprashar for bug 8307469
                 AND api.vendor_id = pov.vendor_id
                 AND app.invoice_id = api.invoice_id
                 AND app.check_id = apc.check_id
--                 AND (api.vendor_id = :p_vendor_id OR :p_vendor_id IS NULL)
--                 AND TRUNC (app.accounting_date) BETWEEN :p_from_date
--                                                     AND :p_to_date
--                 AND api.org_id = :p_org_id
--                 AND (   NVL (pov.Vendor_Type_Lookup_Code, 'NULL') =
--                            :P_Vendor_Type_Lookup_Code
--                      OR :P_Vendor_Type_Lookup_Code IS NULL) /*Added by nprashar for bug # 7207441*/
                 AND apc.status_lookup_code IN
                        ('CLEARED',
                         'NEGOTIABLE',
                         'VOIDED',
                         'RECONCILED UNACCOUNTED',
                         'RECONCILED',
                         'CLEARED BUT UNACCOUNTED')
                 --AND    apd.match_status_flag='A'
                 AND api.vendor_site_id = povs.vendor_site_id
                 --and apc.check_id=276647
--                 AND (   api.vendor_site_id = :p_vendor_site_id
--                      OR :p_vendor_site_id IS NULL)
                 AND EXISTS
                        (SELECT '1'
                           FROM ap_invoice_distributions_all apd
                          WHERE     apd.invoice_id = api.invoice_id
                                AND apd.match_status_flag = 'A'
                                AND apil.line_number = apd.invoice_line_number
                                AND NVL (apil.po_distribution_id, -999) =
                                       NVL (apd.po_distribution_id, -999) --Condition changed  by nprashar for bug 8307469
                                AND apd.ROWID =
                                       (SELECT ROWID
                                          FROM ap_invoice_distributions_all
                                         WHERE     ROWNUM = 1
                                               AND invoice_id = apd.invoice_id))