Devendrakumar Rajendran - A Blog to Oracle Financials World

Query to identify production batch variances (Actual VS WIP Planned) Below query will identify variances for ingredients/by-products/products Batch variance details will be electronically sent to distribution list members Set periodic alert - frequency- everyday and set timings Under actions -> set action type is message Link distribution list members and set message text In action sets -> set all the actions you have created Query select itemcode        ,plan_qty        ,wip_plan_qty        ,actual_qty        ,ROUND(diff, 2) diff        ,ptype        ,batchno        ,(select user_name from fnd_user where user_id = qry.created_by ) created_by_name        ,(select user_name from fnd_user where user_id = qry.last_updated_by ) updated_by_name        ,batchstatus        into &itemcode, &plan_qty, &wip_plan_qty, &actual_qty, &diff, &ptype, &batchno, &created_by, &updated_by, &batch_status from       (select (select se

Raw materials will be used in production batches to produce FG/End-Products Inorder to consume items first we have to assign it to inventory organization. It is a basic step. Assign items to inventory organization        ego_item_pub.assign_item_to_org(                    p_api_version          => 1.0                 ,  p_init_msg_list        => fnd_api.g_false                 ,  p_commit               => fnd_api.g_false                 ,  p_inventory_item_id    => 100                 ,  p_organization_code    =>  l_array(i)                 ,  x_return_status        => x_return_status                 ,  x_msg_count            => x_msg_count ); How to split use comma separate value using PLSQL collection If multiple organizations to be assigned to inventory item Say., item = xxx, to be assigned to INV1, INV2 Then below code will split up INV1, INV2 into plsql array list is varchar type, table length is numeric type, and table is of array typ

Basic business need Business organization offers price discount during festival days to attract customers to purchase goods in large volume This will promote business team to quickly clear their stocks and make cash revenue Discounts can be set up for specific dates say. Ramadan kareem offer - 10-Jun-2017 till 20-Jun-2017 - Discount of 10% on milk products Eid al adha offer - 31-Aug-2017 till 2-sep-2017 - Discount of 5% on all products Modifers Type - Discount Number - Unique code Name - Eid Al Adha promotion System is allowed to define period dates where discount offer is valid upto Discount level is at order level or line level Automatic apply - adjusted automatically at sale order header level override - set to yes or no - depends on business use-case Modifier type - discount Application method - discount percentage Value - numeric value say 5 % or 10% pricing phase - set the precedences - line level adjustments/header level/BOOK event Create sale or

Inquire with transaction type for class invoice Check - Allow freight checkbox This will enable freight charges to enter at AR transaction level Auto accounting Set freight natural account and department as constant

How to add freight charges at sale orders header level  Define modifiers Modifiers helps us to define freight and special charges that can be added additionaly at the time of sales order level Adjustments can be done at line and header level Freight charges can be added automatic  Charges can be overriden or not allowed You can define multiple charges under single modifier Against each base currency this modifier will act upon and charges defined will be added at the time of transaction we can show or control additional charges to be displayed at invoice bill Define charge name under "Discount/charges" tab Select application method as lumpsum Level of charges it can be at order level or line level Modifier no is automatic sequence for record purpose

Basic business need  Automatic reconciliation your remitted receipts and payments in oracle cash management To reconcile automatic bank statements should be pulled into oracle system Approach Before you can reconcile transactions against a bank statement, you need to enter the bank statement information into Cash Management. If your bank provides account statements in a flat file, using a defined format like BAI or SWIFT940, you can use the Bank Statement Open Interface to load this information into Cash Management. Pre-requisite Create a SQL*Loader script. To load a bank statement into the bank statement open interface: 1. Run the SQL*Loader script against the bank file. This populates the bank statement open interface tables. 2. Verify that the bank statement information was loaded into the open interface tables correctly. You can use the Bank Statement Interface window to ensure that bank statement header information is correctly mapped to the C

Basic business need Move order line item will be sent for approvals to planner Pain points from planner: 1. Receiving many notifications based on move order line items 2. If move order is having 100 of items for discard process, then manual approvals or rejections is pain for business stake holders 3. Requires automatic approval by the system and need FYI notification. Consultant solution Create a process to identify the following, a. move order line items which is pending for approval b. identify approval workflow engine which requires updating header and line status c. write a custom plsql program to perform automatic approval process Parameter design Move order no - You can perform automatic approval for specific move order no. Auto run mode - Auto approval / Initiate workflow approval process  Auto approval - performs approval operation in systematic way for pending transactions  Initiate workflow process - sends notification to planner

Basic business need Business organization might have requirement to serve  the purpose of presenting bills to global customers in their respective country currencies. Assume that business headquarters is at Salalah, Sultanate of Oman. Sales team will raise bills in functional currency say Omani riyals. Business organization has customers across gulf countries say United Arab Emirates and Qatar. Customers will seek to present bills in UAE Dirhams and Qatari Riyals. In this scenario, business has a challenge to convert product prices, line amount, and billing amount totals into cross currencies. In our case it should be presented to customers in AED/QAR to fulfill customer needs Solution Design: 1. Create custom invoice/billing report as like BPA standard report 2. Create parameter - Bill print currency format - Assign currencies list of values 3. Based on user currency selection, bills amount will be automatically converted into foreign currencies b

Basic business need Department code should be validated against move order transaction type along with location. Enforce validation if user selects wrong department Right set of departments will be defined in lookups against transaction type + location Enable Form Personalization Sequence: 50 Description: Validate department based on transaction type + location Trigger event: when-validate-record Trigger object TOMAI_MAIN_HEADER_BLK Condition:    ( SELECT count(lookup_code)         FROM apps.fnd_lookup_values        WHERE     lookup_type = 'DCF_INV_MO_DEPT_VALIDATION'              and description = (:TOMAI_MAIN_HEADER_BLK.TRANSACTION_TYPE_NAME||'+'||:TOMAI_MAIN_HEADER_BLK.TO_LOCATION)              AND enabled_flag = 'Y'              AND start_date_active <= SYSDATE              AND meaning like '%'||:TOMAI_MAIN_HEADER_BLK.ATTRIBUTE1||'%'              AND NVL (end_date_active, SYSDATE) >= SYSDATE)         = 0 Action

DECLARE   v_user_name  VARCHAR2(30):=UPPER('&Enter_User_Name');   v_password   VARCHAR2(30):='&Enter_Password';   v_session_id INTEGER     := USERENV('sessionid'); BEGIN   fnd_user_pkg.createuser (     x_user_name => v_user_name,     x_owner => NULL,     x_unencrypted_password => v_password,     x_session_number => v_session_id,     x_start_date => SYSDATE,     x_end_date => NULL   );   COMMIT;   DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully'); EXCEPTION   WHEN OTHERS THEN     DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));     ROLLBACK; END; BEGIN    fnd_user_pkg.addresp ('DEVENDRA.R',                          'SYSADMIN',                          'SYSTEM_ADMINISTRATOR',                          'STANDARD',                          'Add Responsibility to USER using pl/sql',

Honor for my hard work and quality of work Excellence Club Award

Finished Good is an end-product which is sold by business organization to customers Attributes of finished goods FG is an inventory item It is Stockable It is Transactable It is Reservable Order management attributes Customer Ordered = Yes Customer Orders Enabled = Yes Shippable = Yes OE Transactable = Yes Pick Components = No Assemble to Order = No Purchasing = No Costing Enabled = Yes Inventory Asset Value = Yes Include in Rollup = Yes Bills of material Item type = Standard BOM Allowed = Yes Invoicable item = Yes Invoice enabled = Yes

How to create inventory item using open interface 1. Prepare insert script and pump details into mtl_system_items_interface table 2. Run standard concurrent program (Import items) from inventory module Code snippets INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE   (     organization_id,     set_process_id,     process_flag,     transaction_type,     last_updated_by,     last_update_date,     last_update_login,     creation_date,     created_by,     segment1,     description,     template_id,     primary_uom_code,     tracking_quantity_ind,     secondary_uom_code,     secondary_default_ind,     inventory_item_status_code,     inventory_item_flag,     stock_enabled_flag,     mtl_transactions_enabled_flag,     reservable_type,     shelf_life_code,     shelf_life_days,     lot_control_code,     auto_lot_alpha_prefix,     start_auto_lot_number,     lot_split_enabled,     lot_merge_enabled,     lot_divisible_flag,     bom_enabled_flag,     bom_item_type,  

Autocopy journal is a oracle GL feature that enables users to rapidly create journals on the fly. Suppose you have journal data recurring for many months, in this case if journal amount is exactly same and hits the same account then this autocopy is excellent feature and benefits accountants to quickly create and post GL accounts Steps: Navigate to journals workbench Select the journals using current record indicator Click on autocopy Concurrent request will be submitted automatically in background Go and inquire with the batch name which you entered using auto copy It should be available as newly created journal with source as autocopy and hits same accounts << Images removed>>

GL Accounting Flexfield qualifiers Accounting flexfield is defined as a structure in oracle financials. Structure is composed of segments and each segment has its own qualifiers referring cost center, balancing segment, and viz. which is so called as flexfield qualifiers Cost center segment - This attribute is used to identify the cost center segment Natural account segment - This attribute is used to identify the Natural account segment Balancing segment - This attribute is used to identify the Balancing segment Intercompany segment - This attribute is used to identify the Intercompany segment Management segment - This attribute is used to identify the Management segment Secondary tracking segment - This attribute is used to identify the Secondary tracking segment Assume your organization structure is following and qualifiers will be, Company - Balancing segment - where debits must equals credits  while recording journals Division - Management segment -