Sunday, 18 December 2016

India - Debtors Trial Balance Report of Query View

India Debtor Trial Balance Query view:-
******************************************************************
select --*
SUM(AMOUNT)
 from xx_debtor_trial_v
WHERE ORG_ID = 82
AND CUSTOMER_ID = 3064

******************************************************************
CREATE OR REPLACE FORCE VIEW APPS.XX_DEBTOR_TRIAL_V
(
   AMOUNT,
   OTHER_CURRENCY_AMOUNT,
   CUSTOMER_ID,
   CUSTOMER_NAME,
   TRX_DATE,
   ORG_ID
)
AS
     SELECT SUM (AMOUNT), -- SUM(decode(nvl(exchange_rate,1),1,0,AMOUNT_OTHER_CURRENCY))  10090032 25NOV
            SUM (AMOUNT_OTHER_CURRENCY),
            A.CUSTOMER_ID,
            A.customer_name,
            A.trx_date,
            A.ORG_ID
       -- INTO v_tran_tot_amt, v_func_tot_amt
       FROM (SELECT hzca.cust_account_id customer_id,
                    hzca.account_number                     /*a.party_number*/
                                       customer_number, /*Commented by nprashar for bug # 7256288*/
                    a.party_name customer_name,
                    d.gl_date,
                    B.CUSTOMER_TRX_ID,
                    b.trx_number,
                    TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
                    NULL receipt_number,
                    NULL receipt_date,
                    SUBSTR (b.comments, 1, 50) remarks,
                    d.code_combination_id account_id,
                    b.invoice_currency_code currency_code,
                    b.exchange_rate,
                    d.amount amount,
                    (d.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
                    f.TYPE,
                    b.customer_trx_id,
                    d.customer_trx_line_id,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ra_customer_trx_ALL B,
                    RA_CUST_TRX_LINE_GL_DIST_ALL D,
                    GL_CODE_COMBINATIONS E,
                    RA_CUST_TRX_TYPES_ALL F,
                    ar_payment_schedules_all G
              WHERE     a.party_id = hzca.party_id
                    AND b.bill_to_customer_id = hzca.cust_account_id
                    AND b.complete_flag = 'Y'
                    AND d.customer_trx_id = b.customer_trx_id
                    AND d.account_class = 'REC'
                    AND e.code_combination_id = d.code_combination_id
                    AND f.cust_trx_type_id = b.cust_trx_type_id
                    AND f.TYPE IN ('INV', 'CM', 'DM', 'DEP')
                    AND d.latest_rec_flag = 'Y'
                    AND g.customer_trx_id = b.customer_trx_id
                    --  AND b.ORG_ID = :P_ORG_ID
                    --AND      trunc(b.trx_date) between  trunc(p_start_date)  AND trunc(:p_end_date)
                    -- AND TRUNC (G.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
                    AND g.payment_schedule_id IN
                           (SELECT MIN (payment_schedule_id)
                              FROM ar_payment_schedules_all
                             WHERE customer_trx_id = g.customer_trx_id)
             UNION
             -- Following Query for Cash receipts
             SELECT hzca.cust_account_id customer_id,
                    hzca.account_number                     /*a.party_number*/
                                       customer_number, /*Commented by nprashar for bug # 7256288*/
                    a.party_name customer_name,
                    e.gl_date,
                    0,
                    NULL,
                    NULL,
                    b.receipt_number,
                    TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
                    NULL,
                    d.cash_ccid account_id,
                    b.currency_code,
                    b.exchange_rate,
                    b.amount * -1 amount,                  --bug10090032 29nov
                    (b.amount * -1 * NVL (b.exchange_rate, 1))
                       amount_other_currency,              --bug10090032 29nov
                    'REC' TYPE,
                    0,
                    0,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ar_cash_receipts_all B,
                    gl_code_combinations C,
                    ar_receipt_method_accounts_all D,
                    ar_cash_receipt_history_all E,
                    ar_payment_schedules_all F
              /*COMMENTED for bug#10090032
                  (SELECT     MIN(acrh1.cash_receipt_history_id) cash_receipt_history_id,
                      acrh1.cash_receipt_id
                  FROM    ar_cash_receipt_history_all acrh1
                  WHERE    TRUNC(acrh1.gl_date) < TRUNC(p_start_date)
                  AND  acrh1.status <> 'REVERSED'
                  AND NOT EXISTS (select '1' from ar_cash_receipt_history_all acrh2 where acrh2.cash_receipt_id = acrh1.cash_receipt_id and trunc(acrh2.gl_date) < trunc((p_start_date)) )

                  AND     acrh1.P_ORG_ID = P_ORGANIZATION_ID
                                GROUP BY acrh1.cash_receipt_id) G*/
              --Added the above inline view by sanjikum for Bug # 3962497
              WHERE     a.party_id = hzca.party_id
                    AND b.pay_from_customer = hzca.cust_account_id
                    AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
                    AND d.receipt_method_id = b.receipt_method_id
                    AND d.cash_ccid = c.code_combination_id
                    AND e.cash_receipt_id = b.cash_receipt_id
                    --AND         e.cash_receipt_id             =       g.cash_receipt_id
                    --AND         e.cash_receipt_history_id     =       g.cash_receipt_history_id
                    --start additions for bug#10090032
                    AND e.cash_receipt_history_id IN
                           (SELECT MIN (incrh.cash_receipt_history_id)
                              FROM ar_cash_receipt_history_all incrh
                             WHERE     incrh.cash_receipt_id =
                                          b.cash_receipt_id
                                   AND incrh.status <> 'REVERSED')
                    --added the above 2 conditions by sanjikum for Bug # 3962497
                    AND f.cash_receipt_id = b.cash_receipt_id
             -- AND b.ORG_ID = :P_ORG_ID
             --AND     e.current_record_flag                   = 'Y' --commented by sanjikum for Bug # 3962497
             -- AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
             UNION
             -- Following Query for Receipt WriteOff
             SELECT hzca.cust_account_id customer_id,
                    hzca.account_number                     /*a.party_number*/
                                       customer_number, /*Commented by nprashar for bug # 7256288*/
                    a.party_name customer_name,
                    --e.gl_date                                                        , 10137368
                    g.gl_date,                                  --bug#10137368
                    0,
                    NULL,
                    NULL,
                    b.receipt_number,
                    TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
                    NULL,
                    d.cash_ccid account_id,
                    b.currency_code,
                    b.exchange_rate,
                    g.amount_applied amount,
                    (g.amount_applied * NVL (b.exchange_rate, 1))
                       amount_other_currency,
                    'W/O' TYPE,
                    0,
                    0,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ar_cash_receipts_all B,
                    gl_code_combinations C,
                    ar_receipt_method_accounts_all D,
                    ar_cash_receipt_history_all E,
                    ar_payment_schedules_all F,
                    ar_receivable_applications_all G
              WHERE     a.party_id = hzca.party_id
                    AND b.pay_from_customer = hzca.cust_account_id
                    AND g.applied_payment_schedule_id = -3
                    AND g.cash_receipt_id = b.cash_receipt_id
                    AND g.cash_receipt_history_id = e.cash_receipt_history_id
                    AND g.status = 'ACTIVITY'
                    AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
                    AND d.receipt_method_id = b.receipt_method_id
                    AND d.cash_ccid = c.code_combination_id
                    AND e.cash_receipt_id = b.cash_receipt_id
                    AND f.cash_receipt_id = b.cash_receipt_id
                    --  AND b.ORG_ID = :P_ORG_ID
                    --AND    trunc(e.gl_date)  < trunc(p_start_date)
                    --commented and added the below condition for --bug#10137368
                    --  AND TRUNC (g.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
                    AND NOT EXISTS
                               (SELECT 1
                                  FROM ar_cash_receipt_history_all
                                 WHERE     cash_receipt_id = b.cash_receipt_id
                                       AND status = 'REVERSED')
             UNION
             -- Following Query for Receipt Reversal
             SELECT hzca.cust_account_id customer_id,
                    hzca.account_number                     /*a.party_number*/
                                       customer_number, /*Commented by nprashar for bug # 7256288*/
                    a.party_name customer_name,
                    e.gl_date gl_date,
                    0,
                    NULL,
                    TO_CHAR (e.trx_date, 'DD-MM-YYYY') trx_date,
                    b.receipt_number,
                    TO_CHAR (b.receipt_date, 'DD-MM-YYYY'),
                    NULL,
                    c.code_combination_id account_id,
                    b.currency_code,
                    b.exchange_rate,
                    b.amount amount,
                    (b.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
                    'REV' TYPE,
                    0,
                    0,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ar_cash_receipts_all B,
                    gl_code_combinations C,
                    ar_receipt_method_accounts_all D,
                    ar_cash_receipt_history_all E,
                    ar_payment_schedules_all F
              WHERE     a.party_id = hzca.party_id
                    AND b.pay_from_customer = hzca.cust_account_id
                    AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id
                    AND d.receipt_method_id = b.receipt_method_id
                    AND d.cash_ccid = c.code_combination_id
                    AND e.cash_receipt_id = b.cash_receipt_id
                    AND f.cash_receipt_id = b.cash_receipt_id
                    -- AND b.ORG_ID = :P_ORG_ID
                    AND e.status = 'REVERSED'
                    --AND    trunc(b.reversal_date) < trunc(p_start_date)
                    --  AND TRUNC (e.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
                    AND b.reversal_date IS NOT NULL                -- SSUMAITH
             UNION
             -- Following Query for Adjustments
             SELECT HZCA.CUST_ACCOUNT_ID CUSTOMER_ID,
                    hzca.account_number                     /*A.PARTY_NUMBER*/
                                       CUSTOMER_NUMBER, /*Commented by nprashar for bug # 7256288*/
                    A.PARTY_NAME CUSTOMER_NAME,
                    B.GL_DATE,
                    0,
                    B.ADJUSTMENT_NUMBER,
                    TO_CHAR (B.APPLY_DATE, 'DD-MM-YYYY') trx_date,
                    NULL receipt_number,
                    NULL receipt_date,
                    SUBSTR (b.comments, 1, 50) remarks,
                    b.code_combination_id account_id,
                    c.invoice_currency_code currency_code,
                    c.exchange_rate,
                    b.amount amount,
                    (b.amount * NVL (c.exchange_rate, 1)) amount_other_currency,
                    'ADJ' TYPE,
                    0,
                    0,
                    b.ROWID,
                    B.ORG_ID
               FROM HZ_PARTIES A,
                    HZ_CUST_ACCOUNTS HZCA,
                    ar_adjustments_all b,
                    ra_customer_trx_all c,
                    ar_payment_schedules_all d,
                    gl_code_combinations e
              WHERE     b.customer_trx_id = c.customer_trx_id
                    AND a.party_id = hzca.party_id
                    AND c.bill_to_customer_id = hzca.cust_account_id
                    AND b.status = 'A'
                    AND e.code_combination_id = b.code_combination_id
                    AND b.payment_schedule_id = d.payment_schedule_id
                    AND b.customer_trx_id = d.customer_trx_id
                    --  AND c.ORG_ID = :P_ORG_ID
                    --AND    trunc(b.apply_date) < trunc(p_start_date)
                    -- AND TRUNC (b.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
                    AND b.customer_trx_id NOT IN
                           (SELECT customer_trx_id
                              FROM ar_adjustments_all
                             WHERE    CHARGEBACK_CUSTOMER_TRX_ID IS NOT NULL
                                   OR created_from LIKE 'REVERSE%') /*added by abezgam for bug#10228717*/
             UNION
             -- -- Following Query for Discounts
             SELECT hzca.cust_account_id customer_id,
                    hzca.account_number                     /*a.party_number*/
                                       customer_number, /*Commented by nprashar for bug # 7256288*/
                    a.party_name customer_name,
                    d.gl_date,
                    B.CUSTOMER_TRX_ID,
                    b.trx_number,
                    TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
                    NULL receipt_number,
                    NULL receipt_date,
                    SUBSTR (b.comments, 1, 50) remarks,
                    earned_discount_ccid account_id,
                    b.invoice_currency_code currency_code,
                    b.exchange_rate,
                    d.EARNED_discount_taken * -1 amount, --ADDED *-1 FOR BUG#10090032
                    d.ACCTD_EARNED_DISCOUNT_TAKEN * -1 amount_other_currency, --ADDED *-1 FOR BUG#10090032
                    'DSC' TYPE,
                    b.customer_trx_id,
                    0,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ra_customer_trx_all B,
                    ar_receivable_applications_all D
              WHERE     a.party_id = hzca.party_id
                    AND b.bill_to_customer_id = hzca.cust_account_id
                    AND b.complete_flag = 'Y'
                    AND D.EARNED_DISCOUNT_TAKEN IS NOT NULL
                    AND D.EARNED_DISCOUNT_TAKEN <> 0
                    --  AND b.ORG_ID = :P_ORG_ID
                    AND b.customer_trx_id = d.applied_customer_trx_id
                    AND d.application_type = 'CASH'
                    AND d.display = 'Y'
             --  AND TRUNC (d.GL_DATE) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
             UNION ALL
             -- Following Query for Exchange Gain and Loss
             SELECT hzca.cust_account_id customer_id,
                    hzca.account_number                     /*a.party_number*/
                                       customer_number, /*Commented by nprashar for bug # 7256288*/
                    a.party_name customer_name,
                    d.gl_date,
                    b.customer_trx_id,
                    b.trx_number,
                    TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
                    c.receipt_number,
                    TO_CHAR (c.receipt_date, 'DD-MM-yyyy') receipt_date,
                    DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
                    e.code_combination_id,
                    b.INVOICE_CURRENCY_CODE,
                    b.exchange_rate,
                    NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
                    NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
                       acctd_amount,              --ADDED *-1 FOR BUG#10090032
                    e.source_type,
                    0 customer_trx_id,
                    0 customer_trx_line_id,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ra_customer_trx_all b,
                    ar_cash_receipts_all c,
                    ar_receivable_applications_all d,
                    ar_distributions_all e
              WHERE     a.party_id = hzca.party_id
                    AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
                    AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
                    AND c.cash_receipt_id = d.cash_receipt_id
                    AND e.SOURCE_ID = d.receivable_application_id
                    --  AND b.ORG_ID = :P_ORG_ID
                    AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
             --AND TRUNC(c.receipt_date) < trunc(p_start_date)
             --    AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
             UNION ALL
             -- Following Query for Exchange Gain and Loss for CM applied to the invoice
             /**ADDED for bug#10090032*/
             SELECT hzca.cust_account_id customer_id,
                    hzca.account_number customer_number,
                    a.party_name customer_name,
                    d.gl_date,
                    b.customer_trx_id,
                    b.trx_number,
                    TO_CHAR (b.trx_date, 'DD-MM-YYYY') trx_date,
                    NULL,
                    NULL,
                    DECODE (e.amount_dr, NULL, 'CR', 'DR') comments,
                    e.code_combination_id,
                    b.INVOICE_CURRENCY_CODE,
                    b.exchange_rate,
                    NVL (e.AMOUNT_DR * -1, e.AMOUNT_CR) amount, --ADDED *-1 FOR BUG#10090032
                    NVL (e.ACCTD_AMOUNT_DR * -1, e.ACCTD_AMOUNT_CR)
                       acctd_amount,              --ADDED *-1 FOR BUG#10090032
                    e.source_type,
                    0 customer_trx_id,
                    0 customer_trx_line_id,
                    b.ROWID,
                    B.ORG_ID
               FROM hz_parties a,
                    hz_cust_accounts hzca,
                    ra_customer_trx_all b,
                    ar_payment_schedules_all c,
                    ar_receivable_applications_all d,
                    ar_distributions_all e
              WHERE     a.party_id = hzca.party_id
                    AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID
                    AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID
                    AND c.payment_schedule_id = d.payment_schedule_id
                    AND c.class = 'CM'
                    AND e.SOURCE_ID = d.receivable_application_id
                    --AND b.ORG_ID = :P_ORG_ID
                    AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN')
             --   AND TRUNC (d.gl_date) <= TRUNC (p_start_date) --Added condition by nprashar for bug # 12665386
             ORDER BY 1, 4) a
   GROUP BY A.CUSTOMER_ID, A.customer_name, A.trx_date, ORG_ID

No comments:

Post a Comment

Script to update salespersons customer site wise in oracle apps R12

SELECT * FROM HZ_PARTIES WHERE PARTY_NAME LIKE 'DEENA VISION%'; SELECT * FROM HZ_CUST_ACCOUNTS_ALL WHERE PARTY_ID =94043 ; SE...