Oracle Fusion HCM Cloud/EBS (HCM, Financial): ORACLE AR Technical

Customer API

Customer Bank Account APIs

How To Load Customer Bank Account using API (Doc ID 2076688.1)

CUSTOMER BANK ACCOUNTS CREATION SCRIPT

Single Insertion Scripts for R12

Auto AR Invoice Program

Sample PLSQL Script to call Autoinvoice Import Program RAXTRX using fnd_request.submit_request

Receivable Descriptive Flex Field

List of Receivable Descriptive Flex Field

List of Receivable Descriptive Flex Fields (DFF)

R12: How to enable DFF on Customer form and add Segments (OAF)

DB:4.36:Creating Dff On Customer Screen (Oaf Page) 8z

Account Receivables Tables effecting ?

Oracle Receivables : Useful AR

Transaction Form Item Name

arp_trx_line_util.get_item_flex_defaults(
                                l_inventory_item_id,
                                oe_profile.value('SO_ORGANIZATION_ID'),
                                art_context.pg_trx_date,
                                art_context.pg_invoicing_rule_id,
                                l_description,
                                l_primary_uom_code,
                                l_primary_uom_name,
                                l_accounting_rule_id,
                                l_accounting_rule_name,
                                l_accounting_rule_duration,
                                l_accounting_rule_type,
                                l_rule_start_date,
                                l_frequency );

Applied/Unapplied AR Invoices with Receipts

Relation between RA_CUSTOMER_TRX_ALL and AR_CASH_RECEIPTS_ALL

Finding all unapplied receipts in AR

Unapplied Receipts Amount as of date

Finding all unapplied receipts in AR

AR Query to get open invoices for single/All customers

How to Find the Link Between Transaction Number and Receipt Number in AR

Query to find open, unapplied Credit Memos in EBS Accounts Receivables (AR)

AR Receipt Applied on invoice Query

oracle ar unapplied receipts query

EXTERNAL BANK QUERY

Query - 1
SELECT ieba.* --bank_name
FROM iby_external_bank_accounts_v ieba, iby_external_payees_v iep
WHERE iep.payee_party_id = ieba.primary_acct_owner_party_id
AND iep.inactive_date IS NULL
AND iep.supplier_site_id = :vendor_site_id
AND iep.party_site_id = :party_site_id
AND ieba.ext_bank_account_id = :external_bank_account_id
AND ROWNUM = 1;

Query-2
SELECT DECODE (ieba.iban, NULL, ieba.bank_account_num_electronic, ieba.iban)
FROM iby_ext_bank_accounts ieba,
iby_external_payees_v iep,
iby_account_owners ow,
hz_parties op
WHERE iep.payee_party_id = ow.account_owner_party_id
AND ieba.ext_bank_account_id = ow.ext_bank_account_id(+)
AND ow.primary_flag(+) = 'Y' /AND NVL(ow.end_date,SYSDATE+10)>SYSDATE /
AND ow.account_owner_party_id = op.party_id(+)
AND iep.inactive_date IS NULL
AND supplier_site_id = :vendor_site_id
AND party_site_id = :party_site_id
AND ROWNUM = 1;

Query - 3
SELECT eb.ROWID, eb.ext_bank_account_id, eb.bank_id, eb.country_code,
bp.party_name, bapr.bank_or_branch_number, eb.branch_id, br.party_name,
brpr.bank_or_branch_number, branchca.class_code, s.location_id,
branchcp.eft_swift_code, eb.ext_bank_account_id, eb.bank_account_name,
eb.masked_bank_account_num, eb.bank_account_num_hash1,
eb.bank_account_num_hash2, eb.currency_code, eb.description,
eb.check_digits, DECODE (eb.currency_code, NULL, 'Y', 'N'),
eb.bank_account_name_alt, eb.short_acct_name, eb.account_suffix,
eb.masked_iban, eb.iban_hash1, eb.iban_hash2,
ow.account_owner_party_id, op.party_name, eb.account_classification,
eb.bank_account_type, eb.agency_location_code, eb.start_date,
eb.end_date, eb.payment_factor_flag, eb.foreign_payment_use_flag,
eb.exchange_rate_agreement_num, eb.exchange_rate_agreement_type,
eb.exchange_rate, eb.hedging_contract_reference,
eb.secondary_account_reference, eb.attribute_category, eb.attribute1,
eb.attribute2, eb.attribute3, eb.attribute4, eb.attribute5,
eb.attribute6, eb.attribute7, eb.attribute8, eb.attribute9,
eb.attribute10, eb.attribute11, eb.attribute12, eb.attribute13,
eb.attribute14, eb.attribute15, eb.object_version_number,
eb.bank_account_num_electronic, brpr.bank_code
FROM hz_organization_profiles bapr,
hz_organization_profiles brpr,
hz_parties bp,
hz_party_sites s,
iby_account_owners ow,
hz_parties br,
hz_parties op,
iby_ext_bank_accounts eb,
hz_code_assignments branchca,
hz_contact_points branchcp
WHERE eb.bank_id = bp.party_id(+)
AND eb.bank_id = bapr.party_id(+)
AND eb.branch_id = br.party_id(+)
AND eb.branch_id = brpr.party_id(+)
AND eb.ext_bank_account_id = ow.ext_bank_account_id(+)
AND ow.primary_flag(+) = 'Y' /AND NVL(ow.end_date,SYSDATE+10)>SYSDATE /
AND ow.account_owner_party_id = op.party_id(+)
AND (br.party_id = s.party_id(+))
AND (s.identifying_address_flag(+) = 'Y')
AND (branchcp.owner_table_name(+) = 'HZ_PARTIES')
AND (branchcp.owner_table_id(+) = eb.branch_id)
AND (branchcp.contact_point_type(+) = 'EFT')
AND (NVL (branchcp.status(+), 'A') = 'A')
AND (branchca.class_category(+) = 'BANK_INSTITUTION_TYPE')
AND (branchca.owner_table_name(+) = 'HZ_PARTIES')
AND (branchca.owner_table_id(+) = eb.branch_id)
AND SYSDATE BETWEEN NVL (TRUNC (bapr.effective_start_date), SYSDATE - 1)
AND NVL (TRUNC (bapr.effective_end_date), SYSDATE + 1)
AND SYSDATE BETWEEN NVL (TRUNC (brpr.effective_start_date), SYSDATE - 1)
AND NVL (TRUNC (brpr.effective_end_date), SYSDATE + 1)

Queries

Oracle R12 AR query for Invoice net amt, and VAT

Credit Memo and its invoice

Credit Memo against Invoice in r12 Receivables

AR Balances Query.html

List of Credit Memos/Cash Receipts applied against AR Transaction and Vice-Versa

Query Sales Order Number Against AR Receipt No.

AR : Back to Basic(Technical Foundation)

Oracle Apps Query

R12 query to check Credit Limit of a customer
How to find customer credit limit amount in Oracle Apps R12
Query to get the Outstanding Amount of Customers in Project Accounting
Oracle AR Outstanding Amount (Aging Report)

SELECT c.customer_number,c.customer_name,g.party_site_number,d.location,a.overall_credit_limit,
d.site_use_id,sum(e.amount_due_remaining) credit_balance
FROM   HZ_CUST_PROFILE_AMTS a, HZ_CUST_ACCOUNTS b, ar_customers c,
        hz_cust_site_uses_all d,ar_payment_schedules_all e,
              hz_cust_acct_sites_all f,hz_party_sites g
WHERE       overall_credit_limit IS NOT NULL
         and a.cust_account_id = b.cust_account_id
         and b.account_number = c.customer_number
         and a.site_use_id = d.site_use_id
         and c.customer_id = e.customer_id
         and d.site_use_id = e.customer_site_use_id
--         and c.customer_number = :customer_number
         and d.cust_acct_site_id = f.cust_acct_site_id
         and g.party_site_id = f.party_site_id
         and e.org_id = :org_id
GROUP BY c.customer_name,
c.customer_number,
d.site_use_id,
d.location,
a.overall_credit_limit,
g.party_site_number
ORDER BY CUSTOMER_NAME

XLA

AR Recipts to GL SQL including XLA

Drill Down from AR Cash Receipts to GL Ledger R12
Link XLA Tables With AR Tables To Get Information From GL
Link between GL and AR through XLA
Oracle Applications :Drill down from GL to AR Query

HZ Tables Queries for Customer

Code Snippets: Various Queries on Customer Data (HZ Tables)

SELECT hp.party_id, hp.party_type, hca.cust_account_id, hps.party_site_id,hps.party_site_number, hl.address_style, hl.address1, hl.address2,

hl.address3, hl.city, hl.state, hl.country, hpsu.site_use_type

FROM hz_parties hp,

hz_cust_accounts hca,

hz_party_sites hps,

hz_party_site_uses hpsu,

hz_cust_acct_sites_all hcas,

hz_locations hl,

hz_contact_points hcp_p,

hz_contact_points hcp_ps
WHERE hp.party_id = hps.party_id-- AND hp.party_type = 'ORGANIZATION'
AND hca.party_id = hp.party_id
AND hps.location_id = hl.location_id

AND hps.identifying_address_flag = 'Y'

AND hpsu.party_site_id(+) = hps.party_site_id

AND hpsu.status = 'A'

AND hca.cust_account_id = hcas.cust_account_id

AND hcas.party_site_id(+) = hps.party_site_id

AND (hcp_p.owner_table_id(+) = hp.party_id AND hcp_p.owner_table_name(+) =

'HZ_PARTIES')

AND (hcp_ps.owner_table_id(+) = hps.party_site_id

AND hcp_ps.owner_table_name(+) = 'HZ_PARTY_SITES')
-----------------------------------------------------------------------------------

WITH wd_hz_contact_points AS
     (SELECT *
        FROM (SELECT ROW_NUMBER () OVER (PARTITION BY owner_table_id, contact_point_type, phone_line_type ORDER BY owner_table_id,
                      contact_point_type, phone_line_type) rno,
                     hcp.*
                FROM hz_contact_points hcp
               WHERE hcp.status = 'A' AND hcp.owner_table_name = 'HZ_PARTY_SITES')
       WHERE rno = 1)
SELECT DISTINCT hca.account_number acc_code, hp.party_name acc_name, hcsu.creation_date open_date,
                rat.NAME credit_period, hcpa.trx_credit_limit credit_limit,
                   hl.address1
                || ' '
                || hl.address2
                || ' '
--       || hl.address3
                || ' '
                || hl.address4
                || ' '
                || hl.city
                || ' '
--       || hl.postal_code
                || ' '
                || hl.state
                || ' '
                || hl.province address,

--
                hcpt_phone.raw_phone_number acc_phone, hcpt_fax.raw_phone_number acc_fax, hcpt_email.email_address,
                hcpt_url.url web_site, hp_cont_per.party_name contact_person, hcpt_mob.raw_phone_number mobile_no,

--
                ft.territory_short_name country_code, hcpa.currency_code cur_code, zr.registration_number vat_trn
           FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts_all hca,
                hz_cust_acct_sites_all hcas,
                hz_cust_site_uses_all hcsu,
                ra_terms_tl rat,
                hz_customer_profiles hcpf,
                hz_cust_profile_amts hcpa,
                hz_locations hl,
                fnd_territories_tl ft,
                --
                hz_cust_account_roles hcar,
                ar_contacts_v acv,
                hz_parties hp_cont_per,
                --
                wd_hz_contact_points hcpt_phone,
                wd_hz_contact_points hcpt_fax,
                wd_hz_contact_points hcpt_mob,
                wd_hz_contact_points hcpt_email,
                wd_hz_contact_points hcpt_url,
                zx_party_tax_profile zptp,
                zx_registrations zr
          --
WHERE           1 = 1
            --
            AND hp.party_type = 'ORGANIZATION'                                          -- only ORGANIZATION Party types
            AND hp.status = 'A'
            --                                                                                    -- only Active Parties/Customers
            AND hcas.org_id = :p_org_id
--
            AND hp.party_id = hca.party_id
            AND hca.cust_account_id = hcas.cust_account_id(+)
            AND hps.party_site_id(+) = hcas.party_site_id
            AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
            AND hcsu.site_use_code = 'BILL_TO'
            --
            AND hcsu.payment_term_id = rat.term_id(+)
            AND rat.LANGUAGE(+) = USERENV ('LANG')
            --
            AND hcpf.cust_account_id(+) = hca.cust_account_id
            AND hcpf.site_use_id = hcsu.site_use_id(+)
            AND hcpf.cust_account_profile_id = hcpa.cust_account_profile_id(+)
            --
            AND hps.location_id = hl.location_id(+)
            --
            AND hl.country = ft.territory_code(+)
            AND ft.LANGUAGE(+) = USERENV ('LANG')
------(contact information)-------------------------
---------(person)
            AND hcar.cust_acct_site_id(+) = hcas.cust_acct_site_id
            AND acv.contact_id(+) = hcar.cust_account_role_id
            AND acv.contact_party_id = hp_cont_per.party_id
-----------(phone )----------
            AND hcpt_phone.owner_table_id(+) = acv.rel_party_id
--   AND hcpt_phone.primary_flag(+) = 'Y'
            AND hcpt_phone.contact_point_type(+) = 'PHONE'
            AND hcpt_phone.phone_line_type(+) = 'GEN'
---------------(FAX)----------
            AND hcpt_fax.owner_table_id(+) = acv.rel_party_id
--   AND hcpt_fax.primary_flag(+) = 'Y'
            AND hcpt_fax.contact_point_type(+) = 'PHONE'
            AND hcpt_fax.phone_line_type(+) = 'FAX'
---------------(mob )----------
            AND hcpt_mob.owner_table_id(+) = acv.rel_party_id
--   AND hcpt_mob.primary_flag(+) = 'Y'
            AND hcpt_mob.contact_point_type(+) = 'PHONE'
            AND hcpt_mob.phone_line_type(+) = 'MOBILE'
-------------(EMAIL)----------
            AND hcpt_email.owner_table_id(+) = acv.rel_party_id
            AND hcpt_email.primary_flag(+) = 'Y'
            AND hcpt_email.contact_point_type(+) = 'EMAIL'
-------------(URL)----------
            AND hcpt_url.owner_table_id(+) = acv.rel_party_id
            AND hcpt_url.primary_flag(+) = 'Y'
            AND hcpt_url.contact_point_type(+) = 'WEB'
----------(Tax Information)----------------
            AND zptp.party_id(+) = hcas.party_site_id
            AND zptp.party_type_code(+) = 'THIRD_PARTY_SITE'
            AND zr.party_tax_profile_id(+) = zptp.party_tax_profile_id
            AND zr.tax_regime_code(+) = 'UAE_TAX_REGIME'
            AND zr.effective_to(+) IS NULL
----------------------------------------

Query to get corresponding VAT tax from Customer and Customer Site Level
How to Return Customer Name, Customer No. Address, Various Tax Registration No. in Ar?
Customer's Phone, Email and URL
Query To Fetch Customer Account/Site Contact Details in R12
Query to get customer site contact details - R12
Need Customer Account Site Contact with Site Number and Site Id
customer query in EBS r12
R12 query to check Credit Limit of a customer
Query to find credit limit usage by customer?

hz_cust_site_uses_all Table

Code Snippets: Various Queries on Customer Data (HZ Tables)

useful-queriescodescripts-in-order

Receivable Queries

Payment Status Query

SELECT *
FROM (SELECT r.trx_number, CLASS, p.status, amount_due_original trx_amount,
               amount_due_remaining, NVL (applied_amt, 0) applied_amt,
               DECODE (NVL (applied_amt, 0),
                       0, 'UNPAID',
                       DECODE (applied_amt,
                               amount_due_original, 'PAID',
                               'PARTALLY PAID'
                              )
                      ) dn_status
          FROM ar_payment_schedules_all p,
               ra_customer_trx_all r,
               (SELECT   applied_customer_trx_id,
                         NVL (SUM (amount_applied), 0) applied_amt
                    FROM ar_receivable_applications_all a
                   WHERE 1 = 1 AND status = 'APP'
--                     AND gl_date BETWEEN :p_from_date AND :p_to_date
                GROUP BY applied_customer_trx_id) a
         WHERE 1 = 1
           AND r.customer_trx_id = p.customer_trx_id
           AND r.customer_trx_id = a.applied_customer_trx_id(+)
--           AND p.gl_date BETWEEN :p_from_date AND :p_to_date
--and r.customer_trx_id =rct.customer_trx_id --in (3782)
       )

-----------------------------------------------------------------------------------------------------
Supplier Payment Method

R12 Query to find supplier payment method

SELECT ieppm.payment_method_code
FROM ap_supplier_sites_all assa,
ap_suppliers sup,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds ieppm
WHERE sup.vendor_id = assa.vendor_id
AND assa.pay_site_flag = 'Y'
AND assa.vendor_site_id = iepa.supplier_site_id
AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE))
-- AND assa.vendor_site_id = :p_vendor_site_id
AND ieppm.primary_flag = 'Y'
ORDER BY sup.vendor_name, assa.vendor_site_code

---------------------------------------------------------------------

TAX Query

Query to extract AR Invoice-->Order Details-->Customer and ZX Registration Number --> COGS information (Mtl Trxns)

Below script can be used to create AR INVOICE using API.

Example Program:
CREATE OR REPLACE procedure APPS.xx_ar_invoice_api

l_return_status varchar2(1);

l_msg_count number;

l_msg_data varchar2(2000);

l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;

l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;

l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;

l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;

l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;

l_cust_trx_id number;

BEGIN

begin

MO_GLOBAL.SET_POLICY_CONTEXT('S',82);

end;

fnd_global.apps_initialize(1090,20678,222);

l_batch_source_rec.batch_source_id := 1001;

l_trx_header_tbl(1).trx_header_id := 9898;

l_trx_header_tbl(1).trx_date := sysdate;

l_trx_header_tbl(1).trx_currency := 'AED';

l_trx_header_tbl(1).cust_trx_type_id := 1000;

l_trx_header_tbl(1).bill_to_customer_id := 1139;

l_trx_header_tbl(1).term_id := 1000;

l_trx_header_tbl(1).finance_charges := 'N';

l_trx_header_tbl(1).status_trx := 'OP';

l_trx_header_tbl(1).printing_option := 'NOT';

--l_trx_header_tbl(1).reference_number := '1111';

l_trx_lines_tbl(1).trx_header_id := 9898;

l_trx_lines_tbl(1).trx_line_id := 101;

l_trx_lines_tbl(1).line_number := 1;

l_trx_lines_tbl(1).inventory_item_id := 1185;

-- l_trx_lines_tbl(1).description := 'CAST IRON
GRILL-325*485MM';

l_trx_lines_tbl(1).quantity_invoiced := 3;

l_trx_lines_tbl(1).unit_selling_price := 525; --Price

l_trx_lines_tbl(1).uom_code := 'EAC';

l_trx_lines_tbl(1).line_type := 'LINE';

l_trx_dist_tbl(1).trx_dist_id := 101;

l_trx_dist_tbl(1).trx_line_id := 101;

l_trx_dist_tbl(1).ACCOUNT_CLASS := 'REV';

l_trx_dist_tbl(1).percent := 100;

l_trx_dist_tbl(1).CODE_COMBINATION_ID := 1012;

--Here we call the API to create Invoice with the stored values

AR_INVOICE_API_PUB.create_invoice

(p_api_version => 1.0

--,p_commit => 'T'

,p_batch_source_rec => l_batch_source_rec

,p_trx_header_tbl => l_trx_header_tbl

,p_trx_lines_tbl => l_trx_lines_tbl

,p_trx_dist_tbl => l_trx_dist_tbl

,p_trx_salescredits_tbl => l_trx_salescredits_tbl

,x_return_status => l_return_status

,x_msg_count => l_msg_count

,x_msg_data => l_msg_data

);

dbms_output.put_line('Created:'||l_msg_data||l_return_status);

IF l_return_status = fnd_api.g_ret_sts_error OR

l_return_status = fnd_api.g_ret_sts_unexp_error THEN

dbms_output.put_line(l_return_status||':'||sqlerrm);

Else

dbms_output.put_line(l_return_status||':'||sqlerrm);

If (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL) Then

Dbms_output.put_line('Invoice(s) suceessfully created!') ;

Dbms_output.put_line('Batch ID: ' || ar_invoice_api_pub.g_api_outputs.batch_id);

Dbms_output.put_line('customer_trx_id: ' || l_cust_trx_id);

Else

Dbms_output.put_line(sqlerrm);

End If;

end if;

commit;

End;

Script to Create AR Invoice using API

Validations in AR Invoice:

AR Transaction Type Validation :
Check if the Transaction type provided in data file is defined in AR transaction types(RA_CUST_TRX_TYPES_ALL)

Transaction Batch Source Validation:
Check if the source provided in data file is defined in AR transaction Batch source (RA_BATCH_SOURCES_ALL).

Invoice Currency Validation:
Check if the currency provided in data file is defined in AR Currency (FND_CURRENCIES).

Customer Validation:
Check if the Bill to Customer Number Ship to Customer Number Bill to Custom Location Ship to Customer Location provided in the data file is defined in AR Customer(ra_customers).

Primary Sales Representative Validation:
Sales representative number to be hardcode to -3 for No Sales Credit.

Term Name :
Check if the Term name provided in the data file is defined in Payment terms (RA_TERMS)

Validate Sales Credit Type :
Sales Credit to hardcode to Quota Sales Credit

Inventory Item Validation:
Check if the Item provided in data file is defined in Inventory Items (MTL_SYSTEM_ITEMS).

Unit of Measurement validation:
Check if the UOM provided is defined in MTL_UNITS_OF_MEASURE Table

Invoice Tax Code Validation :
Check if the Tax Code provided in data file is defined in AR_VAT_TAX_ALL_B Table.

Invoice GL Date Validation :
Check if the GL Data of provided invoices is in open period.Used ARP_UTIL.IS_GL_DATE_VALID API to validate.

Oracle Fusion HCM Cloud/EBS (HCM, Financial)

ORACLE AR Technical