Monthly Archives: June 2015

Oracle EBS: Import Invoices into Accounts Receivables using AutoInvoices

How to import or load external invoices into Oracle Receivables using Interface tables and AutoInvoice feature.

gУЉnУЉrique rencontres У xv france 2 Setup

1) Setup a Grouping rule – Groups invoices based on Mandatory and Optional grouping transaction attributes (Run seeded report – Ordering and Grouping Rules Listing to see the mandatory attributes). Grouping rules specify attributes that must be identical for lines to appear on the same transaction.

2) Setup a Invoice ordering rule – AutoInvoice uses these rules to order transaction lines when grouping the transactions it creates into invoices, debit memos, and credit memos. You can assign a line ordering rule to each grouping rule.

3) Setup a Transaction Type – Type of invoice being imported.

4) Setup a Batch Source – To identify the source of the transactions being imported

5) Setup a- DFF Context – The set of attributes will identify the lines to import uniquely based on the line transaction flexfield.

site rencontres valais autoinvoice1

rencontre allemand Interface tables

1) Insert into ra_interface_lines_all table: Import Invoices –

INSERT INTO ra_interface_lines_all
(org_id,
orig_system_batch_name,
trx_number,
trx_date,
batch_source_name,
set_of_books_id,
line_type,
description,
memo_line_name,
amount_includes_tax_flag,
quantity,
currency_code,
conversion_rate,
conversion_type,
conversion_date,
cust_trx_type_name,
cust_trx_type_id,
interface_line_attribute1,
interface_line_attribute2,
interface_line_context,
amount,
orig_system_bill_customer_ref,
orig_system_bill_address_ref,
orig_system_bill_customer_ID,
orig_system_bill_address_ID,
receipt_method_name,
tax_rate_code,
tax_code,
primary_salesrep_number,
term_name,
purchase_order,
orig_system_bill_contact_id,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES
(101,
‘ORAGURUS.BATCH.NAME’,
lv_trx_number,
ld_trx_date,
‘ORAGURUS.DFF,
ln_ledger_id,
lv_line_type,
lv_description,
lv_memo,
lv_amount_includes_tax,
ln_quantity,
lv_currency_code,
ln_conversion_rate,
lv_conversion_type,
ld_conversion_date,
lv_cust_trx_type_name,
ln_trx_cust_id,
lv_line_attribute1,
lv_line_attribute2,
‘ORAGURUS.DFF’,
ln_invoice_amount,
lv_trx_orig_bill_cust_ref ,
lv_trx_orig_bill_address_ref ,
lv_trx_orig_bill_cust,
lv_trx_orig_bill_address,
lv_trx_receipt_method,
‘VAT 20’,
32,
‘IMMEDIATE’,
‘PO101’,
1100,
sysdate,
-1,
sysdate,
-1);

2) Insert into ra_interface_salescredits table:

INSERT INTO ra_interface_salescredits_all
(org_id,
interface_line_context,
interface_line_attribute1,
interface_line_attribute2,
sales_credit_percent_split,
sales_credit_type_name,
salesrep_number,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES
(101,
‘ORAGURUSDFF’,
‘Invoice001’,
1,
100,
‘Quota Sales Credit’,
32,
sysdate,
-1,
sysdate,
-1);

official statement Autoinvoice Errors:

Test 1:
insert into ra_interface_lines_all (batch_source_name, line_type,description,currency_code, conversion_type)
values (‘oragurus.dff’, ‘line’, ‘test line for oragurus.dff’, ‘gbp’, ‘user’);
Result: Inteface lines are not picked up
Cause: org_id is not populated

Test 2:
insert into ra_interface_lines_all (batch_source_name, line_type,description,currency_code, conversion_type, org_id)
values (‘oragurus.dff’, ‘line’, ‘test line for oragurus.dff’, ‘gbp’, ‘user’, 87);
Result: None of the transaction flexfields contexts provided for this request have enabled segments
Cause: Line transaction flexfleds contexts not defined

Test 3:
insert into ra_interface_lines_all (batch_source_name, line_type,description,currency_code, conversion_type, org_id,
interface_line_context,interface_line_attribute4,interface_line_attribute5,interface_line_attribute6,interface_line_attribute7,interface_line_attribute8) values (‘oragurus.dff’, ‘line’, ‘test line for oragurus.dff’, ‘gbp’, ‘user’, 87,’oragurus.dff’, null, null, null, null, ‘xyz’);
Result:
APP-FND-00778: Invalid context field value “ORAGURUS.DFF” passed to flexfield routine FDFDFA
Action: Pass an existing context field value or define this context value using the Define Descriptive Segments form.
Cause: DFF ‘Line Transaction Flexfield’ – ORAGURUS.DFF is not setup

UPDATE RA_INTERFACE_LINES
SET
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = :b1,
LAST_UPDATE_LOGIN = :b2,
SET_OF_BOOKS_ID = NVL(SET_OF_BOOKS_ID, :b3),
ORG_ID = NVL(ORG_ID, :b4),
TRX_DATE = TRUNC(TRX_DATE),
GL_DATE = TRUNC(GL_DATE),
ORIGINAL_GL_DATE = TRUNC(GL_DATE),
CONVERSION_DATE = TRUNC(CONVERSION_DATE),
PURCHASE_ORDER_DATE = TRUNC(PURCHASE_ORDER_DATE),
RULE_START_DATE = TRUNC(RULE_START_DATE),
RULE_END_DATE = TRUNC(RULE_END_DATE),
SALES_ORDER_DATE = TRUNC(SALES_ORDER_DATE),
SHIP_DATE_ACTUAL = TRUNC(SHIP_DATE_ACTUAL),
CUSTOMER_TRX_ID = NULL,
INTERFACE_LINE_ID = NVL(INTERFACE_LINE_ID, RA_CUSTOMER_TRX_LINES_S.NEXTVAL)
WHERE NVL(INTERFACE_STATUS, ‘~’) != ‘P’
AND REQUEST_ID = :b5

Test 4:
insert into ra_interface_lines_all (batch_source_name, line_type,description,currency_code, conversion_type, org_id,
interface_line_context,interface_line_attribute4,interface_line_attribute5,interface_line_attribute6,interface_line_attribute7,interface_line_attribut)
values (‘oragurus.dff’, ‘line’, ‘test line for oragurus.dff’, ‘gbp’, ‘user’, 87,’oragurus.dff’, null, null, null, null, ‘xyz’);
Result:
AutoInvoice Master and AutoInvoice Import program completed successfully.
Cause: Check log and interface errors table
–RA_INTERFACE_ERRORS_all
Invalid transaction type name (CUST_TRX_TYPE_NAME)
You must supply an invoice number when your batch source indicates manual invoice numbering; otherwise you must leave invoice number blank
You must supply payment terms for your non-credit transaction
You must supply conversion rate when you supply conversion type of type User
You must supply the amount for this transaction
Invalid bill to customer reference (ORIG_SYSTEM_BILL_CUSTOMER_REF)
The Bill To address reference must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_REF)
All enabled segments of Transaction Flexfield must have a value

Test 5:
insert into ra_interface_lines_all (batch_source_name, line_type,description,currency_code, conversion_type, org_id,
interface_line_context,interface_line_attribute4,interface_line_attribute5,interface_line_attribute6,interface_line_attribute7,interface_line_attribut)
,amount, cust_trx_type_id, term_name, orig_system_bill_customer_id, orig_system_bill_address_id, conversion_rate)
values (‘oragurus.dff’, ‘line’, ‘test line for oragurus.dff’, ‘gbp’, ‘user’, 87,
‘oragurus.dff’, ‘att4′,’att5′,’att6′,’att7′,’batch_xyz’,1100, 1004,’07 days’,7745,7758,1);

Result: standard programs completed successfully
Errors: 1) Invalid bill to customer reference (ORIG_SYSTEM_BILL_CUSTOMER_REF) ((Null))
2) Invalid transaction type name (CUST_TRX_TYPE_NAME) ((Null))
3) The Bill To address reference must exist in Oracle Receivables, and it must be assigned to the Bill To customer
(ORIG_SYSTEM_BILL_ADDRESS_REF) ((Null))
4) You must supply an invoice number when your batch source indicates manual invoice numbering; otherwise you must
leave invoice number blank
Fix: Enter correct values for orig system references

Test 6: –REF interface columns to be passed
insert into ra_interface_lines_all (batch_source_name, line_type,description,currency_code, conversion_type, org_id,
interface_line_context,interface_line_attribute4,interface_line_attribute5,interface_line_attribute6,interface_line_attribute7,interface_line_attribute8,amount, cust_trx_type_name, –cust_trx_type_id,
term_name, orig_system_bill_customer_ref, orig_system_bill_address_ref,conversion_rate)
values (‘oragurus.dff’, ‘line’, ‘test line for oragurus.dff’, ‘gbp’, ‘user’, 87,
‘oragurus.dff’, ‘att4′,’att5′,’att6′,’att7′,’batch_xyz’,
1100, ‘chargeout invoice’, ’07 days’,7911, 7924,1);

Errors: 1) You must supply an invoice number when your batch source indicates manual invoice numbering; otherwise you must
leave invoice number blank

Cause: The transaction batch source – batch numbering is set to automatic but invoice number is not set to automatic#
Test 6:
Errors: 1) Unable to derive a gl date for your transaction. Please ensure that your transaction is in a gl period which you
have defined
Fix: The gl_Date on the interface line should fall in the Open GL Periods

(N) Receivables –> Control  –> Accounting –> Open / Close Periods

(N) General Ledger –> Setup –> Open / Close

Test 7:
Errors: 1) The GL date is not in an open or future-enterable period. (15-JUN-15)
Fix: Open AR periods
Errors: 1) Please correct the receivable account assignment (07000..0000.0000..000.00.0000)
2) Please correct the revenue account assignment (07000…….)

partnersuche höxter kostenlos References:

http://docs.oracle.com/cd/A60725_05/html/comnls/us/ar/rules01.htm

https://docs.oracle.com/cd/A60725_05/html/comnls/us/ar/autoin14.htm

http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e20375/F569968AN5F076.htm

http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e20375/graphics/ar_sucb_aigrp_02_10745064.png

Oracle EBS: How to dynamically process incoming data in the staging table with a Conc Program parameter

sites de rencontre ado.com  Scenario

Consider a scenario for interfaces where incoming data is being pumped to a staging table when a custom interface program has validated & processed the existing data in the staging table by updating the process_status column to ‘S’ – Success or ‘E’ – Error. The successful records are deleted and moved to the appropriate interface table. e.g AutoInvoice interface tables.

badoo rencontre femmes Requirement

The requirement is to provide an option to the end user to decide whether new data that has come into staging table needs to be considered for the current run.

site rencontre korea Probable Solution

One of the probable solution is to include an additional parameter ‘Include new lines?’ parameter and the underlying logic (Limitation is not to use any additional temporary tables) and to decide if the new lines can be included for processing or let go based on the parameter value. will be scheduled with a default value of ‘No’.

imp source Workaround

The simple solution could be let all the lines be processed in such occasions by choosing the parameter value to ‘Yes’. But this is a restriction as the concurrent program is scheduled with default value ‘No’.

badoo rencontre gratuite Test Case

Lets say, the process_status column is used to decide if a row has been processed or is treated as a new line.

If process_status column is null, then it considered as a new row.

If process_status column is other than null, then it is considered as being processed.

dynamic_line_process1

When the concurrent program is run for first time and ‘Include new lines?’ is ‘No’, none of the lines in the staging table are selected for processing. During such occasions, new rows needs to be selected for processing.

Intermittent Issue

In certain occasions, all the data in the staging table is in unprocessed state and hence process_status is null for all records. During these times, the concurrent program run would fail as none of the lines will be selected for processing when ‘Include new lines?’ parameter is set to ‘No’. This post is to overcome such an intermittent issue.

dynamic_line_process2

But the above new lines should be picked up for processing as there are no processed lines available for the concurrent run to process them to copy them into Autoinvoice interface table.

Let me know if you have any ideas to resolve such an issue without using a temporary data storage?

Solution

Use the below WHERE clause in the SELECT statement:

(iv_include_new_batch = ‘N’ AND process_Status IS NOT NULL)

OR

(iv_include_new_batch = ‘N’ AND process_Status IS NULL
AND NOT EXISTS
(SELECT 1
FROM xxora_ar_interface_stage xais
WHERE xais.location = iv_batch_source
AND xais.batch_name != xais1.batch_name
AND xais.process_status IS NOT NULL))