/* A common war between Credit and Warehouse Dept is Delivery after Hold removal
Following query will put a focus on Order and hold till releasing a hold */
SELECT H.ORDER_NUMBER,
HO.NAME HOLD_NAME,
HS.HOLD_UNTIL_DATE,
HS.HOLD_COMMENT,
OH.HEADER_ID,
OH.LINE_ID,
OH.ORDER_HOLD_ID,
L.ITEM_IDENTIFIER_TYPE,
L.INVENTORY_ITEM_ID,
L.ORDERED_ITEM,
OHR.RELEASE_COMMENT
FROM OE_ORDER_HOLDS_ALL OH,
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS_ALL H,
OE_HOLD_DEFINITIONS HO,
OE_HOLD_SOURCES_ALL HS,
OE_HOLD_RELEASES OHR
WHERE OH.HEADER_ID = H.HEADER_ID
AND (H.CANCELLED_FLAG IS NULL OR H.CANCELLED_FLAG = 'N')
--AND OH.RELEASED_FLAG != 'Y'
AND H.OPEN_FLAG = 'Y'
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND HS.HOLD_ID = HO.HOLD_ID
AND H.HEADER_ID = L.HEADER_ID(+)
AND L.OPEN_FLAG = 'Y'
AND L.LINE_ID = NVL (OH.LINE_ID, L.LINE_ID)
AND L.SERVICE_REFERENCE_LINE_ID IS NULL
AND OH.HOLD_RELEASE_ID IS NULL
--AND NVL(H.ORG_ID,0) = 204
AND NVL (L.ORG_ID, 0) = NVL (H.ORG_ID, 0)
AND OH.HOLD_RELEASE_ID = OHR.HOLD_RELEASE_ID(+)
ORDER BY HO.NAME, H.ORDER_NUMBER
/* When - Why */
SELECT H.ORDER_NUMBER,
RELEASE_REASON_CODE,
RELEASE_COMMENT,
TO_CHAR (OH.CREATION_DATE, 'DD-MON-YYYY hh24:mm:ss') HOLD_APPLIED_DATE,
TO_CHAR (OHR.CREATION_DATE, 'DD-MON-YYYY hh24:mm:ss') HOLD_RELEASE_DATE
FROM OE_ORDER_HOLDS_ALL OH, OE_HOLD_RELEASES OHR, OE_ORDER_HEADERS_ALL H
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID = OHR.HOLD_RELEASE_ID
AND OHR.CREATION_DATE LIKE SYSDATE
Thursday, July 28, 2011
Tuesday, July 26, 2011
Inventory Opening Balance Interface (On Hand Qty Interface Part - II An Example )
/* Create a preinterface Table */
CREATE TABLE UETDT.UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)
/* Insert data into Pre-Interface Table
Note : Item is already created , assigned and Item sub inventory also added to item */
INSERT INTO UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (251,
707,
'ELECTRONICS',
'RAR GEMS', -- item already created
'EA',
21, -- Qty
0,
0,
'OUTLET', -- Locator
NULL,
SYSDATE,
SYSDATE - 10,
SYSDATE - 11,
0,
'Y');
COMMIT;
SELECT * FROM UET_INV_OPENING_BALANCES_INT
/* Create Custom Sequences to be used for interface header */
CREATE SEQUENCE APPS.XUET_TRAN_SOURCE_LINE_IDS_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER
CREATE SEQUENCE APPS.XUET_TRAN_INTERFACE_ID_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER
/* Procedure Starts */
----
SET SERVEROUTPUT ON SIZE 1000000;
----
/* Variables Declaration */
DECLARE
l_transaction_interface_id NUMBER;
l_item_count NUMBER := 0;
l_exp_count NUMBER := 0;
CURSOR item_c
IS
SELECT x.item_code old_item_code, x.qty quantity, x.unit_cost COST, x.last_issue_date,
x.last_receipt_date, x.last_supplier_no, x.item_code, x.expiry_date last_exp_date,
x.VALUE, msi.organization_id, msi.restrict_locators_code,
msif.secondary_inventory_name subinventory_code,
get_locator_id(msi.organization_id, msi.inventory_item_id, x.stock_locator) locator_id,
30598 material_account, msi.primary_uom_code, msi.inventory_item_id,
p.default_cost_group_id cost_group_id
FROM uetdt.uet_inv_opening_balances_int x,
mtl_system_items msi,
mtl_secondary_inventories_fk_v msif,
mtl_parameters p
WHERE LTRIM (RTRIM (item_code)) = msi.segment1
AND msi.organization_id = p.organization_id (+)
AND msi.organization_id = 343 --342,343
AND msi.organization_id = msif.organization_id
AND ltrim(rtrim(x.division_CODE))='251'
AND x.inventory_org_code='707'
AND msif.locator_type = 5
AND x.qty > 0;
/* Material account is hard coded which is COGS attached to item. */
/* Main Program */
BEGIN
DBMS_OUTPUT.put_line ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
FOR r IN item_c
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO l_transaction_interface_id
FROM DUAL;
BEGIN
/* Inserting into Main transaction table */
INSERT INTO mtl_transactions_interface
(source_code, source_line_id,
source_header_id, cost_group_id, process_flag, transaction_mode,
transaction_interface_id, inventory_item_id, organization_id,
subinventory_code, transaction_quantity, transaction_uom,
transaction_date, transaction_source_id, transaction_type_id,
transaction_cost, transaction_reference, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, attribute12, attribute13,
attribute14, attribute15, locator_id
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, r.cost_group_id, 1, 3,
l_transaction_interface_id, r.inventory_item_id, r.organization_id,
r.subinventory_code, TO_NUMBER (r.quantity), r.primary_uom_code,
'25-JUL-2011', r.material_account, 40,
TO_NUMBER (r.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, r.last_exp_date, r.last_issue_date,
r.last_receipt_date, r.last_supplier_no, r.locator_id
);
-- COMMIT;
/* Updates the Process flag to 'Y' */
UPDATE uetdt.uet_inv_opening_balances_int
SET x_ob_imported = 'Y'
WHERE item_code = r.item_code AND qty = r.quantity AND unit_cost = r.COST
AND VALUE = r.VALUE;
-- COMMIT;
l_item_count := l_item_count + 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Exception..');
END;
END LOOP;
DBMS_OUTPUT.put_line ('Number of normal items processed ' || l_item_count);
DBMS_OUTPUT.put_line ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;
SELECT * FROM MTL_TRANSACTIONS_INTERFACE
/* Go to Inventory > Setups > Transaction > Interface Manager
select Material transaction manager > Tools Launch Manager
In case of Error
Inventory > Transactions > Transaction Open Interface Query for ERROR
Select Submit > correct error field
resubmit Material transaction manager */
Possible Errors - Wrong Distribution Account , Invalid Sub inventory , Not a valid Inventory open period found .
CREATE TABLE UETDT.UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)
/* Insert data into Pre-Interface Table
Note : Item is already created , assigned and Item sub inventory also added to item */
INSERT INTO UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (251,
707,
'ELECTRONICS',
'RAR GEMS', -- item already created
'EA',
21, -- Qty
0,
0,
'OUTLET', -- Locator
NULL,
SYSDATE,
SYSDATE - 10,
SYSDATE - 11,
0,
'Y');
COMMIT;
SELECT * FROM UET_INV_OPENING_BALANCES_INT
/* Create Custom Sequences to be used for interface header */
CREATE SEQUENCE APPS.XUET_TRAN_SOURCE_LINE_IDS_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER
CREATE SEQUENCE APPS.XUET_TRAN_INTERFACE_ID_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER
/* Procedure Starts */
----
SET SERVEROUTPUT ON SIZE 1000000;
----
/* Variables Declaration */
DECLARE
l_transaction_interface_id NUMBER;
l_item_count NUMBER := 0;
l_exp_count NUMBER := 0;
CURSOR item_c
IS
SELECT x.item_code old_item_code, x.qty quantity, x.unit_cost COST, x.last_issue_date,
x.last_receipt_date, x.last_supplier_no, x.item_code, x.expiry_date last_exp_date,
x.VALUE, msi.organization_id, msi.restrict_locators_code,
msif.secondary_inventory_name subinventory_code,
get_locator_id(msi.organization_id, msi.inventory_item_id, x.stock_locator) locator_id,
30598 material_account, msi.primary_uom_code, msi.inventory_item_id,
p.default_cost_group_id cost_group_id
FROM uetdt.uet_inv_opening_balances_int x,
mtl_system_items msi,
mtl_secondary_inventories_fk_v msif,
mtl_parameters p
WHERE LTRIM (RTRIM (item_code)) = msi.segment1
AND msi.organization_id = p.organization_id (+)
AND msi.organization_id = 343 --342,343
AND msi.organization_id = msif.organization_id
AND ltrim(rtrim(x.division_CODE))='251'
AND x.inventory_org_code='707'
AND msif.locator_type = 5
AND x.qty > 0;
/* Material account is hard coded which is COGS attached to item. */
/* Main Program */
BEGIN
DBMS_OUTPUT.put_line ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
FOR r IN item_c
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO l_transaction_interface_id
FROM DUAL;
BEGIN
/* Inserting into Main transaction table */
INSERT INTO mtl_transactions_interface
(source_code, source_line_id,
source_header_id, cost_group_id, process_flag, transaction_mode,
transaction_interface_id, inventory_item_id, organization_id,
subinventory_code, transaction_quantity, transaction_uom,
transaction_date, transaction_source_id, transaction_type_id,
transaction_cost, transaction_reference, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, attribute12, attribute13,
attribute14, attribute15, locator_id
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, r.cost_group_id, 1, 3,
l_transaction_interface_id, r.inventory_item_id, r.organization_id,
r.subinventory_code, TO_NUMBER (r.quantity), r.primary_uom_code,
'25-JUL-2011', r.material_account, 40,
TO_NUMBER (r.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, r.last_exp_date, r.last_issue_date,
r.last_receipt_date, r.last_supplier_no, r.locator_id
);
-- COMMIT;
/* Updates the Process flag to 'Y' */
UPDATE uetdt.uet_inv_opening_balances_int
SET x_ob_imported = 'Y'
WHERE item_code = r.item_code AND qty = r.quantity AND unit_cost = r.COST
AND VALUE = r.VALUE;
-- COMMIT;
l_item_count := l_item_count + 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Exception..');
END;
END LOOP;
DBMS_OUTPUT.put_line ('Number of normal items processed ' || l_item_count);
DBMS_OUTPUT.put_line ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;
SELECT * FROM MTL_TRANSACTIONS_INTERFACE
/* Go to Inventory > Setups > Transaction > Interface Manager
select Material transaction manager > Tools Launch Manager
In case of Error
Inventory > Transactions > Transaction Open Interface Query for ERROR
Select Submit > correct error field
resubmit Material transaction manager */
Possible Errors - Wrong Distribution Account , Invalid Sub inventory , Not a valid Inventory open period found .
Inventory Opening Balance Interface (On Hand Qty Interface)
This interface lets you import the on hand inventory into Oracle.
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:
Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base Tables:
MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS
Validations:
Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Some important columns that need to be populated in the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Account --- GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias --- MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule ---WIP_ENTITIES.WIP_ENTITY_ID
Sales Order --- MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.
MTL_TRANSACTION_LOTS_INTERFACE:
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
MTL_SERIAL_NUMBERS_INTERFACE:
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:
Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base Tables:
MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS
Validations:
Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Some important columns that need to be populated in the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Account --- GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias --- MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule ---WIP_ENTITIES.WIP_ENTITY_ID
Sales Order --- MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.
MTL_TRANSACTION_LOTS_INTERFACE:
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
MTL_SERIAL_NUMBERS_INTERFACE:
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER
Thursday, July 21, 2011
Account Payable - Creating AP Invoice , CM - Credit Memo , DM
Account Payable - AP Invoice , CM - Credit Memo , DM - Debit Memo .
A very common requirement is creating AP INV, CM, DM using interface. lets have a look.
Scenario - Data for Invoices , CM ,DM from legacy system to Oracle Application.In common case companies multiple ERP's but want to maintain GL in Oracle.
Step 1 - Lets create a temp table in which we will dump data from legacy system.table contains all required columns for interface.
Considering Vendor and site is already created in Oracle.
CREATE TABLE APPS.KER_AP_INVOICES_NOV10_3
(
INVOICE_ID NUMBER(15) NOT NULL,
INVOICE_NUM VARCHAR2(50 BYTE) NOT NULL,
INVOICE_DATE DATE,
TERMS_DATE DATE,
INVOICE_TYPE VARCHAR2(25 BYTE),
VENDOR_ID NUMBER(15) NOT NULL,
VENDOR_SITE_ID NUMBER(15),
INVOICE_CURRENCY_CODE VARCHAR2(15 BYTE) NOT NULL,
INVOICE_AMOUNT NUMBER,
BALANCE_AMOUNT NUMBER,
EXCHANGE_RATE NUMBER,
EXCHANGE_RATE_TYPE VARCHAR2(30 BYTE),
EXCHANGE_DATE DATE,
BALANCE_AMOUNT_BD NUMBER,
DIVISION CHAR(3 BYTE),
REFERENCE VARCHAR2(240 BYTE),
VOUCHER_NUMBER NUMBER,
GL_DATE DATE NOT NULL,
VENDOR_NUMBER VARCHAR2(30 BYTE),
VENDOR_NAME VARCHAR2(240 BYTE)
) ;
Sample Data -
INVOICE_ID = 252665
INVOICE_NUM = A0012107.47
INVOICE_DATE = 10/12/2010
TERMS_DATE = 11/11/2010
INVOICE_TYPE = STANDARD
VENDOR_ID = 777
VENDOR_SITE_ID = 974
INVOICE_CURRENCY_CODE = EUR
INVOICE_AMOUNT = 1195.58
BALANCE_AMOUNT = 1195.58
EXCHANGE_RATE = 0.51
EXCHANGE_RATE_TYPE = User
EXCHANGE_DATE = 10/24/2010
BALANCE_AMOUNT_BD = 609.7458
DIVISION = 104 (DFF)
REFERENCE = DR.201002341
VOUCHER_NUMBER = 92009193
GL_DATE = 10/24/2010
VENDOR_NUMBER = 100777
VENDOR_NAME = IVECO S.P.A
A very common requirement is creating AP INV, CM, DM using interface. lets have a look.
Scenario - Data for Invoices , CM ,DM from legacy system to Oracle Application.In common case companies multiple ERP's but want to maintain GL in Oracle.
Step 1 - Lets create a temp table in which we will dump data from legacy system.table contains all required columns for interface.
Considering Vendor and site is already created in Oracle.
CREATE TABLE APPS.KER_AP_INVOICES_NOV10_3
(
INVOICE_ID NUMBER(15) NOT NULL,
INVOICE_NUM VARCHAR2(50 BYTE) NOT NULL,
INVOICE_DATE DATE,
TERMS_DATE DATE,
INVOICE_TYPE VARCHAR2(25 BYTE),
VENDOR_ID NUMBER(15) NOT NULL,
VENDOR_SITE_ID NUMBER(15),
INVOICE_CURRENCY_CODE VARCHAR2(15 BYTE) NOT NULL,
INVOICE_AMOUNT NUMBER,
BALANCE_AMOUNT NUMBER,
EXCHANGE_RATE NUMBER,
EXCHANGE_RATE_TYPE VARCHAR2(30 BYTE),
EXCHANGE_DATE DATE,
BALANCE_AMOUNT_BD NUMBER,
DIVISION CHAR(3 BYTE),
REFERENCE VARCHAR2(240 BYTE),
VOUCHER_NUMBER NUMBER,
GL_DATE DATE NOT NULL,
VENDOR_NUMBER VARCHAR2(30 BYTE),
VENDOR_NAME VARCHAR2(240 BYTE)
) ;
Sample Data -
INVOICE_ID = 252665
INVOICE_NUM = A0012107.47
INVOICE_DATE = 10/12/2010
TERMS_DATE = 11/11/2010
INVOICE_TYPE = STANDARD
VENDOR_ID = 777
VENDOR_SITE_ID = 974
INVOICE_CURRENCY_CODE = EUR
INVOICE_AMOUNT = 1195.58
BALANCE_AMOUNT = 1195.58
EXCHANGE_RATE = 0.51
EXCHANGE_RATE_TYPE = User
EXCHANGE_DATE = 10/24/2010
BALANCE_AMOUNT_BD = 609.7458
DIVISION = 104 (DFF)
REFERENCE = DR.201002341
VOUCHER_NUMBER = 92009193
GL_DATE = 10/24/2010
VENDOR_NUMBER = 100777
VENDOR_NAME = IVECO S.P.A
Tuesday, July 19, 2011
Lets Talk About Oracle Receivables R12 - AR FAQ
1. What is TCA? Tables?
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle Corporation
Person for example, Jane Doe
Group for example, World Wide Web Consortium
Relationship for example, Jane Doe at Oracle Corporation.
HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.
HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.
/* A Simple Customer Query : cust_acct.customer_number is 11i party number */
select distinct *
from hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
where cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
--AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id(+)
AND cust.party_id = cust_acct.party_id
and PARTY_NAME like '2%'
2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox :
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)
3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.
RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).
RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
4. What are the tables In which Receipt information is stored?
A)
AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL 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.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.
AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.
5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL
6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
7. What Customization that you have done for Autolockbox?
- In Progress
8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
• Import (Formats data from bank file and populates the Interface Table),
• Validation(Validates the data and then Populates data into Interim Tables),
• Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
11. What are the Mandatory Interface Tables in Auto Invoice?
RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.
13. What is AutoAccounting?
A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
14. What are Autocash rules?
A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
16. What are Line Ordering Rules? (Used by Autoinvoice)
A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
19. What are the available Key Flex Fields in Oracle Receivables?
A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle Corporation
Person for example, Jane Doe
Group for example, World Wide Web Consortium
Relationship for example, Jane Doe at Oracle Corporation.
HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.
HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.
/* A Simple Customer Query : cust_acct.customer_number is 11i party number */
select distinct *
from hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
where cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
--AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id(+)
AND cust.party_id = cust_acct.party_id
and PARTY_NAME like '2%'
2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox :
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)
3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.
RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).
RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
4. What are the tables In which Receipt information is stored?
A)
AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL 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.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.
AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.
5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL
6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
7. What Customization that you have done for Autolockbox?
- In Progress
8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
• Import (Formats data from bank file and populates the Interface Table),
• Validation(Validates the data and then Populates data into Interim Tables),
• Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
11. What are the Mandatory Interface Tables in Auto Invoice?
RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.
13. What is AutoAccounting?
A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
14. What are Autocash rules?
A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
16. What are Line Ordering Rules? (Used by Autoinvoice)
A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
19. What are the available Key Flex Fields in Oracle Receivables?
A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.
Saturday, July 16, 2011
Basics Of Finance -
Most Of Techno Functional's are from Engineering background.so lets start from basics of accounting .
In Oracle General basic accounts are classified into 5 types –
• Asset
• Expense
• Liability
• Ownership/Stockholders Equity
• Revenue
1) Assets - In financial accounting, assets are economic resources. Anything tangible or intangible that is capable of being owned or controlled to produce value and that is held to have positive economic value is considered an asset. Simply stated, assets represent ownership of value that can be converted into cash (although cash itself is also considered an asset)
$945 = $500 + $445
For example: A student buys a computer for $945. This student borrowed $500 from his best friend and spent another $445 earned from his part-time job. Now his assets are worth $945, liabilities are $500, and equity $445 (Example is Used throughout document)
Current Assets - Current assets are cash and other assets expected to be converted to cash, sold, or consumed either in a year or in the operating cycle (whichever is shorter), without disturbing the normal operations of a business
1. Cash and cash equivalents — it is the most liquid asset, which includes currency, deposit accounts, and negotiable instruments (e.g., money orders, cheque, bank drafts).
2. Short-term investments — include securities bought and held for sale in the near future to generate income on short-term price differences (trading securities).
3. Receivables — usually reported as net of allowance for uncollectable accounts.
4. Inventory — trading these assets is a normal business of a company. The inventory value reported on the balance sheet is usually the historical cost or fair market value, whichever is lower. This is known as the "lower of cost or market" rule.
5. Prepaid expenses — these are expenses paid in cash and recorded as assets before they are used or consumed (a common example is insurance). See also adjusting entries.
2) Liability (financial accounting) - In financial accounting, a liability is defined as an obligation of an entity arising from past transactions or events, the settlement of which may result in the transfer or use of assets, provision of services or other yielding of economic benefits in the future. If you stick with these two thoughts (“owe” and “pay someone in the future”), you should be able to easily identify your liabilities.
A liability is defined by the following characteristics:
• Any type of borrowing from persons or banks for improving a business or personal income that is payable during short or long time;
• A duty or responsibility to others that entails settlement by future transfer or use of assets, provision of services, or other transaction yielding an economic benefit, at a specified or determinable date, on occurrence of a specified event, or on demand;
• A duty or responsibility that obligates the entity to another, leaving it little or no discretion to avoid settlement; and,
• A transaction or event obligating the entity that has already occurred
Common Liability Accounts ( Payables ):
Current Liabilities:
• Notes Payable - Promissory notes to creditors.
• Accounts Payable - What you owe others on account.
• Unearned Revenue - You've been paid, but haven't delivered.
• Salaries Payable - Salaries you owe employees.
• Interest Payable - Interest you owe.
• Taxes Payable - Taxes you owe.
$500 = $945 - $445
Owner's (Stockholders') Equity –
Expense - In common usage, an expense or expenditure is an outflow of money to another person or group to pay for an item or service, or for a category of costs. For a tenant, rent is an expense. For students or parents, tuition is an expense. Buying food, clothing, furniture or an automobile is often referred to as an expense. An expense is a cost that is "paid" or "remitted", usually in exchange for something of value.
In accounting, expense has a very specific meaning. It is an outflow of cash or other valuable assets from a person or company to another person or company. This outflow of cash is generally one side of a trade for products or services that have equal or better current or future value to the buyer than to the seller. Technically, an expense is an event in which an asset is used up or a liability is incurred.
Ex.
41101 COGS - Domestic Sales ( Cost Of goods Sold In Inventory )
41111 COGS - Clearance Sales
51105 Petrol Allowance
51107 Fixed OT
51121 Absence Salary Deduction
51151 Overtime - I
A Query -
select FLEX_VALUE_MEANING , DESCRIPTION from FND_FLEX_VALUES_VL a
where FLEX_VALUE_MEANING in (select ACCOUNT from YKA_AFF_V b where GL_ACCOUNT_TYPE = 'E' and a.FLEX_VALUE_MEANING = b.ACCOUNT )
A Small Example to understand Accounting -
In Oracle General basic accounts are classified into 5 types –
• Asset
• Expense
• Liability
• Ownership/Stockholders Equity
• Revenue
1) Assets - In financial accounting, assets are economic resources. Anything tangible or intangible that is capable of being owned or controlled to produce value and that is held to have positive economic value is considered an asset. Simply stated, assets represent ownership of value that can be converted into cash (although cash itself is also considered an asset)
Assets = Liabilities + Stockholder's Equity (Owner's Equity)
$945 = $500 + $445
For example: A student buys a computer for $945. This student borrowed $500 from his best friend and spent another $445 earned from his part-time job. Now his assets are worth $945, liabilities are $500, and equity $445 (Example is Used throughout document)
Current Assets - Current assets are cash and other assets expected to be converted to cash, sold, or consumed either in a year or in the operating cycle (whichever is shorter), without disturbing the normal operations of a business
1. Cash and cash equivalents — it is the most liquid asset, which includes currency, deposit accounts, and negotiable instruments (e.g., money orders, cheque, bank drafts).
2. Short-term investments — include securities bought and held for sale in the near future to generate income on short-term price differences (trading securities).
3. Receivables — usually reported as net of allowance for uncollectable accounts.
4. Inventory — trading these assets is a normal business of a company. The inventory value reported on the balance sheet is usually the historical cost or fair market value, whichever is lower. This is known as the "lower of cost or market" rule.
5. Prepaid expenses — these are expenses paid in cash and recorded as assets before they are used or consumed (a common example is insurance). See also adjusting entries.
2) Liability (financial accounting) - In financial accounting, a liability is defined as an obligation of an entity arising from past transactions or events, the settlement of which may result in the transfer or use of assets, provision of services or other yielding of economic benefits in the future. If you stick with these two thoughts (“owe” and “pay someone in the future”), you should be able to easily identify your liabilities.
A liability is defined by the following characteristics:
• Any type of borrowing from persons or banks for improving a business or personal income that is payable during short or long time;
• A duty or responsibility to others that entails settlement by future transfer or use of assets, provision of services, or other transaction yielding an economic benefit, at a specified or determinable date, on occurrence of a specified event, or on demand;
• A duty or responsibility that obligates the entity to another, leaving it little or no discretion to avoid settlement; and,
• A transaction or event obligating the entity that has already occurred
Common Liability Accounts ( Payables ):
Current Liabilities:
• Notes Payable - Promissory notes to creditors.
• Accounts Payable - What you owe others on account.
• Unearned Revenue - You've been paid, but haven't delivered.
• Salaries Payable - Salaries you owe employees.
• Interest Payable - Interest you owe.
• Taxes Payable - Taxes you owe.
Liabilities = Assets - Stockholder's Equity (Owner's Equity)
$500 = $945 - $445
Owner's (Stockholders') Equity –
Stockholder's Equity (Owner's Equity) = Assets - Liabilities
Expense - In common usage, an expense or expenditure is an outflow of money to another person or group to pay for an item or service, or for a category of costs. For a tenant, rent is an expense. For students or parents, tuition is an expense. Buying food, clothing, furniture or an automobile is often referred to as an expense. An expense is a cost that is "paid" or "remitted", usually in exchange for something of value.
In accounting, expense has a very specific meaning. It is an outflow of cash or other valuable assets from a person or company to another person or company. This outflow of cash is generally one side of a trade for products or services that have equal or better current or future value to the buyer than to the seller. Technically, an expense is an event in which an asset is used up or a liability is incurred.
Ex.
41101 COGS - Domestic Sales ( Cost Of goods Sold In Inventory )
41111 COGS - Clearance Sales
51105 Petrol Allowance
51107 Fixed OT
51121 Absence Salary Deduction
51151 Overtime - I
A Query -
select FLEX_VALUE_MEANING , DESCRIPTION from FND_FLEX_VALUES_VL a
where FLEX_VALUE_MEANING in (select ACCOUNT from YKA_AFF_V b where GL_ACCOUNT_TYPE = 'E' and a.FLEX_VALUE_MEANING = b.ACCOUNT )
Debits and Credits
Generally these types of accounts are increased with a debit:
Dividends(Draws)
Expenses
Assets
Losses
You might think of D – E – A – L when recalling the accounts that are increased with a debit.
Generally these types of accounts are increased with a credit:
Gains
Income
Revenues
Liabilities
Stockholders' (Owner's) Equity
You might think of G – I – R – L – S when recalling the accounts that are increased with a credit.
A Small Example to understand Accounting -
Subscribe to:
Posts (Atom)