Tuesday, 25 September 2018

SQL Query for extracting the late fee data for customers in R12 Oracle Apps

SELECT aps.trx_number, aps.trx_date, cust_det.interest_rate,
                ROUND
                    (  cust_det.interest_rate
                     * (  amount_due_remaining
                        - (SELECT NVL (SUM (amount), 0)
                             FROM apps.ar_adjustments_all aaa,
                                  ar_receivables_trx_all art
                            WHERE 1 = 1
                              AND aaa.receivables_trx_id =
                                                        art.receivables_trx_id
                              AND NAME = 'DM Late Fee'
                              --RECEIVABLES_TRX_ID=44076
                              AND aaa.status = 'A'
                              AND aaa.customer_trx_id = aps.customer_trx_id)
                       )
                     / 100,
                     2
                    ) late_fee_charge,
                aps.gl_date, aps.due_date, aps.status, aps.CLASS,
                aps.amount_due_original, aps.amount_due_remaining,
                (SELECT NVL (SUM (amount), 0)
                   FROM apps.ar_adjustments_all aaa,
                        ar_receivables_trx_all art
                  WHERE 1 = 1
                    AND aaa.receivables_trx_id = art.receivables_trx_id
                    AND NAME = 'DM Late Fee'
                    AND aaa.status = 'A'
                    AND aaa.customer_trx_id = aps.customer_trx_id)
                                                             late_fee_amount,
                (  amount_due_remaining
                 - (SELECT NVL (SUM (amount), 0)
                      FROM apps.ar_adjustments_all aaa,
                           ar_receivables_trx_all art
                     WHERE 1 = 1
                       AND aaa.receivables_trx_id = art.receivables_trx_id
                       AND NAME = 'DM Late Fee'
                       --RECEIVABLES_TRX_ID=44076
                       AND aaa.status = 'A'
                       AND aaa.customer_trx_id = aps.customer_trx_id)
                ) amount_without_late_fee,
                aps.customer_id,
                ROUND (TO_DATE (pn_late_fee) - due_date) days_late,
                aps.customer_trx_id, aps.customer_site_use_id,
                cust_det.charge_begin_date, cust_det.account_number,
                cust_det.party_name, cust_det.LOCATION,
                cust_det.interest_charges, cust_det.interest_period_days,
                cust_det.late_charge_type, cust_det.party_site_number,
                cust_det.site_use_id, rct.ship_to_site_use_id,
                rct.cust_trx_type_id, rct.batch_source_id,
                   'Late Fee for '
                || ROUND (TO_DATE (pn_late_fee) - due_date)
                || ' Days'
                || ' Invoice # '
                || rct.trx_number
                || ' Invoice Amount '
                || aps.amount_due_original
                || ' Due Amount '
                || aps.amount_due_remaining
                || ' Due Date '
                || aps.due_date "REMARKS"
           FROM apps.ar_payment_schedules_all aps,
                (SELECT hcaa.account_number, hcaa.account_name, hp.party_name,
                        hca.attribute1, hcsua.site_use_code, hcsua.LOCATION,
                        hcaa.party_id, hcaa.attribute3,
                        hcp.cust_account_id cust_account_id,
                        hcp.cust_account_profile_id cust_account_profile_id,
                        hcpa.currency_code currency_code,
                        hcpa.cust_acct_profile_amt_id
                                                     cust_acct_profile_amt_id,
                        hcpa.exchange_rate_type exchange_rate_type,
                        hcp.object_version_number object_version_number,
                        hcpa.object_version_number amt_obn,
                        hcp.cust_account_profile_id, interest_charges,
                        late_charge_calculation_trx, credit_items_flag,
                        disputed_transactions_flag, payment_grace_days,
                        interest_period_days, late_charge_type,
                        interest_calculation_period,
                        hold_charged_invoices_flag,
                        multiple_interest_rates_flag, charge_begin_date,
                        charge_on_finance_charge_flag,
                        cust_acct_profile_amt_id, currency_code,
                        exchange_rate_type, min_fc_invoice_overdue_type,
                        min_fc_balance_overdue_type, min_fc_balance_amount,
                        min_fc_invoice_amount, min_interest_charge,
                        max_interest_charge, interest_type, interest_rate,
                        hps.party_site_number, hl.address1, hl.address2,
                        hl.address3, hl.city, hl.postal_code, hl.state,
                        hcpa.site_use_id
                   FROM apps.hz_cust_accounts_all hcaa,
                        apps.hz_parties hp,
                        apps.hz_cust_acct_sites_all hca,
                        apps.hz_cust_site_uses_all hcsua,
                        apps.hz_party_sites hps,
                        apps.hz_locations hl,
                        apps.hz_customer_profiles hcp,
                        apps.hz_cust_profile_amts hcpa
                  WHERE hp.party_id = hcaa.party_id
                    AND hca.cust_account_id = hcaa.cust_account_id
                    AND hp.party_id = hps.party_id
                    AND hca.party_site_id = hps.party_site_id
                    AND hps.location_id = hl.location_id
                    AND hcsua.cust_acct_site_id = hca.cust_acct_site_id
                    AND hcp.cust_account_profile_id =
                                                  hcpa.cust_account_profile_id
                    AND hcaa.cust_account_id = hcp.cust_account_id
                    AND hcp.site_use_id = hcsua.site_use_id
                    AND hcsua.site_use_code = 'BILL_TO'
                    AND hcsua.org_id = 82
                    AND hcp.interest_charges = 'Y') cust_det,
                apps.ra_customer_trx_all rct
          WHERE 1 = 1
            AND aps.customer_id = cust_det.cust_account_id
            AND aps.customer_site_use_id = cust_det.site_use_id
            AND aps.customer_trx_id = rct.customer_trx_id
            AND aps.trx_date >= '01-JAN-2018'
            -- AND aps.due_date >= '01-NOV-2017'
            AND aps.status = 'OP'
            AND aps.CLASS = 'INV'
            AND rct.batch_source_id = 3009
            AND aps.due_date >= cust_det.charge_begin_date
            AND ROUND (TO_DATE (pn_late_fee) - due_date) > 0
            AND cust_det.charge_begin_date IS NOT NULL;

No comments:

Post a Comment