Oracle EBS Fundamentals – Simple Guide: AR

Showing posts with label AR. Show all posts

Derive Customer Account Contacts

SELECT

HP1.PARTY_NAME,

HP1.ADDRESS1,HP1.ADDRESS2,

HP1.CITY,

HP1.STATE,

HP1.COUNTRY,

HOC.JOB_TITLE,

HOC.JOB_TITLE_CODE

FROM FUSION.HZ_RELATIONSHIPS HR,

FUSION.HZ_PARTIES HP,

FUSION.HZ_CUST_ACCOUNTS HCA,

FUSION.HZ_PARTIES HP1,

FUSION.HZ_ORG_CONTACTS HOC

WHERE HR.SUBJECT_ID = HP.PARTY_ID

AND HP.PARTY_ID = HCA.PARTY_ID

AND HR.OBJECT_TYPE = 'PERSON'

AND HR.OBJECT_ID = HP1.PARTY_ID

AND HR.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID

AND HCA.ACCOUNT_NUMBER = :ACCOUNT_NUMBER;

How to find AR Customer extract query R12

SELECT DISTINCT hzp.party_name,
       hzp.orig_system_reference party_osr,
       hzp.party_number,
       hzp.party_type,
       hzp.duns_number,
       hzp.tax_reference,
       hzp.category_code,
       hzc.orig_system_reference cust_osr,
       hzc.account_number,
       hzc.account_name,
       hou.NAME ou_name,
       (SELECT a.NAME
          FROM ar.hz_cust_profile_classes a
         WHERE a.profile_class_id = hzcp.profile_class_id
        ) profile_class_name,
       (SELECT a.NAME
          FROM ar.ar_collectors a
         WHERE a.collector_id = hzcp.collector_id
        ) collector_name,
       (SELECT a.NAME
          FROM ar.ar_statement_cycles a
         WHERE statement_cycle_id = hzcp.statement_cycle_id
        ) statement_cycle_name,
       (SELECT a.NAME
     FROM apps.ra_terms a
    WHERE a.term_id = hzcp.standard_terms) payment_term_name,
       (SELECT a.NAME
          FROM ar.ra_grouping_rules a
         WHERE a.grouping_rule_id = hzcp.grouping_rule_id) grouping_rules_name,
       (SELECT a.orig_system_reference
          FROM hz_cust_site_uses_all a
         WHERE a.site_use_id = hzcp.site_use_id) profile_site_use_osr,
       hzcp.credit_checking,
       hzcp.tolerance,
       hzcp.credit_hold,
       hzcp.credit_balance_statements,
       hzcp.send_statements,
       hzcp.jgzz_attribute1 enable_late_charges,
       hzcp.interest_charges,
       hzcp.jgzz_attribute2 late_charge_calculation_trx,
       hzcp.jgzz_attribute5 hold_charged_invoices_flag,
       hzcp.dunning_letters,
       hzcp.discount_terms,
       hzcp.interest_period_days,
       hzcp.payment_grace_days,
       hzcp.discount_grace_days,
       hzcp.auto_rec_incl_disputed_flag,
       hzcp.attribute_category cust_prof_dff_category,
       hzcp.attribute2 cust_prof_dff_attr2,
       hzcpm.currency_code,
       hzcpm.trx_credit_limit,
       hzcpm.overall_credit_limit,
       hzcpm.min_dunning_amount,
       hzcpm.min_dunning_invoice_amount,
       hzcpm.max_interest_charge,
       hzcpm.min_statement_amount,
       hzcpm.auto_rec_min_receipt_amount,
       hzcpm.interest_rate,
       hzcpm.min_fc_balance_amount,
       hzcpm.min_fc_invoice_amount,
       (SELECT a.orig_system_reference
          FROM hz_cust_site_uses_all a
         WHERE a.site_use_id = hzcpm.site_use_id
       ) profile_amt_site_use_osr
      ---- ,hzcp.*
FROM ar.hz_parties             hzp,
       ar.hz_cust_accounts       hzc,
       ar.hz_cust_acct_sites_All has,
       ar.hz_customer_profiles   hzcp,
       ar.hz_cust_profile_amts   hzcpm,
       apps.hr_operating_units   hou
      ,iby_pmt_mthd_attrib_appl ibyappl
WHERE hzp.party_id                 = hzc.party_id
   AND hzc.cust_account_id          = hzcp.cust_account_id
   and hzcp.cust_account_profile_id = hzcpm.cust_account_profile_id(+)
   AND has.cust_account_id          = hzc.cust_Account_id
   AND has.org_id                   = hou.organization_id
   AND has.org_id          = NVL(:p_org_id,has.org_id)
   AND hzc.account_number = nvl(:p_cust_acct, hzc.account_number)
   -- Customer is active and created after cut off date

   AND ((trunc(hzc.creation_date) >'01-APR-2015' and hzc.status = 'A'))
   -- AR invoices created after cut off date
    or exists
               (SELECT 1
                  FROM ar.ra_customer_trx_all t
                 WHERE trunc(t.trx_date)>'01-MAY-2012'
                 and t.bill_to_customer_id = hzc.cust_account_id
                 and t.org_id = hzc.org_id
                 and hzc.status = 'A')
    -- AR invoice Payments are in open status
      or exists
               (SELECT 1
                  FROM ar.ar_payment_schedules_all apsa
                 WHERE apsa.CLASS IN ('INV', 'CM', 'DM','PMT')
                   AND apsa.status = 'OP'
                   AND apsa.org_id = hzc.org_id
                   AND apsa.customer_id = hzc.cust_account_id
                   AND to_number(apsa.acctd_amount_due_remaining) <>
                       to_number('0')
                 GROUP BY apsa.customer_id
                 having sum(nvl(apsa.acctd_amount_due_remaining,0))<>0))

Find AR Invoice Post to GL Query

---AR Invoice is Posted to GL Query

SELECT arc.customer_name,

rct.trx_number,

rct.trx_date,

rctt.name trx_name,

rctt.description,

rctt.TYPE,

SUM (extended_amount)

FROM ar_customers arc,

ra_customer_trx_all rct,

ra_customer_trx_lines_all rctl,

ra_cust_trx_types_all rctt

WHERE rct.customer_trx_id = rctl.customer_trx_id

AND rct.org_id = rctt.org_id

AND rct.cust_trx_type_id = rctt.cust_trx_type_id

AND rct.sold_to_customer_id = arc.customer_id

--AND RCTT.TYPE='INV'

AND rctl.line_type = 'LINE'

AND rct.complete_flag = 'Y'

AND rctt.post_to_gl = 'Y' --Post to GL

AND rctt.accounting_affect_flag = 'Y'

GROUP BY arc.customer_name,

rct.trx_number,

rct.trx_date,

rctt.name,

rctt.description,

rctt.type

AR Customer ALL Amount Details Query

Select sum (invoice_amount) , sum(pending_amt) pay, sum (on_account) advanc ,

(sum(pending_amt) - sum (on_account)) bal

From (

SELECT distinct ac.customer_name party_name, ac.customer_number,

TO_CHAR (rct.trx_date) gl_date,

TO_CHAR (ars.due_date) dutrm_date,

TO_NUMBER (rct.trx_date - TO_DATE (NVL (:p_to_date, SYSDATE))

) due_date,

(ars.amount_due_original) invoice_amount,

(ars.amount_due_remaining * NVL (ars.exchange_rate, 1)

) pending_amt,

TO_NUMBER ('') on_account,

rct.trx_number, rtl.TYPE

FROM ra_customer_trx_all rct,

ra_cust_trx_types_all rtl,

ar_payment_schedules_all ars,

ar_customers ac

WHERE rct.cust_trx_type_id = rtl.cust_trx_type_id

AND ars.customer_trx_id = rct.customer_trx_id

AND rct.customer_trx_id = ars.customer_trx_id

AND ars.amount_due_remaining <> 0

AND rct.bill_to_customer_id = ac.customer_id

AND ac.customer_number = NVL (:cust_id, ac.customer_number)

AND NVL(ars.gl_date,'31-DEC-2999') <=:p_end_date

UNION ALL

SELECT ac.customer_name party_name, ac.customer_number,

TO_CHAR (acr.receipt_date), TO_CHAR ('') dutrm_date,

TO_NUMBER ('') due_date, TO_NUMBER ('') invoice_amount,TO_NUMBER ('') pending_amt,

( NVL (acr.exchange_rate, 1)

* ( acr.amount

- nvl((SELECT SUM (NVL (amount_applied, 0))

FROM ar_receivable_applications_all ara

WHERE ara.cash_receipt_id = acr.cash_receipt_id and org_id=81

AND ara.gl_date <= :p_end_date

AND display = 'Y'

AND applied_customer_trx_id IS NOT NULL),0)

)

) "ON_ACCOUNT",

acr.receipt_number, '' TYPE

FROM ar_cash_receipts_all acr,

ar_customers ac,

hz_cust_accounts_all hca

WHERE acr.status = 'UNAPP'

AND acr.org_id=81

AND hca.cust_account_id = acr.pay_from_customer

AND hca.account_number = ac.customer_number

AND NVL(acr.receipt_date,'31-DEC-2999') <=:p_end_date

--AND ac.customer_number = NVL (:cust_id, ac.customer_number)

UNION ALL

SELECT ac.customer_name party_name, ac.customer_number,

TO_CHAR (acr.receipt_date),

TO_CHAR (ars.trx_number) invoice_num,

TO_NUMBER ('') dutrm_date, TO_NUMBER ('') due_date,

acr.amount invoice_amount,

(SUM (ara.amount_applied * NVL (acr.exchange_rate, 1))

) "ON_ACCOUNT",

acr.receipt_number, TO_CHAR ('') TYPE

FROM ar_cash_receipts_all acr,

ar_customers ac,

hz_cust_accounts_all hca,

ar_receivable_applications_all ara,

ar_payment_schedules_all ars

WHERE acr.status = 'APP'

AND ara.status = 'ACC'

AND ara.display = 'Y'

AND hca.cust_account_id = acr.pay_from_customer

AND ara.cash_receipt_id = acr.cash_receipt_id

AND hca.account_number = ac.customer_number

AND ac.customer_number = NVL (:cust_id, ac.customer_number)

AND ara.applied_payment_schedule_id = ars.payment_schedule_id

AND NVL(ara.gl_date,'31-DEC-2999') <=:p_end_date

--AND

GROUP BY ac.customer_name,

acr.receipt_date,

acr.receipt_number,

acr.amount,

trx_number,

ars.amount_due_remaining,

ars.amount_due_original,

ac.customer_number

) inv

GROUP BY inv.party_name, inv.customer_number

ORDER BY 1

AR Debtors Ledger(Outstanding Bills) ALL Query

--- Following Query for Inv, Credit memo . Debit Memo , Deposit
SELECT b.org_id,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, z.LOCATION
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,
hz_cust_site_uses_all z
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.segment4 = '208030101'
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.bill_to_site_use_id = z.site_use_id
AND b.org_id = NVL(:p_org_id,b.org_id)
--AND trunc(b.trx_date) between trunc(:p_start_date) AND trunc(:p_end_date)
AND TRUNC (g.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
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 b.org_id,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 amount,
(b.amount * NVL (b.exchange_rate, 1)) amount_other_currency,
'REC' TYPE, 0, 0, b.ROWID, z.LOCATION
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,
(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) BETWEEN TRUNC (:p_start_date)
AND TRUNC (:p_end_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 (TO_DATE (:p_start_date)))
AND acrh1.org_id = NVL(:p_org_id,acrh1.org_id)
GROUP BY acrh1.cash_receipt_id) g,
hz_cust_site_uses_all z
--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
--added the above 2 conditions by sanjikum for Bug # 3962497
AND f.cash_receipt_id = b.cash_receipt_id
AND b.customer_site_use_id = z.site_use_id
AND b.org_id = NVL(:p_org_id,b.org_id)
--AND e.current_record_flag = 'Y' --commented by sanjikum for Bug # 3962497
AND TRUNC (e.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
UNION
-- Following Query for Receipt WriteOff
SELECT b.org_id,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,
g.amount_applied amount,
(g.amount_applied * NVL (b.exchange_rate, 1)
) amount_other_currency, 'W/O' TYPE, 0, 0, b.ROWID, z.LOCATION
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,
hz_cust_site_uses_all z
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.customer_site_use_id = z.site_use_id
AND b.org_id = NVL(:p_org_id,b.org_id)
AND TRUNC (e.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
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 b.org_id,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, z.LOCATION
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,
hz_cust_site_uses_all z
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.customer_site_use_id = z.site_use_id
AND b.org_id = NVL(:p_org_id,b.org_id)
AND e.status = 'REVERSED'
AND TRUNC (e.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
AND b.reversal_date IS NOT NULL -- SSUMAITH
UNION
-- Following Query for Adjustments
SELECT b.org_id,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, z.LOCATION
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,
hz_cust_site_uses_all z
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.bill_to_site_use_id = z.site_use_id
AND c.org_id = NVL(:p_org_id,c.org_id)
--AND trunc(b.apply_date) between trunc(:p_start_date) AND trunc(:p_end_date)
AND TRUNC (b.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
UNION
-- -- Following Query for Discounts
SELECT b.org_id,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 amount,
d.acctd_earned_discount_taken amount_other_currency, 'DSC' TYPE,
b.customer_trx_id, 0, b.ROWID, z.LOCATION
FROM hz_parties a,
hz_cust_accounts hzca,
ra_customer_trx_all b,
ar_receivable_applications_all d,
hz_cust_site_uses_all z
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 = NVL(:p_org_id,b.org_id)
AND b.customer_trx_id = d.applied_customer_trx_id
AND d.application_type = 'CASH'
AND d.display = 'Y'
AND b.bill_to_site_use_id = z.bill_to_site_use_id
AND TRUNC (d.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
UNION ALL
-- Following Query for Exchange Gain and Loss
SELECT b.org_id,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, e.amount_cr) amount,
NVL (e.acctd_amount_dr, e.acctd_amount_cr) acctd_amount,
e.source_type, 0 customer_trx_id, 0 customer_trx_line_id, b.ROWID,
z.LOCATION
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,
hz_cust_site_uses_all z
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.bill_to_site_use_id = z.site_use_id
AND b.org_id = NVL(:p_org_id,b.org_id)
AND e.source_type IN ('EXCH_LOSS', 'EXCH_GAIN')
AND TRUNC (d.gl_date) BETWEEN TRUNC (:p_start_date) AND TRUNC
(:p_end_date)
ORDER BY 1, 4

AR Customer Prepayment Aging Report Query

SELECT customer_number, customer_name, paymt, day_030, day_031_060,
day_061_090, day_091_120, day_121_150, day_151_180, day_181, no_oftrx
FROM (SELECT pay.org_id, pay.customer_id
-- ,pay.trx_number, pay.trx_date
-- ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING,to_Date ('asofdate') - trunc(pay.trx_date)
,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date)) <=
30
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_030,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 31
AND 60
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_031_060,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 61
AND 90
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_061_090,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 91
AND 120
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_091_120,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 121
AND 150
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_121_150,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date))
BETWEEN 151
AND 180
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_151_180,
SUM
((CASE
WHEN (TO_DATE (:p_as_of_date) - TRUNC (pay.trx_date)) >=
181
THEN AMOUNT_DUE_ORIGINAL
END
)
) day_181,
COUNT (*) no_oftrx, ''
FROM apps.ar_payment_schedules_all pay
WHERE pay.CLASS IN ('INV','DM','CM','PMT')
AND pay.org_id = :p_org_id
AND pay.gl_date <= TO_DATE (:p_as_of_date)
AND pay.gl_date_closed > TO_DATE (:p_as_of_date)
AND EXISTS (
SELECT 'x'
FROM apps.gl_code_combinations gl,
apps.ra_cust_trx_line_gl_dist_all distgl
WHERE ( TRIM ((:p_acct_cd)) = '0'
OR gl.segment4 IN (:p_acct_cd)
) --gl.segment4 in (:P_ACCT_CD)
AND gl.code_combination_id = distgl.code_combination_id
AND distgl.account_class = 'REC'
AND distgl.customer_trx_id = pay.customer_trx_id)
GROUP BY pay.org_id, pay.customer_id
-- ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING ,pay.trx_number, pay.trx_date
) invag,
(SELECT pay.org_id, pay.customer_id,
SUM (receiptl.amount_applied) paymt
FROM apps.ar_payment_schedules_all pay,
ar_receivable_applications_all receiptl
WHERE pay.CLASS = 'PMT'
AND receiptl.status IN ('ACC', 'UNAPP')
AND pay.org_id = :p_org_id
AND pay.payment_schedule_id = receiptl.payment_schedule_id
AND pay.gl_date <= TO_DATE (:p_as_of_date)
AND pay.gl_date_closed > TO_DATE (:p_as_of_date)
GROUP BY pay.org_id, pay.customer_id) payment,
apps.ra_customers cust
WHERE cust.customer_id = invag.customer_id(+)
AND cust.customer_id = payment.customer_id(+)
AND (invag.org_id IS NOT NULL OR payment.org_id IS NOT NULL)

Find the Operating Unit with AR and AP and Company Code and Location

---Find the Operating Unit with AR and AP and Company code and Location

SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.flex_segment_value company_code,
                (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = arsp.org_id) ar_ou,
               (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = apsp.org_id) ap_ou
FROM xle_entity_profiles          lep,
       xle_registrations            reg,
       hr_locations_all             hrl,
       hz_parties                   hzp,
       fnd_territories_vl           ter,
       hr_operating_units           hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units        gloperatingunitseo,
       gl_legal_entities_bsvs       glev,
       ar_system_parameters_all     arsp,
       ap_system_parameters_all     apsp
WHERE lep.transacting_entity_flag        = 'Y'
   AND lep.party_id                       = hzp.party_id
   AND lep.legal_entity_id                = reg.source_id
   AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id                    = reg.location_id
   AND reg.identifying_flag               = 'Y'
   AND ter.territory_code                 = hrl.country
   AND lep.legal_entity_id                = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id          = hro.business_group_id
   AND hroutl_ou.organization_id          = hro.organization_id
   AND glev.legal_entity_id               = lep.legal_entity_id
   AND hro.set_of_books_id = arsp.set_of_books_id(+)
   AND hro.organization_id = arsp.org_id(+)
   AND hro.set_of_books_id = apsp.set_of_books_id(+)
   AND hro.organization_id = apsp.org_id(+)
Order by company_code

Order To Bill_to and Ship_to Customer Address

----------------------Bill To Customer-------------------------------
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'BILL_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID From oe_order_headers_all
      and hcsu.site_use_id=:INVOICE_TO_ORG_ID --225009 Select INVOICE_TO_ORG_ID From oe_order_headers_all

----------------------Ship To Customer-------------------------------
/* Formatted on 7/17/2017 3:05:46 PM (QP5 v5.115.810.9015) */
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'SHIP_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID From oe_order_headers_all
      and hcsu.site_use_id=:SHIP_TO_ORG_ID --Select SHIP_TO_ORG_ID From oe_order_headers_all

--- Upto INR 40000 off on Desktop CPUs & All in One Computers; No Cost EMI available

How to find Company Address in a database function

/***** Company Ship to Location *********/
Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE SHIP_TO_LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

/***** Company Bill to Location *********/

Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

/***** Find the Multiple Operating Unit *********/
SELECT LOCATION_ID FROM APPS.HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID FROM APPS.HR_OPERATING_UNITS HOU
WHERE HOU.NAME = NVL(:P_OPERATING_UNIT_NAME,HOU.NAME))

Redmi Note 10 | 48MP Quad Camera | AMOLED Display

How to Find Customers in Oracle Apps a Single Query or View

CREATE OR REPLACE FORCE VIEW APPS.PARTY_CUST_V
(
   PARTY_NAME,
   PARTY_NUMBER,
   ACCOUNT_NUMBER,
   CUST_ACCOUNT_ID,
   CUSTOMER_CATEGORY_CODE,
   PARTY_ID,
   PARTY_SITE_ID,
   LOCATION_ID,
   ADDRESS1,
   ADDRESS2,
   ADDRESS3,
   CITY,
   STATE,
   COUNTRY,
   POSTAL_CODE,
   GEOGRAPHY_NAME
   SITE_USE_CODE,
   SITE_USE_ID,
   ADDRESS_ID,
   PRIMARY_SALESREP_ID,
   SALES_PERSON,
   LOCATION,
   ORG_ID,
   BILL_TO_FLAG,
   CUST_ADDRESS,
   CUST_ACCT_SITE_ID
)
AS

   SELECT   ar.customer_name party_name,
            hp.party_number,
            hca.account_number,
            hca.cust_account_id,
            ar.CUSTOMER_CATEGORY_CODE,
            hp.party_id,
            hps.party_site_id,
            hps.location_id,
            hl.address1,
            hl.address2,
            hl.address3,
            hl.city,
            hl.state,
            hl.country,
            hl.postal_code,
            hg.GEOGRAPHY_NAME,
            hcsu.site_use_code,
            hcsu.site_use_id,
            hcsu.CUST_ACCT_SITE_ID address_id,
            hcsu.PRIMARY_SALESREP_ID,
            ( SELECT   resource_name
                 FROM   jtf_rs_defresources_v j, jtf_rs_salesreps s
                -- oe_order_headers_all h
                WHERE   j.resource_id = s.resource_id
                        AND s.salesrep_id = hcsu.PRIMARY_SALESREP_ID
             GROUP BY   resource_name)
               sales_person,
            hcsu.LOCATION,
            hcsa.ORG_ID,
            hcsa.bill_to_flag,
               SUBSTR (HL.ADDRESS1, 1, 60)
            || ' '
            || SUBSTR (HL.ADDRESS2, 1, 60)
            || ' '
            || LTRIM (
                  hl.city || ' ' || '-' || hl.postal_code || ' ' || hl.state
               )
               cust_address,
            hcsa.CUST_ACCT_SITE_ID
     FROM   ar_customers ar,
            hz_parties hp,
            hz_party_sites hps,
            hz_locations hl,
            hz_cust_accounts_all hca,
            hz_cust_acct_sites_all hcsa,
            hz_cust_site_uses_all hcsu,
            HZ_GEOGRAPHIES HG
    WHERE       hp.party_id = hps.party_id
            AND hps.location_id = hl.location_id
            AND hp.party_id = hca.party_id
            AND hcsa.party_site_id = hps.party_site_id
            AND hps.status = 'A'
            AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
            AND hg.geography_name=hl.country
            AND hcsa.status = 'A'
            AND hca.cust_account_id = hcsa.cust_account_id
            AND hcsu.status = 'A'
            AND ar.customer_id(+) = hca.cust_account_id
            AND ar.status <> 'I'
        --AND hca.cust_account_id=2068