Order To Cash Cycle

1. Enter Order in OM (Customer, Order type, Pricelist, shipping details, etc)
2. Enter the Items details and sourcing details in Order Line
3. Check for the availability and Book the Order
4. Schedule and Reserve the Order
5. Pick Release the Order (Reports here: Pick Selection List Generation, Pick Slip Report, Shipping Exception Report)
6. Ship confirm the order in Shipping transaction form (Reports here: Bill Of Ladding, Packing Slip Report, Commercial Invoice, Vehicle Load Sheet Details, Interface Trip Stop)
7. Line Status become Interfaced in Shipping Transaction form
8. Sales Order Line status will be Shipped
9. Run Workflow Background Concurrent request to close the workflow which in turn will close the order line.
10. This will trigger Autoinvoice Master program (inturn it will run Autoinvoice Import Program)
11. Then will trigger Prepayment Matching Program
12. Invoice (Transaction#) can be found in Account Receivables
13. After getting the Cash from customer, Create the Receipt.
14. After creation of the receipt, match the receipt with the invoice transaction
15. Once the receipt is matched, then the data can be transferred to General Ledger by running General ledger Transfer Program.
16. Then Journal can be Imported in GL by Import Journals
17. Then Journals can be posted to GL by Post Journals.

Note: You can also search for this in metalink, you can find excellent document published by Raju which has detailed steps alongwith screenshots and underlying base tables from OM, Shipping and INV modules

AR Key Tables Overview

AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on account credit, or receipt.

Oracle Receivables groups different transactions by the column CLASS. These classes include invoice (INV), debit memos (DM), guarantees (GUAR), credit memos (CM), deposits (DEP),
chargebacks (CB), and receipts (PMT).

When a receipt is applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS
and AMOUNT_DUE_REMAINING. STATUS changes from OP to CL for any transaction that has an AMOUNT_DUE_REMAINING value of 0.

ACTUAL_DATE_CLOSED and GL_DATE_CLOSED are populated with the date of the latest transaction. Oracle Receivables stores debit items such as invoices, debit memos, chargebacks, deposits, and guarantees as positive numbers in the AMOUNT_DUE_REMAINING and AMOUNT_DUE_ORIGINAL columns. Credit items such as credit memos and receipts are stored as
negative numbers.

If you create a debit memo reversal when you reverse a receipt, Oracle Receivables creates a new
payment schedule record for the debit memo and fills in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was reversed.


AR_RECEIVABLE_APPLICATIONS_ALL
This table stores records for cash and credit memo applications. The APPLICATION_TYPE column stores CASH for receipt applications and CM for credit memo applications. Each row in this table includes the amount applied, status (APP or UNAPP), and account code combination information.

When a cash receipt is initially created, Receivables creates a row in this table for the cash receipt amount with a status of UNAPP. For each subsequent application, Receivables creates two rows: one row with a status of APP for the amount that is applied to the invoice, and one row with status of UNAPP for the negative of the amount that is applied. If a cash application is reversed, Receivables creates two new rows: one row with status of APP for the original application's inverse amount (the negative of the original application amount), and one row with a status of UNAPP for the positive amount of the application that is reversed.
Credit memo applications do not have rows with a status of UNAPP, and use only rows with a status of APP.
The sum of the AMOUNT_APPLIED column for cash applications should always equal the amount of the cash receipt. A negative value in the AMOUNT_APPLIED column becomes a debit when this application is posted to General Ledger.

The CUSTOMER_TRX_ID and PAYMENT_SCHEDULE_ID columns also identify the transaction that is actually applied. The APPLIED_CUSTOMER_TRX_ID and APPLIED_PAYMENT_SCHEDULE_ID columns identify the invoice or credit memo that receives the application.

Calling Autolockbox from PL/SQL

declare
begin
v_request_id :=
fnd_request.submit_request
(application => 'AR',
program => 'ARLPLB',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => 'Y', -- P_NEW_TRANSMISSION
argument2 => '', -- P_TRANSMISSION_ID
argument3 => '', -- P_ORIGINAL_REQUEST_ID
argument4 => v_transmission_name,
argument5 => 'Y', -- P_RUN_IMPORT
argument6 => V_UTL_FILE_IN_DIR||'/'||P_DATA_FILE_NAME, -- DATA FILE PATH
argument7 => '', -- P_CONTROL_FILE_NAME
argument8 => v_transmission_format_id,
argument9 => 'Y', -- P_SUBMIT_VALIDATION
argument10 => 'N', -- P_PAY_UNRELATED_INVOICES
argument11 => v_lockbox_id,
argument12 => v_gl_date,
argument13 => 'A', -- P_REPORT_FORMAT
argument14 => 'N', -- P_COMPLETE_BATCHES_ONLY
argument15 => 'Y', -- P_SUBMIT_POST_BATCH
argument16 => 'N', -- P_ALTERNATE_NAME_SEARCH
argument17 => 'Y', -- P_APPLY_PARTIAL_OR_REJECT
argument18 => '', -- P_USSGL_TRANSACTION_CODE
argument19 => v_org_id,
argument20 => 'L',
argument21 => '',
CHR(0), CHR(0)
);
end;

AR AutoLockbox

AutoLockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. An AutoLockbox operation can process millions of transactions a month. AutoLockbox eliminates manual data entry by automatically processing receipts that are sent directly to your bank. You specify how you want this information transmitted and Receivables ensures that the data is valid before creating QuickCash receipt batches.

In more simpler words, Autolockbox is a program available in Oracle Receivables which helps you import payments or receipts. In real world, Banks would provide datafiles which would have information about the payments made by customer’s. Receivable’s autolock box program would help you in importing information from these datafiles into your system

Steps for Lockbox are:

1) Import: The first step involves reading and formatting data from your bank file into AutoLockbox tables. Basically here, a loader program loads data into AR_PAYMENT_INTERFACE_ALL table

2) Validation: The second step involves submitting the validation program which checks data in the AutoLockbox tables for compatibility with Receivables. Once data is validated, it is transferred into QuickCash tables. At this point, you can optionally query your receipts in the QuickCash window and change how they will be applied before submitting the final step.

This step moves the data into AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RECEIPT_LINES_ALLtables

3) Post QuickCash: Use the QuickCash window to review each receipt and use the Applications window to ensure that the application information is correct. After you approve the receipts and their applications, run Post QuickCash to update your customer's account balances. Match is Found or Not Found, Status will be UNAPPLIED.
A matching number can be a transaction number, consolidated billing invoice number, sales order number, purchase order number or another, custom defined number.

This moves the data into the AR base tables like- AR_CASH_RECEIPTS_ALL, AR_RECEIVABLES_APPLICATIONS_ALL, AR_CASH_RECEIPT_HISTORY_ALL
 
4) Action: After a receipt is successfully imported into Receivables, you can reverse and reapply them and apply any unapplied, unidentified, or on-account amounts just like a manually entered receipt

Transmission format

1) AutoLockbox uses the transmission format you specify in the Submit Lockbox Processing window to ensure that data is correctly transferred from the bank file into the AR_PAYMENTS_INTERFACE_ALL table.

2) Transmission formats contain information such as the customer number, bank account number, the amount of each receipt to apply, and transaction numbers to which to apply each receipt

3) Two Windows are important while doing this interface as below
• QuickCash window
• Maintain Transmission Data window
(Receipts that fail validation remain in the AR_PAYMENTS_INTERFACE table until you manually correct errors using the Maintain Transmission Data window)

4) After a receipt is successfully imported into Receivables, you can apply, reverse, remit, or place it on account, just like a manually entered receipt

Calling R12 AR Autoinvoice from PL/SQL

DECLARE
v_req_id NUMBER;
v_current_action VARCHAR2 (50);
v_responsibility_appl_id NUMBER;
v_responsibility_id NUMBER;
v_user_id NUMBER;
v_rtn NUMBER;
arg1 NUMBER := 1;
arg2 VARCHAR2 (50) := :p_org_id;
arg26 VARCHAR2 (50) := 'Y';
v_error_count NUMBER;
l_batch_source_id NUMBER;
l_batch_source_name VARCHAR2 (100);
l_temp_varchar VARCHAR2 (30) := '1';

dphase VARCHAR2 (30);
rphase VARCHAR2 (30);
rstatus VARCHAR2 (30);
dstatus VARCHAR2 (30);
MESSAGE VARCHAR2 (2000);
v_submit_status BOOLEAN;

BEGIN

v_current_action := 'apps initialize';
v_responsibility_appl_id := apps.fnd_profile.VALUE ('RESP_APPL_ID');
v_responsibility_id := apps.fnd_profile.VALUE ('RESP_ID');
DBMS_OUTPUT.put_line ('RESP_ID-' || v_responsibility_id);
DBMS_OUTPUT.put_line ('RESP_APPL_ID-' || v_responsibility_appl_id);
apps.fnd_global.apps_initialize (:p_user_id, v_responsibility_id, v_responsibility_appl_id);
apps.fnd_client_info.set_org_context (:p_org_id);

BEGIN
SELECT batch_source_id, NAME
INTO l_batch_source_id, l_batch_source_name
FROM ra_batch_sources_all
WHERE NAME = :p_source AND org_id = :p_org_id;
-- vm changing this be count from interface lines & increasing limit from 3000 to 8000
SELECT TO_CHAR (LEAST (10, (CEIL ((COUNT (*)) / 8000))))
INTO l_temp_varchar
FROM ra_interface_lines_all
WHERE batch_source_name = :p_source AND org_id = :p_org_id;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

DBMS_OUTPUT.put_line ( l_temp_varchar || ',' || l_batch_source_id || ',' || l_batch_source_name);

IF (l_temp_varchar != '0') THEN
v_rtn :=
apps.FND_REQUEST.SUBMIT_REQUEST
( 'AR'
,'RAXMTR'
,''
,''
,FALSE
, arg1 --arg1
, arg2 --arg2
, l_batch_source_id --arg3
, l_batch_source_name --arg4
,to_char( sysdate , 'YYYY/MM/DD HH:MI:SS' ) --arg5
,'' --arg6
,'' --arg7
,'' --arg8
,'' --arg9
,'' --arg10
,'' --arg11
,'' --arg12
,'' --arg13
,'' --arg14
,'' --arg15
,'' --arg16
,'' --arg17
,'' --arg18
,'' --arg19
,'' --arg20
,'' --arg21
,'' --arg22
,'' --arg23
,'' --arg24
,'' --arg25
,arg26 --arg26
,'' --arg27
,CHR(0) --arg28
,CHR(0) --arg29
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg37
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg47
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg57
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg67
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg77
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg87
,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg97
,CHR(0),CHR(0),CHR(0));

COMMIT;

IF v_rtn = 0 THEN
DBMS_OUTPUT.put_line ('Submit request ended with error');
DBMS_OUTPUT.put_line ('Error message : ' || MESSAGE);
ELSE
DBMS_OUTPUT.put_line ('Submit request submitted successfully');
v_submit_status :=
fnd_concurrent.get_request_status
(v_rtn,
'',
'',
rphase,
rstatus,
dphase,
dstatus,
MESSAGE
);

IF dphase = 'COMPLETE'
THEN
DBMS_OUTPUT.put_line ('Sub Request completed successfully');
ELSE
LOOP
--- Check the status
v_submit_status :=
fnd_concurrent.wait_for_request
(v_rtn,
100,
80,
rphase,
rstatus,
dphase,
dstatus,
MESSAGE
);
EXIT WHEN dphase = 'COMPLETE';
END LOOP;
END IF;

END IF;
END IF;
END;

AR Customer API

Record Type Variable Declaration
v_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
v_customer_account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
v_party_rec HZ_PaRTY_V2PUB.party_rec_type;
v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
v_location_rec hz_location_v2pub.location_rec_type;
v_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
v_party_site_use_rec HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
v_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
v_customer_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
v_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;

Note- Columns values are assigned to record type variable from various Cursor Staging Tables

-- Create the Party Organization Record
v_organization_rec.organization_name := rec_party.customer_name;
v_organization_rec.party_rec.orig_system_reference := rec_party.orig_system_reference ;
v_organization_rec.party_rec.attribute2 := rec_party.attribute2;
v_organization_rec.party_rec.attribute3 := rec_party.attribute3;
v_organization_rec.party_rec.attribute4 := rec_party.attribute4;
v_organization_rec.party_rec.attribute5 := rec_party.attribute5;
v_organization_rec.created_by_module := 'TCA_V1_API';

-- Create Customer Account Record
v_customer_account_rec.account_name:= rec_party.customer_name;
v_customer_account_rec.account_number:= rec_party.customer_number;
v_customer_account_rec.orig_system_reference := rec_party.orig_system_reference;
v_customer_account_rec.created_by_module:='TCA_V1_API';

-- Create Customer Profile Record
v_customer_profile_rec.party_id := v_party_id;
v_customer_profile_rec.status := rec_profile.status;
v_customer_profile_rec.credit_checking := rec_profile.credit_checking;
v_customer_profile_rec.tolerance := rec_profile.tolerance;
v_customer_profile_rec.discount_terms := rec_profile.discount_terms;
v_customer_profile_rec.dunning_letters := rec_profile.dunning_letters;
v_customer_profile_rec.interest_charges := rec_profile.interest_charges;
v_customer_profile_rec.credit_balance_statements := rec_profile.credit_balance_statements;
v_customer_profile_rec.credit_hold := rec_profile.credit_hold;
v_customer_profile_rec.credit_rating := rec_profile.credit_rating;
v_customer_profile_rec.risk_code := rec_profile.risk_code;
v_customer_profile_rec.override_terms := rec_profile.override_terms;
v_customer_profile_rec.payment_grace_days := rec_profile.payment_grace_days;
v_customer_profile_rec.discount_grace_days := rec_profile.discount_grace_days;
v_customer_profile_rec.account_status := rec_profile.account_status;
v_customer_profile_rec.auto_rec_incl_disputed_flag := rec_profile.auto_rec_incl_disputed_flag;
v_customer_profile_rec.tax_printing_option := rec_profile.tax_printing_option;
v_customer_profile_rec.jgzz_attribute5 := rec_profile.jgzz_attribute5;
v_customer_profile_rec.jgzz_attribute6 := rec_profile.jgzz_attribute6;
v_customer_profile_rec.created_by_module := 'TCA_V1_API';

Below API creates record for Party organization, customer account and customer profile all in one

HZ_CUST_ACCOUNT_V2PUB.create_cust_account
(
p_init_msg_list =>FND_API.G_FALSE,
p_cust_account_rec => v_customer_account_rec, -- Customer Account Record
p_organization_rec => v_organization_rec, -- Party Organization Record
p_customer_profile_rec => v_customer_profile_rec, -- Customer Profile Record
p_create_profile_amt => FND_API.G_FALSE,
x_cust_account_id => v_customer_account_id,
x_account_number => v_customer_account_number,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Profile Amount Record
v_customer_profile_amt.cust_account_profile_id := v_cust_account_profile_id;
v_customer_profile_amt.cust_account_id := v_customer_account_id;
v_customer_profile_amt.currency_code := rec_customer_prof_amt_staging.currency_code;
v_customer_profile_amt.trx_credit_limit := rec_customer_prof_amt_staging.trx_credit_limit;
v_customer_profile_amt.overall_credit_limit := rec_customer_prof_amt_staging.overall_credit_limit;
v_customer_profile_amt.min_dunning_amount := rec_customer_prof_amt_staging.min_dunning_amount;
v_customer_profile_amt.min_statement_amount := rec_customer_prof_amt_staging.min_statement_amount;
v_customer_profile_amt.created_by_module := 'TCA_V1_API';

HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt
(
p_init_msg_list => 'T' ,
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt,
x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Location Record
v_location_rec.address1 := rec_addresses.address1;
v_location_rec.address2 := rec_addresses.address2;
v_location_rec.address3 := rec_addresses.address3;
v_location_rec.address4 := rec_addresses.address4;
v_location_rec.city := rec_addresses.city;
v_location_rec.country := rec_addresses.country;
v_location_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_location_rec.postal_code := rec_addresses.postal_code;
v_location_rec.created_by_module :='TCA_V1_API';

HZ_LOCATION_V2PUB.create_location
(
p_init_msg_list => 'T',
p_location_rec => v_location_rec,
x_location_id => v_location_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Party Site Record
v_party_site_rec.party_id := v_party_id;
v_party_site_rec.location_id := v_location_id;
v_party_site_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_party_site_id := NULL;
v_party_site_rec.created_by_module := 'TCA_V1_API';

HZ_PARTY_SITE_V2PUB.create_party_site
(
p_init_msg_list => 'T',
p_party_site_rec => v_party_site_rec,
x_party_site_id => v_party_site_id,
x_party_site_number => v_party_site_no,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Contact Point and Phone Record
v_contact_point_rec.contact_point_type := 'PHONE';
v_contact_point_rec.status := rec_phones.status;
v_contact_point_rec.owner_table_name := 'HZ_PARTIES';
v_contact_point_rec.owner_table_id := v_party_id;
v_contact_point_rec.primary_flag := rec_phones.primary_flag;
v_contact_point_rec.orig_system_reference := rec_phones.orig_system_reference;
v_contact_point_rec.created_by_module := 'TCA_V1_API';
v_phone_rec.phone_number := rec_phones.phone_number;
v_phone_rec.phone_line_type := rec_phones.phone_type;

HZ_CONTACT_POINT_V2PUB.create_contact_point
(
p_init_msg_list => 'T',
p_contact_point_rec => v_contact_point_rec,
p_phone_rec => v_phone_rec,
x_contact_point_id => v_contact_point_party_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Person Record
v_person_rec.person_first_name := rec_contacts.first_name;
v_person_rec.person_last_name := rec_contacts.last_name;
v_person_rec.party_rec.orig_system_reference := rec_contacts.orig_system_reference;
v_person_rec.created_by_module := 'TCA_V1_API';

HZ_PARTY_V2PUB.create_person
(
p_init_msg_list => 'T',
p_person_rec => v_person_rec,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_party_id => v_person_party_id,
x_party_number => v_person_party_number,
x_profile_id => v_profile_id
);

-- Create Org Contact Record

v_org_contact_rec.created_by_module := 'TCA_V1_API';
v_org_contact_rec.job_title := rec_contacts.job_title;
v_org_contact_rec.party_rel_rec.subject_id := v_person_party_id ;
v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.object_id := v_party_id ;
v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
v_org_contact_rec.party_rel_rec.start_date := SYSDATE;

HZ_PARTY_CONTACT_V2PUB.create_org_contact
(
p_init_msg_list => 'T',
p_org_contact_rec => v_org_contact_rec,
x_org_contact_id => v_org_contact_id,
x_party_rel_id => v_party_rel_id,
x_party_id => v_party_contact_id,
x_party_number => v_party_rel_number,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
) ;

-- Create Party Site Use Record

v_party_site_use_rec.site_use_type := rec_customer_sites_staging.site_use_code;
v_party_site_use_rec.party_site_id := v_party_site_id;
v_party_site_use_rec.primary_per_type := rec_customer_sites_staging.primary_flag;
v_party_site_use_rec.status := rec_customer_sites_staging.status;
v_party_site_use_rec.created_by_module := 'TCA_V1_API';

HZ_PARTY_SITE_V2PUB.create_party_site_use
(
p_init_msg_list => 'T',
p_party_site_use_rec => v_party_site_use_rec,
x_party_site_use_id => v_party_site_use_id ,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Account Site Record
v_cust_acct_site_rec.cust_account_id := v_customer_account_id;
v_cust_acct_site_rec.party_site_id := v_party_site_id;
v_cust_acct_site_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_cust_acct_site_rec.status := rec_customer_sites_staging.status;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';

HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site
(
p_init_msg_list => 'T',
p_cust_acct_site_rec=> v_cust_acct_site_rec,
x_cust_acct_site_id => v_cust_acct_site_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Account Site Use Record
v_customer_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := rec_customer_sites_staging.site_use_code;
v_customer_site_use_rec.status := rec_customer_sites_staging.status;
v_customer_site_use_rec.orig_system_reference :=rec_addresses.orig_address_reference;
v_customer_site_use_rec.primary_flag :=rec_customer_sites_staging.primary_flag;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';

HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use
(
p_init_msg_list => 'T',
p_cust_site_use_rec => v_customer_site_use_rec,
p_customer_profile_rec => v_customer_profile_rec,
p_create_profile => FND_API.G_FALSE,
p_create_profile_amt => FND_API.G_FALSE,
x_site_use_id => v_site_use_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Profile Amount Record

v_customer_profile_amt := NULL;
v_customer_profile_amt.cust_account_profile_id := v_cust_account_profile_id;
v_customer_profile_amt.cust_account_id := v_customer_account_id;
v_customer_profile_amt.currency_code := rec_cust_prof_amt_stage_sites.currency_code;
v_customer_profile_amt.trx_credit_limit := rec_cust_prof_amt_stage_sites.trx_credit_limit;
v_customer_profile_amt.overall_credit_limit := rec_cust_prof_amt_stage_sites.overall_credit_limit;
v_customer_profile_amt.min_dunning_amount := rec_cust_prof_amt_stage_sites.min_dunning_amount;
v_customer_profile_amt.min_statement_amount := rec_cust_prof_amt_stage_sites.min_statement_amount;
v_customer_profile_amt.site_use_id := v_site_use_id;
v_customer_profile_amt.created_by_module := 'TCA_V1_API';

HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt
(
p_init_msg_list => 'T' ,
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt,
x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- If you want to Create Organization separately in some other program, use below API for reference

HZ_PARTY_V2PUB.create_organization
(
p_init_msg_list => 'T',
p_organization_rec => v_organization_rec,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id
);

AR-GL Reconciliation

The "AR Reconciliation Report" shows the summary amounts of various AR reports, that are used to reconcile AR. This report mainly has 3 parts
a) Period Beginning Balance
b) Period Activity in different Areas and their differences
c) Period Ending Balance

AR is reconciled when the
"Aging on the beginning of the Period" +
"TheTotal Activity in the Period" =
"Aging on the end of thePeriod"

Total Activity in a period is calculated as follows:Transaction Register for the Period
(-)Applied Receipts Register for the Period
(-)Un-Applied Receipts Register for the Period
(+)Adjustments Register for the Period
(-)Invoice Exceptions for the Period
(+)Rounding Differences for the Period (*)
(+)Credit Memo gain/loss for the Period(*)

(*) These can occur only in the case of foreign currency transactions.

AR Balances Query

All Below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Customer Transaction Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date AND cust_acct.account_number = :p_account_number AND ps.customer_id = cust_acct.cust_account_id AND ps.cust_trx_type_id = rtt.cust_trx_type_id AND ps.trx_date <= :p_as_of_date AND ps.CLASS NOT IN ('CM', 'PMT') AND site_uses.site_use_code = 'BILL_TO' AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND NVL (site_uses.status, 'A') = 'A' AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND ps.customer_id = acct_site.cust_account_id AND ps.customer_site_use_id = site_uses.site_use_id AND rta.customer_trx_id = ps.customer_trx_id AND rta.customer_trx_id = rgld.customer_trx_id AND rgld.code_combination_id = cc.code_combination_id AND rgld.account_class = 'REV'

-- Query for Credit Memo Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) cr_memo_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'CM'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'

AR Receipt Queries

All Below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Total On Account Receipt Amount
SELECT NVL (SUM (ps.amount_due_remaining), 0) total_onacct_receipts
FROM hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
ar_receivable_applications_all arr,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
ar_cash_receipts_all acr,
ar_cash_receipt_history_all crh,
gl_code_combinations cc
WHERE TRUNC (ps.gl_date) <= :p_as_of_date AND ps.customer_id = cust_acct.cust_account_id AND cust_acct.account_number = :p_account_number AND ps.customer_id = cust_acct.cust_account_id AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND ps.cash_receipt_id = acr.cash_receipt_id AND acr.cash_receipt_id = crh.cash_receipt_id AND crh.account_code_combination_id = cc.code_combination_id AND ps.trx_date <= :p_as_of_date AND ps.CLASS = 'PMT' AND ps.cash_receipt_id = arr.cash_receipt_id AND arr.status IN ('ACC') AND ps.status = 'OP' AND site_uses.site_use_code = 'BILL_TO' AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id AND NVL (site_uses.status, 'A') = 'A' AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND ps.customer_id = acct_site.cust_account_id AND ps.customer_site_use_id = site_uses.site_use_id HAVING NVL (SUM (arr.amount_applied), 0) > 0;

-- Query for Total Unapplied Receipt Amount

SELECT NVL (SUM (arr.amount_applied), 0) total_unapp_receipts
FROM hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
ar_receivable_applications_all arr,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
ar_cash_receipts_all acr,
ar_cash_receipt_history_all crh,
gl_code_combinations cc
WHERE TRUNC (ps.gl_date) <= :p_as_of_date AND ps.customer_id = cust_acct.cust_account_id AND cust_acct.account_number = :p_account_number AND ps.customer_id = cust_acct.cust_account_id AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND ps.cash_receipt_id = acr.cash_receipt_id AND acr.cash_receipt_id = crh.cash_receipt_id AND crh.account_code_combination_id = cc.code_combination_id AND ps.trx_date <= :p_as_of_date AND ps.CLASS = 'PMT' AND ps.cash_receipt_id = arr.cash_receipt_id AND arr.status = 'UNAPP' AND ps.status = 'OP' AND site_uses.site_use_code = 'BILL_TO' AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id AND NVL (site_uses.status, 'A') = 'A' AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND ps.customer_id = acct_site.cust_account_id AND ps.customer_site_use_id = site_uses.site_use_id HAVING NVL (SUM (arr.amount_applied), 0) > 0;

-- Query for Total Uncleared Receipt Amount

SELECT NVL (SUM (ps.amount_due_remaining), 0) total_uncleared_receipts
FROM hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
ar_receivable_applications_all arr,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all acr,
gl_code_combinations cc
WHERE TRUNC (ps.gl_date) <= :p_as_of_date AND ps.customer_id = cust_acct.cust_account_id AND cust_acct.account_number = :p_account_number AND ps.customer_id = cust_acct.cust_account_id AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND ps.cash_receipt_id = acr.cash_receipt_id AND acr.cash_receipt_id = crh.cash_receipt_id AND crh.account_code_combination_id = cc.code_combination_id AND ps.trx_date <= :p_as_of_date AND ps.CLASS = 'PMT' AND ps.cash_receipt_id = arr.cash_receipt_id AND arr.status = 'UNAPP' AND ps.status = 'OP' AND site_uses.site_use_code = 'BILL_TO' AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id AND NVL (site_uses.status, 'A') = 'A' AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND ps.customer_id = acct_site.cust_account_id AND ps.customer_site_use_id = site_uses.site_use_id AND ps.cash_receipt_id = crh.cash_receipt_id AND crh.status NOT IN ('CLEARED') HAVING NVL (SUM (arr.amount_applied), 0) > 0;

AR Receipt API

AR_RECEIPT_API_PUB.CREATE_CASH
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_COUNT,
X_MSG_DATA => L_MSG_DATA,
P_CURRENCY_CODE => V_CUR_DATA.RECEIPT_CURRENCY_CODE, P_AMOUNT => V_CUR_DATA.UNAPPLIED_AMOUNT,
P_RECEIPT_NUMBER => V_CUR_DATA.RECEIPT_NUMBER,
P_RECEIPT_DATE => V_CUR_DATA.RECEIPT_DATE,
P_GL_DATE => V_CUR_DATA.RECEIPT_DATE,
P_CUSTOMER_NUMBER => V_CUR_DATA.CUSTOMER_NUMBER, P_CUSTOMER_SITE_USE_ID => V_CUR_DATA.SITE_USE_ID, P_REMITTANCE_BANK_ACCOUNT_ID => V_BANK_ACC_ID, P_RECEIPT_METHOD_ID => V_RECEIPT_METHOD_ID,
P_CR_ID => L_CASH_RECEIPT_ID, -- Out Parameter
P_ORG_ID => FND_PROFILE.VALUE ('ORG_ID'));
-----------------------------------------------------------
AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS => L_RETURN_STATUS_ACC, X_MSG_COUNT => L_MSG_COUNT_ACC,
X_MSG_DATA => L_MSG_DATA_ACC,
p_cash_receipt_id =>L_CASH_RECEIPT_ID,
p_amount_applied =>V_CUR_DATA.ON_ACCOUNT_AMOUNT, p_apply_date => V_CUR_DATA.RECEIPT_DATE,
p_apply_gl_date => V_CUR_DATA.RECEIPT_DATE,
p_org_id => FND_PROFILE.VALUE ('ORG_ID'));

Customer Interface Performance Tips

1) Define "HZ: Number of Workers Used by Customer Interface"

2) Use Customer Interface Master Conc Program (RACUSTMA) instead of "Customers Interface" (RACUST)

3) Set "HZ: Gather Table Stats" the Profile Option set to "YES".

4) Run Gather Schema Stats program and Rebuild all AR Indexes with online clause

Transaction Conversion Error/Resolution

**ERRORraapem: no errors recordedraavgi()+ Current system time is 11-JUN-2009 06:21:11raavgi()- Current system time is 11-JUN-2009 06:21:12raapem: 0 <227439 you="you">/u03/dev1/apps/apps_st/appl/ar/12.0.0/bin/RAXTRXProgram was terminated by signal 11

**SOLUTION
This Error comes up if CODE_COMBINATION_ID is Null for any of the Transaction in Interface Table - RA_INTERFACE_DISTRIBUTIONS_ALL. Setup Receivables & Revenue Account properly for all Transaction Types, so that Correct CCID gets populated in Interface Table and we will get rid of this Signal 11 Error.

**ERRORERROR: from eBusiness Tax - return code = 2 Current system time is 08-MAY-2009 13:02:07Please review the plsql debug log for additional details. Current system time is 08-MAY-200913:02:07Error calling raaebt()Error calling raapic()Error.No.2)ZX_API_PUB.import_document_with_tax returns failureAn unexpected error has occurred. Please contact your system administrator.arp_etax_autoinv_util.calculate_tax()-

**SOLUTIONRun the following scripts in a TEST environment first and then to PROD:Please run ARHGEFTR.sql manually from sqlplus Dir: $AR_TOP/patch/115/sql/This will synch the data between fnd_territories_vl and hz_geographies.

**ERRORcalling raapem to record error
raapem: 2 <31486 the="the">

**SOLUTIONPrecision Length is Zero for Currency Codes. Increase the length to 2.

**ERRORError calling arcdsu_do_setup()
Error calling raaini()
+---------------------------------------------------------------------------+
No completion options were requested.
AR_NO_PROFILE
+---------------------------------------------------------------------------+
Exceptions posted by this request:
Concurrent Request for "Autoinvoice Import Program" has completed with error.

**SOLUTIONComplete the Setup as per Metalink Doc ID- 733835.1 (Point 5A & 7A)

**ERRORAPP-AR-11526: ORA-01458: invalid length inside variable character string
select NAME ,DECODE(CUST_TRX_TYPE_RULE,'Id','I','R') ,DECODE(BILL_CUST
APP-AR-11526: 7207185/ar/lib/raamtr.opc 371

**SOLUTIONExtend the Buffer size in AR System Options screen(N) setup->system -> Options (Tab: Trans. and customers->)Set - Max memory in bytes = 3000000 and save changes.This setup step will prevent the error.

AR Purging

There are 2 Types of Purging in Oracle Receivables:
1) Autoinvoice Interface Purge
2) Transaction Data Archive & Purge

Autoinvoice Interface Purge
1) Login to corresponding AR Purge User responsibility

2) Run ‘Autoinvoice Purge Program’ from SRS window

3) The program deletes the data from following tables for specific operating unit attached to the responsibility
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_ERRORS_ALL

Transaction Data Archive & Purge
1) Login to corresponding AR Archive & Purge User responsibility

2) Run ‘Call New Archive and Purge’ program from SRS window which will eventually calls ‘New Archive and Purge’ program

3) This program archives the data in tables- AR_ARCHIVE_HEADER & AR_ARCHIVE_DETAIL and deletes transaction data from following base tables:
AR_ACTION_NOTIFICATIONS
AR_ADJUSTMENTS
AR_BATCHES
AR_CALL_ACTIONS
AR_CASH_BASIS_DISTRIBUTIONS
AR_CASH_RECEIPTS
AR_CASH_RECEIPT_HISTORY
AR_CORRESPONDENCE_PAY_SCHED
AR_CUSTOMER_CALL_TOPICS
AR_MISC_CASH_DISTRIBUTIONS
AR_NOTES
AR_PAYMENT_SCHEDULES
AR_RATE_ADJUSTMENTS
AR_RECEIVABLE_APPLICATIONS
RA_BATCHES
RA_CUSTOMER_TRX
RA_CUSTOMER_TRX_LINES
RA_CUST_TRX_LINE_GL_DIST
RA_CUST_TRX_LINE_SALESREPS
AR_CORRESPONDENCES
AR_DISTRIBUTIONS
4) If in case, Archive & Purge program fails, you can submit ‘Archive-Restart’ program from SRS window. It can be used to save the system from having to revalidate all purge candidates, if Archive & Purge has completed the selection and validation phase, then fails during the archive phase. Archive-Restart clears the Archive Header and Detail tables and submits the archive report. When submitting the Archive-Restart program you must provide the following parameters: Archive Level, Summary Report Only, Number of Workers, Commit Size, and Archive Id

5) Run ‘Archive to File’ program from SRS window to move your archive data to a file in the directory with the file name . Also ensure that you move your archive output from the AR_TOP/out directory to an appropriate storage area. Otherwise, it will be deleted when your system administrator clears the output directories.

6) Once archive data has been stored the archive tables must be cleared before the next purge run. To clear the archive tables use the TRUNCATE command in SQL with the following tables:
AR_ARCHIVE_HEADER
AR_ARCHIVE_DETAIL
7) The following tables will be cleared automatically the next time you run the Archive/Purge programs.
AR_PURGE_TRX_AUX
AR_PURGE_REC_AUX
AR_ARCHIVE_PURGE_LOG
AR_ARCHIVE_PURGE_INTERIM
AR_PURGE_OE_EXCEPTIONS
8) After you purge your database, you should contact your Database Administrator (DBA), so that he can export and import the tables and indexes from which you purged data. By recreating these objects, you can reduce the memory each object occupies in your tablespace and increase the performance of your system.