Monday, 9 May 2011

Script to Cancel AP Invoices

/* Formatted on 2010/08/09 20:15 (Formatter Plus v4.8.0) */
CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)
AS
l_resp_id NUMBER;
l_appl_id NUMBER;
l_user_id NUMBER := apps.fnd_global.user_id;
l_org_id NUMBER := apps.fnd_global.org_id;
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
err_msg VARCHAR2 (2000);

CURSOR invoice_cur
IS
SELECT aia.invoice_id, aia.last_updated_by, aia.last_update_login,
aia.gl_date, aia.invoice_num
FROM xx_ap_invoices_conv_stg a,
ap_invoices_all aia,
ap_invoice_lines_all aila
WHERE a.ls_inv_num = aia.invoice_num
AND a.ls_org_id = aia.org_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.payment_status_flag = 'N'
AND NVL (aila.cancelled_flag, 'N') <> 'Y';
--- AND aia.invoice_num = '65431';
BEGIN
BEGIN
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_resp_id, l_appl_id
FROM apps.fnd_responsibility frx,
apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND UPPER (fr.responsibility_name) LIKE
UPPER (DECODE (ip_operating_unit,
'OU USA MA', 'Payables Manager',
'OU USA WI', 'OU USA WI_Payables Manager',
'OU Austria', 'OU AUSTRIA_Payables Manager',
'OU China', 'OU CHINA_Payables Manager'
)
);

DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);
DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);
EXCEPTION
WHEN OTHERS
THEN
err_msg :=
'Error Occured while Deriving responsibility id' || SQLERRM;
apps.fnd_file.put_line
(apps.fnd_file.output,
'Error Occured while Deriving responsibility id'
);
END;

mo_global.set_policy_context ('S', l_org_id);
apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR l_inv_rec IN invoice_cur ----(l_org_id)
LOOP
DBMS_OUTPUT.put_line
( 'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '
|| l_inv_rec.invoice_num
);
DBMS_OUTPUT.put_line
('**************************************************************');
l_boolean :=
ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
p_token => l_token,
p_calling_sequence => NULL
);
DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);
DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);
DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);
DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);
DBMS_OUTPUT.put_line ( 'l_temp_cancelled_amount => '
|| l_temp_cancelled_amount
);
DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);
DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);
DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);
DBMS_OUTPUT.put_line ( 'P_original_prepayment_amount => '
|| l_original_prepayment_amount
);
DBMS_OUTPUT.put_line ( 'l_pay_curr_invoice_amount => '
|| l_pay_curr_invoice_amount
);

IF l_boolean
THEN
DBMS_OUTPUT.put_line ( 'Successfully Cancelled the Invoice => '
|| l_inv_rec.invoice_num
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ( 'Failed to Cancel the Invoice => '
|| l_inv_rec.invoice_num
);
ROLLBACK;
END IF;
END LOOP;
END cancel_invoices;

Oracle Apps HRMS Interview Questions

1. Important Tables in HRMS

Per_all_people_F,
per_person_types,
per_person_type_usages,
per_addresses,
per_contact_Relationships,
per_periods_of_service
Per_all_assignments_f,
per_pay_groups,
per_jobs,
per_job_Definitions,
per_grades,
per_grade_definitions,
hr_all_positions,
hr_all_position_definitions,
hr_all_locations,
pay_all_payrolls_F
pay_element_entry_values_F,
pay_element_entries_F,
pay_elements_links_F,
pay_element_types_F

2. Key Flexfields (KFFs) in HRMS

Job KFF,
Grade KFF,
People Group KFF,
Position KFF,
Cost Allocation KFF,
Comptence KFF

3.What are Date Track Tables?

every update in the Table, we will save the change in the form of a Record to provide the facility to find the information at any point of time.

These tables are post fixed by _F

4. What are secured Views?

The Views which do not have the _all to be said as secured views.

Per_all_people_F
per_people_f
per_all_assignemtns_f
per_assignments_f
pay_all_payrolls_F
per_payrolls_f

5. The differece between both secured views and non secured views is :

1.Secured views display information only for the current period

2.Unsecured views is used to get the information from the entire rows

6. APIs in HRMS

API are used in HR to insert the data into the Base tables. As its very secured system, the user does nothave the facility to copy the data directly into the Base tables.When we write the inbound interfaces / use WebAdI, the systems will use the APIs to store the data into system.The API are published by oracle with number of parameters.The different types of parameters are IN / INOUT / OUT.Of these parameters few are mandatory, with out which the process wont complete.Generally when we use API we give data for : Object Version Number, Effective Date, P_Validate

HR_EMOYEE_api ex: hr_employee_api.create_employee
hr_PERSON_api
hr_organization_api Ex: hr_organization_api.create_organization
hr_applicant_apI
hr_assignment_api

7. What are the reports which you have done in HR?

HR PAYROLL COSTING :

This report is used to display the information about the employees, the assignements which were given to the employees along with the payroll details including the Hours Paid, Salary, NI, Pension, Car Allowances and Other Allowances.

[INV] Lot Expiry Date Maintenance

YES, WE CAN CHANGE THE ITEM LOT EXPIRY DATE WITHOUT HAVING TO TRANSACT OUT AND TRANSACT IN THE SAME ITEM.GO ON-HAND, AVAILABILITY->LOTS IN INVENTORY RESPONSIBILITY.

Learn using Sub Ledger Accounting (SLA) in R12 Oracle Payables

Sub Ledger Accounting (SLA) is a Rule-Based accounting engine that defines how journal entries are generated in sub-ledger transactions in Oracle sub-ledger applications. However, SLA also supports external applications generating accounting information which ultimately needs to be transferred to Oracle General Ledger. Before we get into SLA we need to know few of the basic concepts like event types, event class, etc.

Event Class - classifies transaction types for accounting rule purposes. E.g. in Payables, following are possible event classes: Invoice, Debit Memo, Prepayments, Refunds and Payments.

Event Type - for each transaction type, defines possible actions with accounting significance. E.g. in Payables, following are possible event classes: AP Invoice Events – Validation, Adjustment and Cancellation. Similarly we will have event types for other event classes.

In most of the cases we would not need to customize SLA and accounting features will work same as 11i. Some of the typical business scenarios where we would need to customize SLA in Payables are as follows:

To have a different Liability account based on Operating Unit for which the invoice is entered.
To have different natural account (expense) based on different Invoice Type and Invoice Line type.
To have different natural account (expense) and different liability account based on different criteria like supplier type, entering currency, pay group, etc.
The cost center segment of Invoice distribution Liability account shall be picked from the Invoice distribution Account while the other segment values from the Liability account defined at supplier site.

To cater to some of the above requirements we can use other alternatives like using distribution sets also. But setting up a custom SLA for such scenarios is an easier approach with lower user maintenance. I will try and show a simple scenario of how to derive custom accounting for a business scenario using SLA in Oracle Payables.

Business Scenario: We need to define different liability account (natural account segment) based on Supplier Type so that business can track the liability by supplier type. The other segment values will default from supplier site. I am limiting this example to only one supplier type “Contractor". The objective would be to have a different natural account for Liability account for invoices of supplier type "Contractor" alone, while for other supplier types the normal liability account should default.

Solution:

Step1: First define a mapping set for various supplier types.

Navigation: Setup > Accounting Setups >Sub Ledger Accounting Setups >Accounting Methods Builders > Journal Entry Setups > Mapping Sets

Step2: Define ADR (Account Derivation Rules)

Navigation: Setup > Accounting Setups >Sub Ledger Accounting Setups >Accounting Methods Builders > Journal Entry Setups > Account Derivation Rules

Step3: Define JLD (Journal Line Definition)

Navigation: Setup > Accounting Setups >Sub Ledger Accounting Setups > Accounting Methods Builder > Methods and Definitions > Journal Line Definitions

Always create a copy of the seeded JLD and do not modify a seeded JLD. We will create a copy of ACCRUAL_INVOICES_ALL for our Chart of Accounts ‘Operations Accounting Flex’ only. Add the custom ADR created to ‘Liability, Basic’ (Line Assignment)

Step 4: Setup AAD (Application Accounting Definition)

Navigation: Setup > Accounting Setups >Sub Ledger Accounting Setups > Accounting Methods Builder > Methods and Definitions > Application Accounting Definition

Create a copy of seeded AAD only and do not modify existing AAD. I am creating a custom AAD called ‘TEST_AAD’ for COA ‘Operations Accounting Flex’.

Step 5: Setup SAM (Subledger Accounting Methods)

Navigation: Setup > Accounting Setups >Sub Ledger Accounting Setups > Accounting Methods Builder > Methods and Definitions > Subledger Accounting Methods

Create a copy of a seeded SAM and do not modify seeded SAM. Add the custom AAD to the Event Class ‘Payables’.

Step 6: Assign the custom SAM to Primary Ledger

Navigation: Set ups > Accounting Setups > Ledger Setup > Define > Accounting Setup

Verification of new SLA rule:

Create an invoice for supplier type ‘Contractor’ and create accounting

Liability Account for Supplier Type “Contractor” is 01-000-2990-0000-000

Liability Account for other Supplier Types is 01-000-2210-0000-000

Show me all active ebiz users who have logged into a forms based responsibility in the last 6 months.....

If you are upgrading JREs, or just want to know who uses forms based responsibilities, here's a sample SQL. Note, it assumes you have auditing switched on at the form level.

SELECT lower(user_name)
  FROM fnd_user e
 WHERE trunc(sysdate) < nvl(end_date,sysdate+1) 
   AND EXISTS
    (SELECT 'x'
       FROM apps.FND_USER_RESP_GROUPS_DIRECT a,
            apps.fnd_responsibility_tl b,
            fnd_responsibility b1,
            FND_LOGIN_RESPONSIBILITIES fl,
            FND_LOGINS U
      WHERE a.end_date is null --active responsibility
        AND b.language = 'US'
        AND a.responsibility_id = b.responsibility_id
        AND b1.responsibility_id = b.responsibility_id
        AND b1.application_id = b.application_id
        AND b1.version = '4'  --forms based responsibilities
        AND e.user_id = a.user_id 
        AND fl.responsibility_id = b.responsibility_id
        AND fl.login_id = u.login_id
        AND fl.resp_appl_id = b1.application_id
        AND trunc(fl.start_time) > trunc(sysdate-185)) -- 6 months