Oracle Apps Gurus

Query to find profile Option values using SQL

2011-12-02T07:52:00.001-08:00

SELECT po.profile_option_name "NAME", po.user_profile_option_name,
DECODE (TO_CHAR (pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER',
'???'
) "LEV",
DECODE (TO_CHAR (pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'???'
) "CONTEXT",
pov.profile_option_value "VALUE"
FROM fnd_profile_options_vl po,
fnd_profile_option_values pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE 1 = 1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
AND svr.node_id(+) = pov.level_value
AND org.organization_id(+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE";

Query to retrieve Discoverer business areas, users and privileges

2011-06-18T16:47:00.000-07:00

SELECT eu_username
FROM eul5_eul_users a, eul5_access_privs b, eul5_bas c
WHERE a.eu_id = b.ap_eu_id
AND b.gba_ba_id = c.ba_id
AND c.ba_name = --- BA_NAME

Thanks to Juntaletras for sharing the script.

Script to update Supplier Bank Payment Method

2011-06-18T16:39:00.000-07:00

DECLARE
x_return_status VARCHAR2 (200) := NULL;
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (200) := NULL;
t_output VARCHAR2 (200) := NULL;
t_msg_dummy VARCHAR2 (200) := NULL;
l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type;
p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type;
--IBY_PAYMENT_METHODS_B.payment_method_code%TYPE :='CHECK';
p_ext_payee_id_tab_type iby_disbursement_setup_pub.ext_payee_id_tab_type;
--IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE:=12193;
i NUMBER := 0;
BEGIN
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;
--for xyz in abc
--loop
i := i + 1;
p_external_payee_tab_type (i).default_pmt_method := 'EFT';
p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';
p_external_payee_tab_type (i).exclusive_pay_flag := 'N';
p_external_payee_tab_type (i).payee_party_id := 167912;
p_ext_payee_id_tab_type (i).ext_payee_id := 17030;
--end loop
apps.fnd_global.apps_initialize (1774, 51308, 200);
mo_global.set_policy_context ('S', 305);
iby_disbursement_setup_pub.update_external_payee
(p_api_version => 1.0,
p_init_msg_list => 'T',
--fnd_api.g_true,
p_ext_payee_tab => p_external_payee_tab_type,
p_ext_payee_id_tab => p_ext_payee_id_tab_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_ext_payee_status_tab => l_payee_upd_status
);
DBMS_OUTPUT.put_line ('Return Status : ' || x_return_status);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);

IF x_return_status <> 'S'
THEN
IF x_msg_count > 0
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, x_msg_data, t_msg_dummy);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);
t_output := (TO_CHAR (i) || ': ' || x_msg_data);
END LOOP;
END IF;

DBMS_OUTPUT.put_line
( 'Error occured while updating the Payment Method'
|| t_output
);
END IF;

FOR j IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST
LOOP
DBMS_OUTPUT.put_line ( 'Error Message from table type : '
|| l_payee_upd_status (j).payee_update_msg
);
END LOOP;
END;

Thanks to Kiran Kammili, Apps Associates LLC for sharing the code.

Upload/Download XML RTF Templates in Oracle from UNIX

2011-01-28T06:26:00.000-08:00

For Downloading RTF template:

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD
-DB_USERNAME
-DB_PASSWORD
-JDBC_CONNECTION
-LOB_TYPE
-APPS_SHORT_NAME
-LOB_CODE
-LANGUAGE
-TERRITORY
-LOG_FILE

For Uploading RTF template:

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD
-DB_USERNAME
-DB_PASSWORD
-JDBC_CONNECTION
-LOB_TYPE
-APPS_SHORT_NAME
-LOB_CODE
-LANGUAGE
-TERRITORY
-XDO_FILE_TYPE
-NLS_LANG
-FILE_CONTENT_TYPE
-FILE_NAME
-OWNER
-CUSTOM_MODE [FORCE|NOFORCE]
-LOG_FILE

Oracle R12: Script to update Serial Numbers on Install Base

2011-01-25T11:09:00.000-08:00

/* Formatted on 2011/01/25 14:10 (Formatter Plus v4.8.0) */
DECLARE
CURSOR ib_cur
IS
SELECT cii.instance_id, cii.serial_number, cii.inventory_item_id,
cii.object_version_number
FROM csi_item_instances cii
WHERE 1 = 1 AND cii.instance_id = :ip_instance_id
ORDER BY 1;

TYPE ib_rec_tbl_type IS TABLE OF ib_cur%ROWTYPE;

ib_rec_tbl ib_rec_tbl_type;
l_api_version CONSTANT NUMBER := 1.0;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_instance_id_lst csi_datastructures_pub.id_tbl;
l_instance_header_rec csi_datastructures_pub.instance_header_rec;
l_party_header_tbl csi_datastructures_pub.party_header_tbl;
l_party_acct_header_tbl csi_datastructures_pub.party_account_header_tbl;
l_org_unit_header_tbl csi_datastructures_pub.org_units_header_tbl;
l_instance_rec csi_datastructures_pub.instance_rec;
l_party_tbl csi_datastructures_pub.party_tbl;
l_account_tbl csi_datastructures_pub.party_account_tbl;
l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
l_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
l_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl;
l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
l_txn_rec csi_datastructures_pub.transaction_rec;
l_return_status VARCHAR2 (5);
lc_init_msg_lst VARCHAR2 (1) := 'T';
ln_validation_level NUMBER;
lc_error_text VARCHAR2 (4000);
BEGIN
OPEN ib_cur;

FETCH ib_cur
BULK COLLECT INTO ib_rec_tbl;

CLOSE ib_cur;

IF ib_rec_tbl.COUNT > 0
THEN
DBMS_OUTPUT.put_line ('Begin loop');

FOR i IN ib_rec_tbl.FIRST .. ib_rec_tbl.LAST
LOOP
l_instance_header_rec.instance_id := ib_rec_tbl (i).instance_id;
csi_item_instance_pub.get_item_instance_details
(p_api_version => l_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_header_rec,
p_get_parties => fnd_api.g_true,
p_party_header_tbl => l_party_header_tbl,
p_get_accounts => fnd_api.g_true,
p_account_header_tbl => l_party_acct_header_tbl,
p_get_org_assignments => fnd_api.g_true,
p_org_header_tbl => l_org_unit_header_tbl,
p_get_pricing_attribs => fnd_api.g_false,
p_pricing_attrib_tbl => l_pricing_attribs_tbl,
p_get_ext_attribs => fnd_api.g_false,
p_ext_attrib_tbl => l_ext_attrib_tbl,
p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
p_get_asset_assignments => fnd_api.g_false,
p_asset_header_tbl => l_asset_header_tbl,
p_resolve_id_columns => fnd_api.g_false,
p_time_stamp => SYSDATE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
lc_error_text := NULL;
l_instance_rec.instance_id := l_instance_header_rec.instance_id;
l_txn_rec.transaction_id := fnd_api.g_miss_num;
l_txn_rec.transaction_date := SYSDATE;
l_txn_rec.source_transaction_date := SYSDATE;
l_txn_rec.transaction_type_id := 205;
l_instance_rec.serial_number := :ip_new_serial_number;
l_instance_rec.object_version_number :=
l_instance_header_rec.object_version_number;
DBMS_OUTPUT.put_line ( 'Updating IB record for IB# '
|| ib_rec_tbl (i).instance_id
);
csi_item_instance_pub.update_item_instance
(p_api_version => l_api_version,
p_commit => 'F',
p_init_msg_list => lc_init_msg_lst,
p_validation_level => ln_validation_level,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
DBMS_OUTPUT.put_line
( 'Error updating the install base for IB# '
|| ib_rec_tbl (i).instance_id
);

FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => -1,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index
);
lc_error_text := lc_error_text
|| (SUBSTR (l_msg_data, 1, 255));
END LOOP;

DBMS_OUTPUT.put_line (lc_error_text);
ELSE
DBMS_OUTPUT.put_line
( 'Install base update successful for IB# '
|| ib_rec_tbl (i).instance_id
);
lc_error_text := 'SUCCESS!';
COMMIT;
END IF;
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No records to process');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error in update_ib procedure');
DBMS_OUTPUT.put_line (TO_CHAR (SQLCODE) || ' - ' || SQLERRM);
END;

Oracle Receivables Module Technical Details

2011-01-20T11:33:00.000-08:00

Oracle Accounts Receivable uses the following tables for recording customer account information:

The major tables containing parties and customer accounts information in Oracle Receivables are grouped by business function.
• HZ_PARTIES
• HZ_CUST_ACCOUNTS
• HZ_PARTY_SITES
• HZ_CUST_ACCT_SITES_ALL
• HZ_CUST_SITE_USES_ALL
• HZ_LOCATIONS
• HZ_PARTY_RELATIONSHIPS
• HZ_ORGANIZATION_PROFILES
• HZ_CONTACT_POINTS
• HZ_CUST_ACCOUNT_ROLES
• HZ_PERSON_PROFILES
• HZ_ORG_CONTACTS

Transaction Tables
• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES_ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_CASH_RECEIPTS_ALL
• AR_CASH_RECEIPT_HISTORY_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL

HZ_PARTIES
A party is an entity that can enter into a business relationship.
This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.

Parties can be one of four types:
Organization - Oracle Corporation
Person - Jane Doe
Group – Doc Household
Relationship - Jane Doe at Oracle Corporation

HZ_LOCATIONS
A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
• This table stores information about an address such as: street address and postal code.
• This table provides physical location information about parties (organizations and people) and customer accounts.
• Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.

HZ_PARTY_SITES
This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
• One party can point to one or more party sites.
• One location can point to one or more party site.
• Party sites serve as the intersection between parties and locations, allowing for a many-to-many relationship between the two.

HZ_RELATIONSHIPS
This table stores information about relationships between two entities, for example, one party and another party.
• The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship type is “Parent Of,” then a holding company could be the “SUBJECT_ID” in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created.
• A party can have different relationships with one or more other parties that can change over time.

HZ_ORGANIZATION_PROFILES
This table stores a variety of information about a party of type Organization.
• This table gets populated when a party of type ORGANIZATION is created.
• Historical data is also stored in this table.

HZ_PERSON_PROFILES
This table stores a variety of information about a party of type Person.
• For example, this table could contain the correct spelling and phonetic pronunciation of the person’s name.
• Some information in this table may also be entered into the HZ_PARTIES table.

HZ_ORG_CONTACTS
This table stores a variety of information about an organization contact.
• The records in this table provide information about a contact position such as job title, rank, and department.
• This table is not used to store information about a specific person or organization. For example, this table may include a record for the position of Vice President of Manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position.

HZ_CUST_ACCOUNTS
This table stores information about customer/financial relationships established between a Party and the deploying company.
• Because a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.

HZ_CUST_ACCT_SITES_ALL
This table stores information about customer/financial account sites information.
Stores information about customer sites. One customer account can have multiple sites.

HZ_CUST_SITE_USES_ALL
This table stores information about the business purposes assigned to a customer account site.
• A customer account site can have multiple purposes, but each record in this table only specifies one purpose for a customer account site. For example, a customer account site may be assigned as a ship-to site in one record and as a bill-to site in another record.

HZ_CUST_ACCOUNT_ROLES
This table stores information about a role or function that a party performs as related to a customer account. For example, Jane Doe might be the Legal Contact for a specific customer account of Corporation ABC. Note that account ownership such as financial responsibility for an account is determined by the single party ID that is stored directly on the HZ_CUST_ACCOUNTS table.

HZ_CONTACT_POINTS
This table stores information about how to communicate with parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
• Each medium or method should be stored as a separate method in this table. For example, the attributes of a complete telephone number connection should be stored in a record, while EDI information should be stored in a different record.

RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commit-ment, chargeback, bills receivable, and credit memo header information.
• Each row includes general invoice information such as customer, transaction type, and printing instructions.
• You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables and these are all distinguished by their transaction types stored in RA_CUST_ TRX_ TYPES_ALL.

RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. It describes to the customer the charges that appear on these documents.

AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjust-ments and miscellaneous cash receipts. A miscellaneous cash receipt is one that is not connected to a customer.
• All customer-related activity is logged in this table.
• This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable,
receipt, or commitments.

RA_CUST_TRX_TYPES_ALL
This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
• Each row includes Auto Accounting information
as well as standard defaults for the resulting invoices. The primary key for this table is CUST_TRX_TYPE_ID.

AR_TRANSACTION_HISTORY_ALL
This table is a Bills Receivable-specific table containing the history of a transaction’s lifecycle.
• A new row is created each time there is activity on the transaction or the status of the transaction has changed.
• This table stores the header for the Receivables posting information.

AR_DISTRIBUTIONS_ALL
This table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions.

AR_CASH_RECEIPTS_ALL
This table stores one record for each receipt entry.
• All cash receipts are logged in this table.
• Oracle Receivables creates records concurrently in the AR_CASH _RECEIPT_ HISTORY_ ALL, AR_PAYMENT_ SCHEDULES_ ALL, AR_DISTRI-BUTIONS_ALL, and AR_ RECEIVABLE_ APPLICA-TIONS_ALL tables for invoice-related receipts.
• For receipts that are not related to invoices, records are created in the AR_MISC_CASH_ DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ ALL table.

AR_CASH_RECEIPT_HISTORY_ALL
This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.

AR_RECEIVABLE_APPLICATIONS_ALL
This table stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flex field information.

AR_MISC_CASH_DISTRIBUTIONS_ALL
This table stores all accounting entries for miscellaneous cash applications.
• Miscellaneous cash cannot be invoiced, such as stock revenue, interest income, and investment income.
• AR_CASH_RECEIPTS_ALL stores one record for each payment, and this table stores one record for each distribution of the receipt.

AR_RECEIPT_CLASSES
This table stores the different receipt classes that you define.
• Receipt classes determine whether the receipt[s] belonging to this class are created manually or automatically, and whether the receipts go through the different steps in a receipt’s life-cycle.

AR_RECEIPT_METHODS
This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications.
• For automatically created receipts, a Payment Method defines the rules for creating these receipts.
• For manually created receipts, a Payment Method defines a user-definable type for the receipt.
• Each Payment Method is associated with a set of bank accounts, which forms the set of bank accounts you can assign to your receipt.

AR_ADJUSTMENTS_ALL
This table stores information about the adjustment applied to the Invoices.

Puchasing and Payables Module: Technical Details

2011-01-20T11:28:00.000-08:00

PO_VENDORS
This table stores information about your suppliers. Oracle Purchasing uses this information to determine active suppliers. The primary key is VENDOR_ID.

PO_VENDOR_SITES_ALL
This table stores information about supplier sites. Oracle Purchasing uses this information to store supplier address information. The primary key is VENDOR_SITE_ID.

PO_VENDOR_CONTACTS
This table stores information about supplier site contacts. The primary key is VENDOR_CONTACT_ID.

PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. Each row contains the requisition number, preparer status, and description. It is one of three tables that stores requisition information. The primary key is REQUISITION_HEADER_ID.

PO_REQUISITION_LINES_ALL
This table stores information about requisition lines. Each row contains the line number, item number, item category, item description, need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line. This table is one of three tables that stores requisition information. The primary key is REQUISITION_LINE_ID.

PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution. Each row includes the Accounting Flex field ID and requisition line quantity. This table is one of three tables that stores requisition information. The primary key is DISTRIBUTION_ID.

PO_HEADERS_ALL
PO_HEADERS_ALL contains information for your purchasing documents. Each row contains buyer information, supplier information, notes, foreign currency information, terms and conditions information, and the document status. Oracle Purchasing uses this information to record information related to a complete document. The primary key is PO-HEADER_ID.

PO_LINES_ALL
PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. Each row includes the line number, item number and category unit, price, tax information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs. The primary key is PO_LINE_ID.

PO_LINE_LOCATIONS_ALL
This table contains information about purchase order shipment schedules and blanket agreement price breaks. You must have one row for each schedule or price break you attach to a document line. Each row contains the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders and price break information for blanket purchase orders, quotations, and RFQs. The primary key is LINE_LOCATION_ID.

PO_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL contains accounting information for a purchase order shipment line. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases. It is one of five tables that stores purchase orders and releases. The primary key is PO_DISTRIBUTION_ID.

RCV_SHIPMENT_HEADERS
This table stores common information about the source of your receipts or expected receipts. You group your receipts by the source type and the source of the receipt. Oracle Purchasing does not allow you to group receipts from different sources under one receipt header. The primary key is SHIPMENT_HEADER_ID.

RCV_SHIPMENT_LINES
This table stores information about items that have been shipped or received from a specific receipt source. This table also stores information about the default destination for in-transit shipments. The primary key is SHIPMENT_LINE_ID.

RCV_TRANSACTIONS
This table stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table.
Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing. The primary key is TRANSACTION_ID.

PO_RELEASES_ALL
This table contains information about blanket and planned purchase order releases. You need one row for each release you issue for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment (PO_LINE_LOCATIONS_ALL). The primary key is PO_RELEASE_ID.

AP_INVOICES_ALL
This table contains records for supplier invoices and has one row for each invoice.
To pass Invoice Validation, the INVOICE_AMOUNT in the AP_INVOICES_ALL table must equal the sum of the AMOUNT columns in the AP_DISTRIBUTIONS_ALL table. The primary key is INVOICE_ID.

AP_INVOICE_DISTRIBUTIONS_ALL
This table holds the distribution line information.
There is a row for each invoice distribution. If matching is used, distribution information is copied from PO_DISTRIBUTIONS_ALL during the matching process.

AP_PAYMENT_SCHEDULES_ALL
This table contains information about scheduled payments for an invoice.
Oracle Payables uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch.

AP_HOLDS_ALL
This table contains a record for each hold placed on an invoice.
An invoice might have multiple records in this table.
An invoice cannot be paid until all holds placed on it have been released.

AP_CHECKS_ALL
This table stores information about payments issued to suppliers.
Each row includes the supplier name, address, and bank account name for auditing purposes, in case any of them change after you make the payment.
If the payment is electronic, the supplier bank account information is stored in the payment record.

AP_INVOICE_PAYMENTS_ALL
This table provides the link between a payment (CHECK_ID) and the invoice(s) paid by that payment.
This table contains records of invoice payments that you make to suppliers. Oracle Payables updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick Payment. This table contains one row for each payment made for each invoice.
Void payments are also entered into this table as a negative record of the original payment line.

AP_BANK_ACCOUNTS_ALL
This table stores information about your bank accounts and your suppliers’ and customers’ bank accounts. This table contains one row for every bank account, whether it is an internal (supplier) or external (customer) account.

AP_BANK_BRANCHES
This table stores information about the bank branches and details about the branches.

Thanks to my friend Sairam Malla for sharing this technical information

PL/Sql script to add System Administrator responsibility

2011-01-17T13:23:00.000-08:00

BEGIN
fnd_user_pkg.addresp
(username => UPPER
('arathod'),
resp_app => 'SYSADMIN',
resp_key => 'SYSTEM_ADMINISTRATOR',
security_group => 'STANDARD',
description => 'DESCRIPTION',
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not added due to'|| SQLCODE|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
/

Script to create a FND user using PL/SQL

2011-01-17T13:20:00.000-08:00

DECLARE
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER := fnd_global.user_id;
x_user_id NUMBER;
l_password VARCHAR2 (2000) := 'welcome1';
BEGIN
apps.hr_user_acct_internal.create_fnd_user (p_user_name => 'arathod',
p_password => l_password,
p_employee_id => NULL,
p_user_id => x_user_id,
p_user_start_date => SYSDATE,
p_email_address => NULL,
p_description => NULL,
p_password_date => NULL
);
DBMS_OUTPUT.put_line (x_user_id);

IF x_user_id IS NOT NULL
THEN
UPDATE fnd_user
SET password_lifespan_days = 90
WHERE user_id = x_user_id;

COMMIT;
END IF;
END;

Oracle PO Archive extract

2011-01-17T13:16:00.000-08:00

SELECT po_number, creation_date po_date, vendor_num, vendor_name,
revision_num, approved_date, closed_date,
change_type type_of_modification
FROM (SELECT pv.segment1 vendor_num, pv.vendor_name, pha.po_header_id,
pha.segment1 po_number, pha.agent_id, pha.creation_date,
pha.vendor_id, phaa.submit_date, pha.revision_num rev_num,
phaa.revision_num, pha.approved_flag, pha.approved_date,
pha.closed_date, phaa.closed_code, pha.cancel_flag,
CASE
WHEN ( NVL (pha.approved_flag, '01-JAN-1951') <>
'01-JAN-1951'
AND NVL (pha.closed_date, '01-JAN-1951') <>
'01-JAN-1951'
AND NVL (phaa.closed_code, 'OPEN') = 'OPEN'
AND pha.approved_flag <> 'F'
AND phaa.revision_num = pha.revision_num
)
THEN 'APPROVED & CLOSED'
WHEN ( NVL (pha.approved_flag, '01-JAN-1951') <>
'01-JAN-1951'
AND NVL (pha.closed_date, '01-JAN-1951') =
'01-JAN-1951'
AND pha.approved_flag = 'Y'
AND phaa.revision_num = pha.revision_num
)
THEN 'APPROVED'
WHEN ( NVL (phaa.closed_code, 'OPEN') = 'CLOSED'
AND pha.cancel_flag = 'Y'
)
THEN 'CANCELLED'
WHEN (pha.approved_flag = 'F')
THEN 'REJECTED'
WHEN phaa.submit_date IS NULL
THEN 'NEW'
WHEN phaa.revision_num < pha.revision_num
THEN 'CHANGE'
END change_type
FROM po_headers_all pha,
po_headers_archive_all phaa,
po_vendors pv
WHERE 1 = 1
AND phaa.po_header_id(+) = pha.po_header_id
AND pv.vendor_id = pha.vendor_id
ORDER BY pha.po_header_id, phaa.revision_num) po_archive

Enabling AuditTrail Functionality to capture changes to project status

2011-01-17T12:33:00.000-08:00

Set profile Option
1. Log in with the responsibility: System Administrator
Navigation Path: System/profile
2. Query on profile option: AuditTrail:Activate

3. Ensure that this profile option is set to yes at site level and not overwriten by a No at application level ( valid values are yes or null when it is defined at site level as indicated above)

Install
1. Log in with the responsibility: System Administrator
Navigation Path: Security/AuditTrail/Install
2. Query on profile option: Oracle Username : PA

3. Ensure that the user account for Oracle Project Accounts is checked as enabled for audit

Define the Audit Group
1. Log in with the responsibility: System Administrator
Navigation Path: Security/AuditTrail/Groups
2. Create an audit group for the application projects

3. Add PA_PROJECTS_ALL table in the audit tables list

Define the Audit Columns
1. Log in with the responsibility: System Administrator
Navigation Path: Security/AuditTrail/Tables
2. Query on the Table Name PA_PROJECTS_ALL and add the column PROJECT_STATUS_CODE to it

AuditTrail Update tables
1. Log in with the responsibility: System Administrator
Navigation Path: Requests/Run
2. Submit the report: AuditTrail Update Tables

Tables Schema created

select * from dba_objects
where object_name like 'PA_PROJECTS_ALL_A'

AuditTrail Report for Audit Group Validation
1. Log in with the responsibility: System Administrator
Navigation Path: Requests/Run
2. Submit the report: AuditTrail Report for Audit Group Validation by choosing the audit group in parameters

This process creates Shadow Tables with the same data type columns. Shadow Tables have the name _A which mirror the audited tables. (in our example the audited table is PA_PROJECTS_ALL and the shadow table is PA_PROJECTS_ALL _A).It also creates views on the Shadow Tables with the name _AC1 and _AV1 which allow access to the data in the Shadow Tables.

Script to Update Oracle Project Tasks: pa_project_pub.update_task

2010-10-22T10:35:00.000-07:00

DECLARE
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_pm_product_code VARCHAR2 (10);
l_out_pa_task_id NUMBER;
l_out_pm_task_reference VARCHAR2 (2000);
l_output VARCHAR2 (2000);
l_msg_dummy VARCHAR2 (2000);
api_error EXCEPTION;
n NUMBER := 0;
l_pa_project_id NUMBER := :project_id;
BEGIN
pa_interface_utils_pub.set_global_info (p_api_version_number => 1.0,
p_responsibility_id => :Responsibility_id,
p_user_id => :User_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status
);
pa_project_pub.update_task
(p_api_version_number => '1.0',
p_commit => 'F',
p_init_msg_list => 'F',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_pm_product_code => l_pm_product_code,
p_pa_project_id => l_pa_project_id,
p_pa_task_id => :pa_task_id,
-- p_task_description => 'TEST DESC1',
p_tasks_dff => 'N',
p_out_pa_task_id => l_out_pa_task_id,
p_out_pm_task_reference => l_out_pm_task_reference
);
COMMIT;

IF l_return_status <> 'S'
THEN
FOR n IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (n, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (n) || ': ' || l_msg_data);
DBMS_OUTPUT.put_line
( 'Error: API Error while updating the Task: '
|| l_output
);
COMMIT;
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('Sucessfully Update the task');
COMMIT;
END IF;
EXCEPTION
WHEN api_error
THEN
DBMS_OUTPUT.put_line ('API Error in Project: ');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Other Error in Project: ' || SQLERRM);
END;

Steps to customize the PO Approval Workflow.

2010-10-13T18:36:00.000-07:00

1. Take the seeded workflow (POAPPRV)
2. Right click on the Item TYpe and create a new Item TYpe .
3. Open the Seeded Workflow(POAPPRV) in other window .
4. Copy and paste ATTRIBUTES, NOTIFICATIONs, FUNCTIONS and PROCESSES etc from the the seeded workflow to the Newly named workflow .
5. Verify the workflow
6. Customize the new workflow according to your needs.
7. Save the changes and port it to Oracle.

Login with a PO Super User responsibility, go to Setup -> Document Types, select the PO types for which you want to use the custom workflow, and change workflow fields 'Approval Workflow' and 'Workflow Startup Process' to the new ones.

API to Close Purchase Order

2010-10-02T17:53:00.000-07:00

po_actions.close_po

To Close the PO we need to pass 'CLOSE' to the p_action variable and make sure to pass 'N' to the p_auto_close.

Sql Query to find Unbaselined Fundings

2010-09-14T20:11:00.001-07:00

SELECT ppf.project_number, ppf.project_name,
pps.project_system_status_code project_status, ppf.task_number,
ppf.task_name, paa.agreement_num, paa.agreement_type,
paa.amount agreement_amount, ppf.allocated_amount funding_amount,
ppf.date_allocated, ppf.funding_category,
ppf.budget_type_code baseline_status, ppf.last_update_date,
fu.user_name last_updated_by
FROM pa_agreements_all paa,
pa_project_fundings_v ppf,
fnd_user fu,
pa_projects_all ppa,
pa_project_statuses pps
WHERE paa.agreement_id = ppf.agreement_id
AND ppa.project_status_code = pps.project_status_code
AND ppa.project_id = ppf.project_id
AND fu.user_id = ppf.last_updated_by
AND ppf.budget_type_code <> 'BASELINE'
ORDER BY paa.agreement_id;

Sql Script to find Projects which are funded by multiple Sales Order (Agreement)

2010-09-14T20:08:00.000-07:00

SELECT paa.agreement_num sales_order, ppa.segment1 project_number,
paps.project_status_name project_status,
pspf.total_baselined_amount funding_amount,
TRUNC (pspf.last_update_date) last_funding_date, '-Budget-',
pbv.revenue,PA.FULL_NAME PROJECT_MANAGER
FROM pa_projects_all ppa,
(SELECT pspf.project_id
FROM pa_summary_project_fundings pspf
WHERE 1 = 1
GROUP BY pspf.project_id
HAVING COUNT (*) > 1) multi,
(SELECT *
FROM pa_budget_versions
WHERE budget_type_code = 'Revenue Budget'
AND budget_status_code = 'B'
AND current_flag = 'Y') pbv,
pa_summary_project_fundings pspf,
pa_project_classes ppct,
pa_agreements_all paa,
pa_employees pa,
pabv_key_members pkm
WHERE multi.project_id = ppa.project_id
AND pspf.project_id = ppa.project_id
AND paa.agreement_id = pspf.agreement_id
AND paps.project_status_code = ppa.project_status_code
AND ppa.project_id = pbv.project_id(+)
AND pa.person_id = pkm.person_id
AND pkm.project_id = ppa.project_id
AND pkm.project_role_type = 'PROJECT MANAGER'
and NVL (PKM.END_EFFECTIVE_DATE, TRUNC (sysdate)) >= TRUNC (sysdate)
and NVL (PA.EFFECTIVE_END_DATE, TRUNC (sysdate)) >= TRUNC (sysdate)
AND current_flag = 'Y'
AND paps.project_status_name <> 'Not Acquired'
AND ppa.template_flag = 'N'

Sql Script to extract Vendor, Project, Receipt information for a PO

2010-09-14T19:57:00.000-07:00

SELECT pha.po_header_id, pha.segment1 po_number, pov.vendor_name,
pov.segment1 vendor_num, pla.line_num po_line_number, pla.item_id,
(SELECT MAX (segment1)
FROM mtl_system_items_b
WHERE inventory_item_id = pla.item_id) item,
DECODE (NVL (pla.item_id, 0),
0, NULL,
pla.item_description
) item_description,
DECODE (pla.cancel_flag,
'Y', 'CANCELLED',
DECODE (pha.authorization_status,
'IN PROCESS', 'PENDING APPROVAL',
NVL (pha.authorization_status, 'INCOMPLETE')
)
) authorization_status,
DECODE (pla.cancel_flag,
'Y', 'N/A',
DECODE (pha.authorization_status,
'APPROVED', DECODE (NVL (pla.closed_code, 'b'),
'CLOSED', 'CLOSED',
'FINALLY CLOSED', 'CLOSED',
'OPEN'
),
'N/A'
)
) po_status,
pha.creation_date date_issued, pla.creation_date line_creation_date,
(pda.quantity_ordered - pda.quantity_cancelled) po_quantity,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_ordered, 0)
) po_line_amt,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_cancelled, 0)
) po_cancelled_amount,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_delivered, 0)
) po_line_received_amount,
rt.transaction_date receipt_date,
TO_CHAR (rt.transaction_date, 'MON-RR') receipt_month,
rsh.receipt_num,
(DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_quantity,
( NVL (rt.po_unit_price, 0)
* DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_amount,
ai.invoice_id, ai.invoice_num, ai.invoice_amount, ai.amount_paid,
ai.invoice_date, aid.quantity_invoiced, aid.amount inv_line_amount,
ppa.project_id, ppa.segment1 project_number, ppa.NAME project_name,
(SELECT full_name
FROM pa_project_players_v
WHERE project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER'
AND NVL (end_date_active, SYSDATE) IN (
SELECT MAX (NVL (end_date_active, SYSDATE))
FROM pa_project_players_v
WHERE 1 = 1
AND project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER')
AND ROWNUM < 2) "Project Manager",
total_billings.invoice_amount total_actual_billings
FROM po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pov,
pa_projects_all ppa,
(SELECT i.project_id, SUM (NVL (ii.inv_amount, 0)) invoice_amount
FROM pa_draft_invoices_all i, pa_draft_inv_items_bas ii
WHERE 1 = 1
AND ii.project_id(+) = i.project_id
AND ii.draft_invoice_num(+) = i.draft_invoice_num
AND i.pa_date < ((SELECT end_date
FROM pa_periods_all ppi
WHERE ppi.current_pa_period_flag = 'Y') + 1)
GROUP BY i.project_id) total_billings
WHERE pha.po_header_id = pla.po_header_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pla.po_line_id
AND pda.po_header_id = pha.po_header_id
AND pha.vendor_id = pov.vendor_id
AND pda.po_distribution_id = aid.po_distribution_id(+)
AND aid.invoice_id = ai.invoice_id(+)
AND pla.po_line_id = rt.po_line_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.destination_type_code(+) = 'RECEIVING'
AND pda.project_id = ppa.project_id(+)
AND ppa.project_id = total_billings.project_id(+)

Script to Approve PO Requisition

2010-09-14T19:51:00.000-07:00

/* Formatted on 2010/05/15 11:23 (Formatter Plus v4.8.0) */
DECLARE
l_itemkey VARCHAR2 (200);
l_po_id NUMBER := :PO_HEADER_ID;
l_po_number VARCHAR2 (200) := :REQUISITION_NUMBER;
BEGIN
SELECT l_po_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO l_itemkey
FROM DUAL;

wf_engine.createprocess ('REQAPPRV',
l_itemkey,
'MAIN_REQAPPRV_PROCESS',
NULL,
:USER_NAME
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'RESPONSIBILITY_ID',
avalue => :RESPONSIBILITY_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'APPLICATION_ID',
avalue => :APPLICATION_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'USER_ID',
avalue => :USER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SUBMITTER_ID',
avalue => :BUYER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_ID',
avalue => l_po_id
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_NUMBER',
avalue => l_po_number
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'ORG_ID',
avalue => :ORG_ID
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_SUBTYPE',
avalue => 'PURCHASE'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE',
avalue => 'REQUISITION'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE_DISP',
avalue => 'Purchase Requisition'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS',
avalue => 'APPROVED'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS_DISP',
avalue => 'Approved'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SEND_CREATEPO_TO_BACKGROUND',
avalue => 'Y'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'INTERFACE_SOURCE_CODE',
avalue => 'PO_FORM'
);

DBMS_OUTPUT.put_line (l_itemkey);
wf_engine.startprocess ('REQAPPRV', l_itemkey);
COMMIT;
END;
/

Script to cancel PO Requisition

2010-09-14T19:48:00.000-07:00

DECLARE
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
lv_header_id po_tbl_number;
lv_line_id po_tbl_number;
m NUMBER := NULL;
l_msg_dummy VARCHAR2 (2000);
l_output VARCHAR2 (2000);
BEGIN
m := 1;
lv_header_id := po_tbl_number (:REQ_HEADER_ID);
lv_line_id := po_tbl_number (:REQ_LINE_ID);
po_req_document_cancel_grp.cancel_requisition
(p_api_version => 1.0,
p_req_header_id => lv_header_id,
p_req_line_id => lv_line_id,
p_cancel_date => SYSDATE,
p_cancel_reason => 'Cancelled Requisition',
p_source => 'REQUISITION',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
COMMIT;
DBMS_OUTPUT.put_line (l_return_status);

IF l_return_status <> 'S'
THEN
fnd_msg_pub.get (m, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (m) || ': ' || l_msg_data);
DBMS_OUTPUT.put_line (l_output);
END IF;
END;

Restrict 'Change Status' button on Projects Screen

2010-07-17T07:52:00.000-07:00

1. Navigation: System Administrator -> Responsibility -> Define
2. Query for the Responsibility Name to which we want to restrict the button
3. Under the Menu Exclusions tab include the Menu ‘Project Status Function Security Menu’

This will grey out the 'Change Status' button and the user will not be able to update the status.

Form error while trying to update Projects 'FRM-40654: Record has been updated. Requery block to see change'

2010-07-17T07:41:00.000-07:00

While trying to update a Project we were getting the following error:

'FRM-40654: Record has been updated. Requery block to see change'

On investigation we found that the issue is due to trailing or leading spaces in attribute columns for the project. Oracle has provided a fix for this:

Refer to the Metalink DOC ID 362800.1

Run this script with the 'Automatically Fix all the errors Found' parameter as 'N' initially to determine the columns with the trailing or leading spaces. Once you analyze the data and everything looks good then we can run the script again with 'Automatically Fix all the errors Found' parameter as 'Y'.

How to add logo on 'PO Output for Communication' report - PO_STANDARD_XSLFO

2010-05-09T08:07:00.000-07:00

1. Login as a user with the XML Publisher Administrator responsibility and then navigate to the Home: Templates page.
2. From the XML Publisher Templage page, query for 'PO_STANDARD_XSLFO' code and click on the 'Standard Purchase Order Stylesheet' in the Create Template section.
3. On the View Template page Scroll down to the Add File section and click the Download link. This will pop save/Open option for the 'PO_STANDARD_XSLFO.xsl' spreadsheet. Save the file to your local disk.
4. Scroll up again to the General section and click the Update button.
5. Apply the end date with history date. Preferably the end date should be (SYSDATE-1) and click Apply button. This will end date the standard template.
6. Locate the 'PO_STANDARD_XSLFO.xsl' on your local drive. Open the file in Wordpad and press ctl+f to find the 'Logo' string in the file. Uncomment the and strings. This code is used to display the image at top left corner in the first page. The complete block after changes should read as below:

7. Save the file and make sure that you have taken appropiate backup of the XSL spreadsheet before doing any updates.
8. Navigate to XML Publisher Template page again. Click the Create Template button link and enter data per the following steps:

i. Enter a unique name in the Name field -- Custom Purchase Order Stylesheet
ii. Enter unique code in the Code field -- PO_STANDARD_XSLFO1
iii. Enter Purchasing in the Application field
iv. Select XSL-FO from the Type drop-down list box
v. Enter Standard Purchase Order Data Source in the Data Definitionbox -- Standard Purchase Order Data Source
vi. Click the Browse button and navigate to the edited PO_STANDARD_XSLFO.xsl file where it is located on your
computer
vii. Enter English in the Language box
Viii.Click Apply button

9. Login with Purchasing Super User responsibility and then navigate to Setup / Organizations / Purchasing Options / Control TAB / set 'PO Output Format' = 'PDF'

10. Navigate to setup / purchasing / document types / select "Standard Purchase Order" / Set the Document Type Layout to your new template.

p_datetrack_update_mode, p_datetrack_delete_mode

2010-01-11T01:05:00.001-08:00

Understanding the p_datetrack_update_mode Control Parameter in HRMS API

The p_datetract_update_mode control parameter enables you to define the type of DateTrack change to be made. This mandatory parameter must be set to one of the values as mentioned below:

UPDATE - Keep history of existing information
CORRECTION - Correct existing information
UPDATE_OVERRIDE - Replace all scheduled changes
UPDATE_CHANGE_INSERT - Insert this change before next scheduled change

Understanding the p_datetrack_delete_mode Control Parameter

The p_datetract_update_mode control parameter enables you to define the type of DateTrack deletion to be made. This mandatory parameter must be set to one of the following values:

ZAP - Completely remove from the database
DELETE - Set end date to effective date
FUTURE_CHANGE - Remove all scheduled changes
DELETE_NEXT_CHANGE - Remove next change

How to clear Apache Cache from Application without bouncing listener.

2010-01-10T23:34:00.000-08:00

While going through one of the HRMS blogs i stumbled on a very interesting article on how to clear the Apache cache without bouncing the listener. Here are the steps:

1. Navigate to "Functional Administrator" responsibility.
2. Once logged in click on the "Core Services" tab.
3. Click on "Caching Framework" link in the blue menu bar.
4. Click on "Global Configuration" link in the left vertical menu.
5. In the "Cache Policy" region click on the "Clear All Cache" button.
6. Click the "Yes" button to confirm the action.
7. Click the "Apply" button to apply the changes.

The above steps will clear the apache cache without the need of bouncing by the DBA's

Reference: http://oraclehrmsways.blogspot.com

HRMS Benefits query to fetch the dependent of the beneficiaries

2009-12-31T01:15:00.000-08:00

SELECT DISTINCT emp_papf.full_name employee_full_name,
emp_papf.national_identifier employee_ssn,
ppf.full_name dependent_name,
ppf.national_identifier dependent_ssn
FROM ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp,
per_contact_relationships pcr,
per_all_people_f ppf,
per_all_people_f emp_papf,
ben_per_in_ler pil
WHERE epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pdp.dpnt_person_id = pcr.contact_person_id
AND pcr.contact_person_id = ppf.person_id
AND pcr.person_id = emp_papf.person_id
AND pcr.personal_flag = 'Y'
AND epe.prtt_enrt_rslt_id IS NOT NULL
AND epe.per_in_ler_id = pil.per_in_ler_id
AND pcr.person_id = pil.person_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN pdp.effective_start_date
AND pdp.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd')
BETWEEN NVL (pcr.date_start,
TO_DATE (sysdate, 'rrrr/mm/dd'))
AND NVL (pcr.date_end, TO_DATE (sysdate, 'rrrr/mm/dd'))
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd')
BETWEEN emp_papf.effective_start_date
AND emp_papf.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND pdp.cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pdp.per_in_ler_id = pil.per_in_ler_id

HRMS Benefits query to fetch the beneficiaries

2009-12-31T01:13:00.000-08:00

SELECT DISTINCT emp_papf.full_name employee_name,
emp_papf.national_identifier employee_ssn,
ppf.full_name beneficiary_name,
ppf.national_identifier beneficiary_ssn
FROM per_all_people_f ppf,
per_contact_relationships pcr,
ben_prtt_enrt_rslt_f pen,
ben_pl_bnf_f pbn,
per_all_people_f emp_papf
WHERE 1 = 1
AND pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pcr.personal_flag(+) = 'Y'
AND pbn.bnf_person_id = pcr.contact_person_id(+)
AND pbn.bnf_person_id = ppf.person_id(+)
AND emp_papf.person_id = pcr.person_id
AND TO_DATE (SYSDATE, 'rrrr/mm/dd') BETWEEN pbn.effective_start_date
AND pbn.effective_end_date
AND TO_DATE (SYSDATE, 'rrrr/mm/dd')
BETWEEN NVL (ppf.effective_start_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND NVL (ppf.effective_end_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND TO_DATE (SYSDATE, 'rrrr/mm/dd')
BETWEEN NVL (emp_papf.effective_start_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND NVL (emp_papf.effective_end_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND TO_DATE (SYSDATE, 'rrrr/mm/dd') BETWEEN pen.effective_start_date
AND pen.effective_end_date

FND_NEW_MESSAGES

2009-11-15T22:29:00.001-08:00

The FND_NEW_MESSAGES table in the database stores all Oracle Applications messages for all languages. The below API is used to load new error messages in FND_NEW_MESSAGES table.

fnd_new_messages_pkg.load_row

Ex:

DECLARE
BEGIN
fnd_new_messages_pkg.load_row
(x_application_id => 800, -- Application id
x_message_name => 'INVALID_VALUES', -- error message name
x_message_number => NULL,
x_message_text => 'Cannot update values in CANCEL mode.', -- Message
x_description => 'Cannot update values in CANCEL mode.',
x_type => 'ERROR', -- message type
x_max_length => NULL,
x_category => NULL,
x_severity => NULL,
x_fnd_log_severity => NULL,
x_owner => NULL,
x_custom_mode => NULL,
x_last_update_date => SYSDATE
);

COMMIT;
END;
/

User Profiles

2009-09-23T09:16:00.000-07:00

If you are looking for information on User profiles then i think you should navigate to the below link. This link provides very good information on user profiles.

User Profiles (Click Here)

API to Purge an person from Oracle HRMS

2009-09-10T21:49:00.000-07:00

DECLARE
l_person_org_manager_warning VARCHAR2 (200);
BEGIN
hr_person_api.delete_person
(p_validate => FALSE,
p_effective_date => SYSDATE,
p_person_id => :person_id,
p_perform_predel_validation => FALSE,
p_person_org_manager_warning => l_person_org_manager_warning
);
COMMIT;
END;

Before purging the person from Oracle HRMS we need to make sure that the employee and fnd_user link is been deleted and also the person should not have an active payroll.

If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from 'Employee' to 'Applicant' and then use the above API again to purge the record.

How to upload customized Oracle Benefits Confirmation Page (BENSSCNF)

2009-09-10T21:32:00.000-07:00

1. Navigate to the XML Publisher Administrator Responsibility > Templates.
2. Use the search engine to find the 'BENSSCNF' template. The seeded report should now display.
3. Click the "Duplicate" icon next to the seeded report.
4. Change the required Code field from Copy of BENSSCNF to BENSSCNF2. Please note that these codes are non-updateable, so we need to make sure to rename them while duplicating the template itself.
5. Change the required Name field to 'xx Custom Benefit Enrollment Confirmation' and click the Apply button.
6. Click the Add File button and upload the new or customized replacement file.
7. Go back to Templates and find the seeded report we have duplicated.
8. Click the link for the seeded template and click the Update button.
9. End date the seeded template.
10. Generate the report in the application. The generated report will reflect the updated RTF.

For any reason if we want to revert back to the Original template then we need to follow the below mentioned steps:

1. Go to XML Publisher Administrator Responsibility > Templates.
2. Use the search engine to find the template you wish to copy.
3. Click on the link for the copy previously created.
4. Click the Update button and enter an end date into the End Date field. (Use the creation date to completely remove the incorrect copy.)
5. Repeat this for every incorrect copy that may exist.

Setup to enable Effective Date Tracking popup in Oracle HRMS

2009-08-25T02:12:00.000-07:00

This setup will turn on the switch that allows the effective date tracking in HRMS to pop up when someone navigates into Oracle employee record so that users do not forget to date track.

Navigation: Application Developer -> Profile -> System
Uncheck Site and check the Application
Application Field: Human Resources
Profile field : DateTrack:Reminder

Press the find button and then a screen will popup

In the Application field where we have the white background we need to give the value as ‘Always pop warning window’ and then save the form.

How to change the Address Style from US to Generic Addess Style in Oracle HRMS

2009-08-22T08:50:00.000-07:00

Select any HRMS or Super HRMS Manager Responsibility (Example: US Super HRMS Manager)

1. Open the 'Define Taskflow' form. ( Security -> Task Flow Definitions )
2. Query the Taskflow that is used by the Person form function (For Example: 'US HR PERSON TASKFLOW').
3. Go to 'Node section – Name field' and delete the 'USADDRESS' node (This node is used by the US Address Style) and then add a new node 'ADDRESS' (This node is used by the international Address Style) with the same sequence number as the 'USADDRESS' node.

Save and Exit the “Define Taskflow” form.

Logout the current applications and re-login to check that the new Address form with international address style is launched instead of old US Address Style form when “Address” button is selected in People Window.

Single record insert script for Engineering Change Orders

2009-08-16T05:06:00.001-07:00

DECLARE
p_eco_rec eng_eco_pub.eco_rec_type;
p_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type;
p_revised_item_tbl eng_eco_pub.revised_item_tbl_type;
p_rev_component_tbl bom_bo_pub.rev_component_tbl_type;
p_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type;
p_sub_component_tbl bom_bo_pub.sub_component_tbl_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_message_list error_handler.error_tbl_type;
x_eco_rec eng_eco_pub.eco_rec_type;
x_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type;
x_revised_item_tbl eng_eco_pub.revised_item_tbl_type;
x_rev_component_tbl bom_bo_pub.rev_component_tbl_type;
x_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type;
x_sub_component_tbl bom_bo_pub.sub_component_tbl_type;
x_rev_operation_tbl bom_rtg_pub.rev_operation_tbl_type;
x_rev_op_resource_tbl bom_rtg_pub.rev_op_resource_tbl_type;
x_rev_sub_resource_tbl bom_rtg_pub.rev_sub_resource_tbl_type;
x_change_line_tbl eng_eco_pub.change_line_tbl_type;
l_change_line_tbl eng_eco_pub.change_line_tbl_type;
error_msg error_handler.error_tbl_type;
BEGIN
p_eco_rec.eco_name := 'ECO009';
p_eco_rec.organization_code := 'V1';
p_eco_rec.change_name := 'A Test ECO History';
p_eco_rec.description := 'A Test ECO History';
p_eco_rec.status_name := 'Scheduled';
p_eco_rec.approval_status_name := 'Approved';
p_eco_rec.approval_date := SYSDATE;
p_eco_rec.approval_request_date := SYSDATE;
p_eco_rec.change_type_code := 'ECO';
p_eco_rec.transaction_type := 'CREATE';
p_eco_rec.plm_or_erp_change := 'ERP';

-----ECO REVISIONS
IF p_eco_revision_tbl.EXISTS (1) IS NOT NULL
THEN
p_eco_revision_tbl.DELETE;
END IF;

p_eco_revision_tbl (1).eco_name := 'ECO009';
p_eco_revision_tbl (1).organization_code := 'V1';
p_eco_revision_tbl (1).revision := 'A';
p_eco_revision_tbl (1).transaction_type := 'CREATE';

-----REVISED ITEMS
IF p_revised_item_tbl.EXISTS (1) IS NOT NULL
THEN
p_revised_item_tbl.DELETE;
END IF;

p_revised_item_tbl (1).eco_name := 'ECO009';
p_revised_item_tbl (1).organization_code := 'V1';
p_revised_item_tbl (1).revised_item_name := 'ASO0012';
p_revised_item_tbl (1).start_effective_date := SYSDATE;
p_revised_item_tbl (1).new_effective_date := SYSDATE + 2;
p_revised_item_tbl (1).transaction_type := 'CREATE';

/* ----history
l_change_line_tbl (1).eco_name := 'ECO002';
l_change_line_tbl (1).scheduled_date := SYSDATE;
l_change_line_tbl (1).assignee_name := '60805 Open';
l_change_line_tbl (1).transaction_type := 'CREATE';
p_revised_item_tbl (1).reschedule_comments := 'Testing revised items';
*/ ---COMPONENT------------
IF p_rev_component_tbl.EXISTS (1) IS NOT NULL
THEN
p_rev_component_tbl.DELETE;
END IF;

p_rev_component_tbl (1).eco_name := 'ECO009';
p_rev_component_tbl (1).organization_code := 'V1';
p_rev_component_tbl (1).revised_item_name := 'ASO0012';
p_rev_component_tbl (1).start_effective_date := SYSDATE;
p_rev_component_tbl (1).operation_sequence_number := 1;
p_rev_component_tbl (1).component_item_name := '060 1918';
p_rev_component_tbl (1).acd_type := 1;
p_rev_component_tbl (1).old_operation_sequence_number := 1;
p_rev_component_tbl (1).new_operation_sequence_number := 1;
p_rev_component_tbl (1).transaction_type := 'CREATE';

-----REFERENCE DESIGNATOR-------------
IF p_ref_designator_tbl.EXISTS (1) IS NOT NULL
THEN
p_ref_designator_tbl.DELETE;
END IF;

p_ref_designator_tbl (1).eco_name := 'ECO009';
p_ref_designator_tbl (1).organization_code := 'V1';
p_ref_designator_tbl (1).revised_item_name := 'ASO0012';
p_ref_designator_tbl (1).start_effective_date := SYSDATE;
p_ref_designator_tbl (1).operation_sequence_number := 1;
p_ref_designator_tbl (1).component_item_name := '060 1918';
p_ref_designator_tbl (1).reference_designator_name := 'ECO009_dsg';
p_ref_designator_tbl (1).acd_type := 1;
p_ref_designator_tbl (1).transaction_type := 'CREATE';
----- SUBSTITUTE COMPONENT ----------------
IF p_sub_component_tbl.EXISTS (1) IS NOT NULL
THEN
p_sub_component_tbl.DELETE;
END IF;

p_sub_component_tbl (1).eco_name := 'ECO009';
p_sub_component_tbl (1).organization_code := 'V1';
p_sub_component_tbl (1).revised_item_name := 'ASO0012';
p_sub_component_tbl (1).start_effective_date := SYSDATE;
p_sub_component_tbl (1).operation_sequence_number := 1;
p_sub_component_tbl (1).component_item_name := '060 1918';
p_sub_component_tbl (1).substitute_component_name := '10-016-0063';
p_sub_component_tbl (1).new_substitute_component_name := '10-016-0063';
p_sub_component_tbl (1).acd_type := 1;
p_sub_component_tbl (1).transaction_type := 'CREATE';
p_sub_component_tbl (1).row_identifier := NULL;
--fnd_client_info.set_org_context (204);

fnd_global.apps_initialize (1007941, 20567, 703);
DBMS_OUTPUT.put_line ('Before API');

eng_eco_pub.process_eco (p_api_version_number => 1.0,
p_init_msg_list => FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
p_bo_identifier => 'ECO',
p_eco_rec => p_eco_rec,
p_eco_revision_tbl => p_eco_revision_tbl,
p_change_line_tbl => l_change_line_tbl,
p_revised_item_tbl => p_revised_item_tbl,
p_rev_component_tbl => p_rev_component_tbl,
p_ref_designator_tbl => p_ref_designator_tbl,
p_sub_component_tbl => p_sub_component_tbl,
p_rev_operation_tbl => x_rev_operation_tbl,
p_rev_op_resource_tbl => x_rev_op_resource_tbl,
p_rev_sub_resource_tbl => x_rev_sub_resource_tbl,
x_eco_rec => x_eco_rec,
x_eco_revision_tbl => x_eco_revision_tbl,
x_change_line_tbl => x_change_line_tbl,
x_revised_item_tbl => x_revised_item_tbl,
x_rev_component_tbl => x_rev_component_tbl,
x_ref_designator_tbl => x_ref_designator_tbl,
x_sub_component_tbl => x_sub_component_tbl,
x_rev_operation_tbl => x_rev_operation_tbl,
x_rev_op_resource_tbl => x_rev_op_resource_tbl,
x_rev_sub_resource_tbl => x_rev_sub_resource_tbl,
p_debug => 'N',
p_output_dir => NULL,
p_debug_filename => 'ECO_BO_Debug.log'
);
DBMS_OUTPUT.put_line ('Return Status ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
error_handler.get_message_list (x_message_list);

FOR i IN 1 .. x_msg_count
LOOP
error_msg (i).MESSAGE_TEXT := x_message_list (i).MESSAGE_TEXT;
DBMS_OUTPUT.put_line (error_msg (i).MESSAGE_TEXT);
END LOOP;
END IF;

COMMIT;
END;
/

Base Tables and views involved:
ENG_ENGINEERING_CHANGES, ENG_CHANGE_ORDER_REVISIONS, ENG_REVISED_ITEMS, BOM_REFERENCE_DESIGNATORS, BOM_BILL_OF_MATERIALS, BOM_INVENTORY_COMPONENTS, BOM_SUBSTITUTE_COMPONENTS

Single Record script to load Customer Cross Reference

2009-08-16T05:04:00.000-07:00

INSERT INTO mtl_ci_interface
(customer_id,
customer_item_number,
commodity_code,
item_definition_level, process_flag, process_mode,
inactive_flag, transaction_type, lock_flag,
last_updated_by, created_by, last_update_date,
creation_date
)
VALUES (1290,
'Test_customer_item2',
'Finished Good',
1, 1, 1,
'2', 'CREATE', 'N',
1007941, 1007941, SYSDATE,
SYSDATE
);

commit;

---After inserting customer items into the interface table
---run the Import program Import Customer Items
---Navigation to run the program: Inventory--> Reports --> All.
---Parameters: Abort On Error=N, Delete Record =Y
---After running import program verify the base table MTL_CUSTOMER_ITEMS

INSERT INTO mtl_ci_xrefs_interface
(customer_id,
customer_item_number,
preference_number,
inventory_item,
master_organization_id,
item_definition_level, process_flag, process_mode,
inactive_flag, transaction_type, lock_flag,
last_updated_by, created_by, last_update_date,
creation_date
)
VALUES (1290,
'Test_customer_item2',
1,
'MC31324',
204,
1, 1, 1,
'2', 'CREATE', 'N',
1007941,1007941, SYSDATE,
SYSDATE
);

commit;

---After inserting customer items cross references into the interface table
---run the Import program, Import Customer Item Cross References
---Navigation to run the program: Inventory--> Reports --> All.
---Parameters: Abort On Error=N, Delete Record =Y
---After running import program verify the base table MTL_CUSTOMER_ITEM_XREFS

Single insert script for PO Quotations

2009-08-16T05:02:00.000-07:00

INSERT INTO po_headers_interface
(interface_header_id, batch_id, action, document_type_code,
document_subtype, vendor_id, vendor_site_id, org_id,
currency_code, rate, rate_type_code, agent_id,
bill_to_location_id, approval_status, approved_date, created_by,
creation_date, last_updated_by, last_update_date, comments,
attribute11, quote_warning_delay
)
VALUES (po_headers_interface_s.NEXTVAL, 5, 'ORIGINAL', 'QUOTATION',
'STANDARD', 8943, 4754, 204,
'USD', NULL, NULL, 2159,
204, 'APPROVED', SYSDATE, 1007941,
SYSDATE, 1007941, SYSDATE, 'test_po_for_status',
NULL, 10
);

COMMIT ;

INSERT INTO po_lines_interface
(interface_line_id, interface_header_id,
action, line_type, line_num, item_id, item,
item_description, uom_code,
quantity, unit_price, created_by, creation_date,
last_updated_by, last_update_date, line_attribute11
)
VALUES (po_lines_interface_s.NEXTVAL, po_headers_interface_s.CURRVAL,
'ORIGINAL', 'Goods', 1, 8063, '10-40W Oil',
'10-40W Car / Truck Motor Oil', 'QT',
2, 0.89, 1007941, SYSDATE,
1007941, SYSDATE, NULL
);

COMMIT ;

After inserting into interface tables, run the import program

Import Price Catalogs.
Navigation: Purchasing: Requests

Parameters:
1. Document Type = QUOTATION
2. Create or Update Items= NO
3. Create Sourcing Rules= NO
4. Approval Status= Approved
5. Batch Id=5

Base Tables
PO_HEADERS_ALL, PO_LINES_ALL

Mfg Cross References Single Record Script

2009-08-16T05:01:00.001-07:00

DECLARE
l_rowid VARCHAR2 (30);
BEGIN
fnd_global.apps_initialize (1007941, 20567, 703);
mtl_mfg_part_numbers_pkg.insert_row
(l_rowid,
69, --mfg_xref_load_rec.manufacturer_id,
'Test Part', --mfg_xref_load_rec.ls_mfg_part_number,
173, --mfg_xref_load_rec.inventory_item_id,
SYSDATE,
1007941, --l_user_id,
SYSDATE,
1007941, --l_user_id,
1007941, --l_user_id,
204, --mfg_xref_load_rec.organization_id,
'',
'',
NULL, --mfg_xref_load_rec.ls_authorization_status,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
COMMIT;
END;

Base table involved: MTL_MFG_PART_NUMBERS

PO Blanket Releases Single Insert script

2009-08-16T04:56:00.000-07:00

INSERT INTO po_requisitions_interface_all
(quantity, unit_price, authorization_status, unit_of_measure,
destination_organization_id, last_updated_by, last_update_date,
requisition_header_id, source_type_code, requisition_line_id,
need_by_date, org_id, amount, uom_code, document_type_code,
interface_source_code, destination_type_code, charge_account_id,
preparer_id, category_id, deliver_to_requestor_id,
deliver_to_location_id, item_id, batch_id, line_type_id,
currency_code, creation_date, requisition_type,
suggested_buyer_id, ---suggested_vendor_id,
autosource_doc_header_id,
autosource_doc_line_num, req_number_segment1,
req_dist_sequence_id
)
VALUES (10, 20, 'APPROVED', 'Each',
207, 1111, SYSDATE,
123492, 'VENDOR', 101198,
SYSDATE + 1, 204, NULL, 'Ea', 'RELEASE',
'test', 'EXPENSE', 13401,
15516, 1, 15516,
207, 10928, 100, 1,
'USD', SYSDATE, 'PURCHASE',
15516, 34333,
1, 'Req-106',
1
);

After inserting into interface tables, run the following import program with the parameters

Requisition Import.
Navigation: Purchasing: Requests

Parameters:
1. Import Source = test
(Select given interface_source_code value from LOV )

2. Import Batch ID = 100
(Give batch_id which u gave for the header)

3. Group By = Item+ (Default value)

4. Last Requisition Number = NULL

5. Multiple Distributions = No (Select YES only if u have multiple lines in distribution table)

6. Initiate Approval after ReqImport = YES (Select YES only if u want to auto approve the requisition)

Single Record Script to link 'BOM Resources' to 'BOM Departments'

2009-08-16T04:45:00.000-07:00

DECLARE
l_rowid VARCHAR2 (2000);
BEGIN
bom_department_resources_pkg.insert_row (x_rowid => l_rowid,
x_department_id => 21773,
x_resource_id => 8270,
x_last_update_date => SYSDATE,
x_last_updated_by => 1007911,
x_creation_date => SYSDATE,
x_created_by => 1007911,
x_last_update_login => 3156870,
x_share_capacity_flag => 2,
x_share_from_dept_id => NULL,
x_capacity_units => 1,
x_resource_group_name => NULL,
x_available_24_hours_flag => 1,
x_ctp_flag => 2,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_exception_set_name => NULL,
x_atp_rule_id => NULL,
x_utilization => NULL,
x_efficiency => NULL,
x_schedule_to_instance => 2
);
COMMIT;
END;

Single Record Script to load 'BOM Resources'

2009-08-16T04:44:00.000-07:00

DECLARE
l_rowid VARCHAR2 (2000);
l_resource_id NUMBER;
BEGIN
bom_resources_pkg.insert_row (x_rowid => l_rowid,
x_resource_id => l_resource_id,
x_resource_code => 'XXTEST123',
x_organization_id => 204,
x_last_update_date => SYSDATE,
x_last_updated_by => 1007911,
x_creation_date => SYSDATE,
x_created_by => 1007911,
x_last_update_login => 3156870,
x_description => NULL,
x_disable_date => NULL,
x_cost_element_id => 3,
x_purchase_item_id => 17817,
x_cost_code_type => 3,
x_functional_currency_flag => 2,
x_unit_of_measure => 'EA',
x_default_activity_id => 4,
x_resource_type => 2,
x_autocharge_type => 1,
x_standard_rate_flag => 2,
x_default_basis_type => 1,
x_absorption_account => 13735,
x_allow_costs_flag => 1,
x_rate_variance_account => NULL,
x_expenditure_type => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_request_id => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_program_update_date => NULL,
x_batchable => 1,
x_min_batch_capacity => NULL,
x_max_batch_capacity => NULL,
x_batch_capacity_uom => NULL,
x_batch_window => NULL,
x_batch_window_uom => NULL,
x_competence_id => NULL,
x_rating_level_id => NULL,
x_qualification_type_id => NULL,
x_billable_item_id => NULL,
x_supply_subinventory => NULL,
x_supply_locator_id => NULL
);
COMMIT;
END;

Single Record Script to load 'BOM Department'

2009-08-16T04:41:00.000-07:00

DECLARE
l_rowid VARCHAR2 (2000);
l_department_id NUMBER;
BEGIN
bom_departments_pkg.insert_row (x_rowid => l_rowid,
x_department_id => l_department_id,
x_department_code => 'XXTEST',
x_organization_id => 204,
x_last_update_date => SYSDATE,
x_last_updated_by => 1007911,
x_creation_date => SYSDATE,
x_created_by => 1007911,
x_last_update_login => 3156870,
x_description => 'Test Department',
x_disable_date => NULL,
x_department_class_code => NULL,
x_pa_expenditure_org_id => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_location_id => NULL,
x_scrap_account => NULL,
x_est_absorption_account => NULL,
x_maint_cost_category => NULL
);
DBMS_OUTPUT.put_line (l_department_id);
COMMIT;
END;

Query to find the workflow overrider address in test instances

2009-07-13T03:08:00.000-07:00

SELECT fscpv.parameter_value override_email_address
FROM fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE 1 = 1
AND fscpt.display_name = 'Test Address'
AND fscpt.parameter_id = fscpv.parameter_id;

Query to fetch Error workflow details for 'HRSSA' workflow

2009-07-08T09:41:00.001-07:00

The below query will show you the error workflow for a particular time period for the item type 'HRSSA'. You can also fetch the error for other workflows too, for that instead of 'HRSSA' you need to give the item type for which you want to fetch the data.

SELECT ias.begin_date, ias.item_key, ac.NAME activity,
ias.activity_result_code RESULT, ias.error_name error_name,
ias.error_message error_message
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities ac,
wf_activities ap,
wf_items i
WHERE ias.item_type = 'HRSSA'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'HRSSA'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < NVL (ac.end_date, i.begin_date + 1)
and trunc(i.begin_date) between to_date(:begin_date) and trunc(:end_date)
ORDER BY ias.begin_date DESC

Query to link a Responsibility to a Set of Books/Operating Unit in Oracle

2009-06-10T21:41:00.001-07:00

--- Link to Set of Books
SELECT fr.responsibility_name, fpov.profile_option_value set_of_books_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr
WHERE fpo.user_profile_option_name = 'GL Set of Books Name'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id

--- Link to Operating Unit
SELECT fr.responsibility_name, fpov.profile_option_value orgid, NAME org_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr,
hr_operating_units hou
WHERE UPPER (fpo.user_profile_option_name) LIKE UPPER ('MO%OPERATIN%')
AND profile_option_name = 'ORG_ID'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id
AND hou.organization_id = fpov.profile_option_value

Query that shows all the repsonsibilities and what functions are attached to these responsibilities.

2009-06-09T04:51:00.001-07:00

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;

How to close/respond to a FYI notification from PL/SQL

2009-06-07T23:37:00.000-07:00

Closing/Responding to a FYI notification is perty straight forward. You need to just run the following API:

begin
wf_notification.CLOSE (nid => --Notification ID--,
responder => --Responder Name--
);
commit;
end;

Note: The Responder Name should be the same as the 'User Name' which the user uses to login the Oracle applications.

How to preserve leading zeros in a CSV file using Oracle

2009-05-13T22:33:00.001-07:00

Some time back i was working on alerts which will extract the data from Oracle and send alert extracts to the users via email as attachments in 'CSV' format. But when i received the notifications i noticed that the leading zeros in the segment values of the code combinations were truncated.

After some research i found that when ever the Excel opens up a CSV file it defaults all cells to "General" formatting and removes the leading zeros on cell values thus converting them from strings to numbers.

The simple solution is to SELECT the value such that Excel will see it as a string and not a number. So we prefixed an equals sign and double quote on to the value and post fix a double quote. So

SELECT xx_column
FROM xx_table

becomes

SELECT '="'||xx_column||'"'
FROM xx_table

PL/Sql Script to assign responsibilities to Oracle Users

2009-05-04T05:12:00.000-07:00

DECLARE
l_user_id NUMBER;

CURSOR cur_rec
IS
SELECT *
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER ('XXXXX');
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'XXXX';

FOR rec_cur IN cur_rec
LOOP
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => rec_cur.responsibility_id,
responsibility_application_id => rec_cur.application_id,
security_group_id => 0,
start_date => SYSDATE - 1,
end_date => NULL,
description => NULL
);
COMMIT;
END LOOP;
END;

Oracle Expense Report (Approval Limits)

2009-04-20T03:01:00.000-07:00

Expense reports will fail is the manager does not have approval authority for a particular cost center, or an appropriate approval level for the cost center of the employee submitting the expense report. The query to show the employee approval limits are as follows:

SELECT b.full_name, a.cost_center, a.org_id, c.NAME org_name, a.signing_limit
FROM ap_web_signing_limits_all a,
per_all_people_f b,
hr_organization_units c
WHERE a.employee_id = b.person_id
AND a.org_id = c.organization_id
AND b.effective_start_date = (SELECT MAX (effective_start_date)
FROM apps.per_all_people_f
WHERE person_id = b.person_id)
AND a.document_type = 'APEXP'

The navigation to check/update the approval limits for an expense report of an employee is as follows:

Payables Manager -> Employees -> Signing Limits

GL Setups

2009-04-20T02:53:00.000-07:00

Defining Types of Calendar
Nav: Setup -> Financials -> Calendar -> Type

Accounting the Calendar
Nav: Setup -> Financials -> Calendar -> Accounting

Defining Currency
Nav: Setup -> Financials -> Currency -> Define

Define rate
Nav: Setup -> Financials -> Currency -> Rate -> Daily

Create a value set
Nav: Setup -> Financials -> Flexfields -> Validation -> Sets

Create a structure:
Nav: Setup -> Financials -> Flexfields -> key -> Segment

Define values:
Nav: Setup -> Financials -> Flexfeild -> Key -> Values

Creating a set of Books:
Nav: Setup -> Financials -> Books -> Define

Query to extract Employee Contact Information

2009-04-20T02:49:00.000-07:00

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Script to delete the concurrent program from Oracle

2009-04-20T02:46:00.000-07:00

Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;

Person Types in Oracle HRMS

2009-04-20T02:35:00.000-07:00

There are two tables (per_all_people_f,per_person_type_usages_f) where you can find the person_types for an employee. Developers usually gets misguided to which person_type_id we need to consider.

The answer is we should always need to use the table per_person_type_usages_f to retrieve the correct person_id. Though the person_type_id in the two tables appear to be same but they are different and that can perty much mislead us. Here are the reasons below:

1. The per_all_people_f.person_type_id holds the default user_person_type for a given system_person_type. It is not maintained to reflect the true user person type. For each system_person_type in each business group, there can only be one USER_PERSON_TYPE with a default_flag = 'Y'. However, an employee can have a user_person_type = Contractor, What will show in person_type_id of the two tables with a CONTRACTOR user_person_type will be as follows:

PER_ALL_PEOPLE_F : 2
PER_PERSON_TYPE_USAGES_F: 4

where 'Contractor' is the subgroup of the person type 'Employee' and 2 is the person_type_id for the employee and 4 is the person_type_id of contractor..

So, only the person_type_usages_f table will give us a true picture of user_person_type and should be used in ALL HR transactions. The per_all_people_f.person_type_id is for use by non HR applications, such as Purchasing which doesn't care about the various user flavors but the system_person_type of 'EMP'. When HR Development made the change to use per_person_type_usages_f table, they retain the person_type_id column in per_all_people_f table for compatibility with other applications. This scheme will enable other non-HR applications still work.

NOTE: When accessing per_person_type_usages_f table for a given person at a given time, there can be multiple rows returned because per_person_type_usages_f table will have a row for each person_type. When an employee is an 'EMP_APL', there'll be two rows returned: one for a system_person_type = 'EMP'with user_person_type = whatever, and another row for system_person_type = 'APL' and user_person_type = whatever. Any SQL statement or cursor needs to handle the multiple rows returned condition. Otherwise, one will get an error something like "Exact fetch returns more than 1 row".

Workflow Error

2009-04-19T23:16:00.000-07:00

Error Name: WFNTF_ROLE
Error Message 3205: 'FND_RESP...' is not a valid role or user name.

Resolution: The reason for the error is due to the users not been assigned to the responsibility. The workflow notification was looking for all the users that are been assigned to the responsibility, but as there were no users been assigned the workflow was going in error status.

The query to find the roles that are been assigned to the users or not is as follows:
SELECT *
FROM wf_user_roles
WHERE UPPER (role_name) LIKE 'FND_RESP...'

SELECT c.user_name, c.start_date, c.end_date, b.responsibility_name
FROM fnd_user_resp_groups_all a,
fnd_responsibility_tl b,
fnd_user c,
fnd_application d
WHERE a.user_id = c.user_id
AND a.responsibility_id = b.responsibility_id
AND b.application_id = d.application_id
AND b.responsibility_id = :responsibility_id

For more information on 'Workflow Directory Service Views' please visit:
Oracle Workflow Administrator's -> Workflow Directory Service Views

Query to retrieve active Alert information

2009-04-14T05:44:00.001-07:00

SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code
AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'

Expense report number postfixed with "-1"

2009-03-25T23:45:00.001-07:00

When an user submitted an expense report, his expense report number was discarded and a new expense report was generated with "-1" postfixed. He came up with the following questions.

Question: Why does my original expense report have a "-1" after it?
Answer: The expense report was short-paid by Accounts Payable – probably for lack of a receipt. Please contact your AP department and submit receipts where required.

Question: Why do I have a new expense report number?
Answer: The original expense report included two or more items. At least one item was short-paid by Accounts Payable. The original expense report was partially paid. The short-paid amount was given a document ID with a “-1” suffix. A new expense report was created to capture the amount of the short payment. Ultimately, there will be two payments which are associated with two different expense report numbers.

Workflow Issue

2009-03-22T23:39:00.000-07:00

Some time back we had a requirement to resolve the timecard workflows that were in error status at PROD. After some analysis we found that someone has recompiled the older version of the workflow at PROD and as the underlying workflow package was referring to some attributes which were missing in the older workflow the process was going in error status. We had a difficult task ahead..

As it was a weekend there were many timecards that were been submitted and went in error status and all these timecards have to be approved in another couple of days by there respective approving authority failing which these timecards would not be considered for accounting.. Initially to resolve the issue the only way we thought would be possible is to recompile the original workflow, cancel all the timecards manually at PROD and inform all the users with error timecards to resubmit again. Informing to all the users of this count was a hectic task.

We did some further investigations in the workflow developer’s guide and found that we can actually abort the existing process and resubmit the timecards with same item key without the need cancel and inform the users to resubmit the timecards. We tried this process initially at DEV instance and found that it really worked as we expected.

In short the process what we followed is as follows:
1. Recompiled the original workflow.
2. Created a temp table to store the important attribute values for the error workflows.
3. Performed the Abort, Purge, Create and Start process using standard oracle workflow packages. (Before Starting the new process we set the workflow attributes from the temp table.). The below is the code which we used to perform all the process mentioned in the step 3:

/************************************************************
1. Below code is used to retrieve the important attribute values from the workflow table for the error timecards and store them in a temp table.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT DISTINCT item_key
FROM wf_item_activity_statuses
WHERE activity_status = 'ERROR'
AND activity_result_code = '#EXCEPTION'
AND item_type = 'PATCARD'
AND error_stack LIKE '%CHECKPERSONCOSTINGS%';
BEGIN
FOR c1_rec IN c1
LOOP
INSERT INTO xx_wf_temp
(SELECT 'N', c1_rec.item_key,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'APPLICATION_ID'
AND item_type = 'PATCARD') application_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'APPROVER_COUNT'
AND item_type = 'PATCARD') approver_count,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'COMMENT_DOC'
AND item_type = 'PATCARD') comment_doc,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_NAME'
AND item_type = 'PATCARD') employee_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EXPENDITURE_ID'
AND item_type = 'PATCARD') expenditure_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'LINE_TABLE'
AND item_type = 'PATCARD') line_table,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_NAME'
AND item_type = 'PATCARD') manager_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'ORG_ID'
AND item_type = 'PATCARD') org_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_NAME'
AND item_type = 'PATCARD') preparer_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'RESPONSIBILITY_ID'
AND item_type = 'PATCARD') responsibility_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TIMECARD_DETAILS'
AND item_type = 'PATCARD') timecard_details,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TIMECARD_NUMBER'
AND item_type = 'PATCARD') timecard_number,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TOTAL_HOURS'
AND item_type = 'PATCARD') total_hours,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'USER_ID'
AND item_type = 'PATCARD') user_id,
(SELECT date_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'WEEK_END_DATE'
AND item_type = 'PATCARD') week_end_date,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_ID'
AND item_type = 'PATCARD') employee_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_ID'
AND item_type = 'PATCARD') manager_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_ID'
AND item_type = 'PATCARD') preparer_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_DISP_NAME'
AND item_type = 'PATCARD') employee_disp_name,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_DISP_NAME'
AND item_type = 'PATCARD') manager_disp_name,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_DISP_NAME'
AND item_type = 'PATCARD') preparer_disp_name
FROM DUAL);

COMMIT;
END LOOP;
END;

/************************************************************
2. Basically to delete the workflows in open status, we need to abort workflow process first so they will be eligible for purge process. The below code is used to perform the abort process on the workflow. Once the code is executed the workflow status would be set to Completed.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'N'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.abortprocess ('PATCARD', --Item Type
cur_rec.item_key, -- Item Key
'PA_TCARD_APPROVAL_PROCESS', -- Parent Process
'CANCEL' -- Process
);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'A'
WHERE status_stg = 'N' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
3. The below code is used to perform the purge process for the error workflow timecards from the oracle workflow base tables.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'A'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_purge.items ('PATCARD', cur_rec.item_key, SYSDATE, TRUE, TRUE);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'P'
WHERE status_stg = 'A' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
4. The below code is used to create the timecard again in Oracle with the same item key and with the same version of the workflow existing in Oracle now..
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'P'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.createprocess ('PATCARD',
cur_rec.item_key,
'PA_TCARD_APPROVAL_PROCESS',
NULL,
cur_rec.preparer_name
);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'C'
WHERE status_stg = 'P' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
5. In the below code we are setting the attributes from the temp table in which we have stored all the values at Step 1 and would be starting the workflow process.
/************************************************************

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'C'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'COMMENT_DOC',
avalue => cur_rec.comment_doc
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_NAME',
avalue => cur_rec.employee_name
);
wf_engine.setitemattrdocument (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'LINE_TABLE',
documentid => cur_rec.line_table
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_NAME',
avalue => cur_rec.manager_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_NAME',
avalue => cur_rec.preparer_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TIMECARD_DETAILS',
avalue => cur_rec.timecard_details
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TIMECARD_NUMBER',
avalue => cur_rec.timecard_number
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_DISP_NAME',
avalue => cur_rec.employee_disp_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_DISP_NAME',
avalue => cur_rec.manager_disp_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_DISP_NAME',
avalue => cur_rec.preparer_disp_name
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'APPLICATION_ID',
avalue => cur_rec.application_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'APPROVER_COUNT',
avalue => cur_rec.approver_count
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EXPENDITURE_ID',
avalue => cur_rec.expenditure_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'ORG_ID',
avalue => cur_rec.org_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'RESPONSIBILITY_ID',
avalue => cur_rec.responsibility_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TOTAL_HOURS',
avalue => cur_rec.total_hours
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'USER_ID',
avalue => cur_rec.user_id
);
wf_engine.setitemattrdate (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'WEEK_END_DATE',
avalue => cur_rec.week_end_date
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_ID',
avalue => cur_rec.employee_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_ID',
avalue => cur_rec.manager_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_ID',
avalue => cur_rec.preparer_id
);
wf_engine.startprocess ('PATCARD', cur_rec.item_key);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'S'
WHERE status_stg = 'C' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

Check out Jaago re! - One Billion Votes

2009-03-05T00:34:00.001-08:00

Hi,

I want you to take a look at: Jaago re! - One Billion Votes

Understanding and Using API User Hooks

2009-03-04T21:48:00.000-08:00

You can get all the information of API User Hooks in the metalink with the Doc ID: 73170.1

Retrieve the correct person type in HRMS

2009-03-04T21:32:00.000-08:00

Use the below standard oracle function to retrieve the correct person type for an employee.

SELECT hr_person_type_usage_info.get_user_person_type (SYSDATE, &person_id)
FROM DUAL

If you want to retrieve the latest person type then perform the instr and substr on the output of the above query.

Simple Script to load the HR Locations using API

2009-03-03T01:43:00.000-08:00

DECLARE
l_location_id NUMBER;
l_object_version_number NUMBER;
BEGIN
apps.hr_location_api.create_location
(p_effective_date => SYSDATE,
p_location_code => 'XXXXX',
p_description => 'XXX Location',
p_address_line_1 => 'XXX Rd',
p_country => 'US',
p_postal_code => '12345',
p_telephone_number_1 => '1234567890',
p_town_or_city => 'XXX',
p_business_group_id => '0',
p_style => 'US_GLB',
p_location_id => l_location_id,
p_object_version_number => l_object_version_number
);
COMMIT;
END;

11i Oracle General Ledger Technical Document

2009-02-18T22:55:00.000-08:00

Sets of Books and Charts of Accounts govern Oracle’s General Ledger. The set of books keeps what currency, chart of accounts structure and calendar that the book will use. The main tables for this are
GL_SETS_OF_BOOKS
FND_ID_FLEX_STRUCTURES

JOURNAL ENTRIES
Journal Entries can be added several ways. By upload from and external source, upload through ADI, transfer from the various modules, or manually keyed into the application from the form.

Manual Entry - The responsibility that the user is in for the entry determines the chart of accounts structure and set of books. The user will enter a Batch name and period (which will default to the current open period). The user will then enter a header record with a description, and a journal category chosen from the list of valid journal categories. The user may also override the currency defaulted from the set of books. This will cause the entry to be booked to that currency but it will translate to the functional currency of the set of books. To translate, it will use the data from the GL_DAILY_RATES table. The user will last enter the lines for the journal. The main tables needed for this are:
GL_CODE_COMBINATIONS
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_CATEGORIES
GL_DAILY_RATES (If needed)

Imported Journals - Journals can also come into the general ledger from the other financial modules or by an integration tool (either custom built of ADI). The path for all of these types of entries is the same. Data flows through the GL_INTERFACE where is groups the source into batches, headers and lines based on where the data originates. When the Journal Import process is submitted either manually as a concurrent process or by a transfer process from the other modules. A journal batch, header and lines are created in the base tables exactly like a fully manual journal entry. The processes that are submitted from the other modules to transfer data into the GL are
Payables Import to General Ledger
Create Journal Entries (Fixed Assets)
PRC: Interface Revenue to General Ledger (Projects)
Interface Receivables to General Ledger

Main GL tables
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_SOURCES
GL_JE_CATEGORIES
GL_SETS_OF_BOOKS
GL_CODE_COMBINATIONS
GL_PERIODS
GL_CURRENCIES

JOURNAL POSTING
When the user has completed a balance journal entry or the import process completes with no errors, the entries can be posted. The posting is a concurrent process submitted by the post form. When the entry is posted, the data is copied from the base tables into a temporary table called GL_POSTING_INTERIM. Here the program will use the information to group all lines with the same Currency, Code Combination, Set of Books, and Period to be used to update GL_BALANCES. The balances table will be updated with the debits and credits. The batches, headers and lines tables will also be updated for posting status. When the post is complete, the posting interim table is cleared.

Main tables
GL_POSTING_INTERIM
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_BALANCES

BALANCE TRANSLATIONS
Balances that require translation into one currency for reporting and/or consolidation purposes must run the translation process. This process requires that there be a period rate entered for set of books functional currency to the translation currency, i.e. EUR to USD. This rate is entered manually using the period rates form for each set of books. The user will then submit a process using the translation form to translate the balances to the new currency. The process will perform the following query to determine the balances available for translation.

SELECT DISTINCT CC.SEGMENT1
FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL
WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND GBAL.ACTUAL_FLAG = (user entered)
AND GBAL.SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND GBAL.PERIOD_NAME = (user entered)
AND GBAL.CURRENCY_CODE = (functional currency for the set of books)
AND GBAL.TEMPLATE_ID IS NULL
AND CC.DETAIL_POSTING_ALLOWED_FLAG = 'Y'

SELECT EARLIEST_EVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR
FROM GL_TRANSLATION_TRACKING
WHERE BAL_SEG_VALUE = :bal_seg_value
AND TARGET_CURRENCY = (user entered)
AND SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND AVERAGE_TRANSLATION_FLAG = 'N'
AND ACTUAL_FLAG = 'A'

The process will also retrieve the historical rates for equity accounts from the GL_HISTORICAL_RATES table for the prior period and insert new records based on a profile option set for the module (Year to Date or Period To Date).

The process will then insert into a temporary table all of the balances to be translated called the GL_TRANSLATION_INTERIM. From here the process will translate the balances and insert those records into the GL_XLATE_POSTING_INTERIM table. Last the data will be inserted into or updated in the GL_BALANCES table.

Main tables
GL_PERIOD_RATES
GL_HISTORICAL_RATES
GL_TRANSLATION_TRACKING
GL_TRANSALATION_INTERIM
GL_XLATE_POSTING_INTERIM
GL_BALANCES

11i Oracle Fixed Asset Technical Document

2009-02-18T22:36:00.000-08:00

In Oracle Fixed Assets it is important to remember that most of the processes are c programs. All current asset records from the tables have an NULL DATE_INEFFECTIVE and/or NULL TRANSACTION_HEADER_ID_OUT. And the application is governed by books which point to a GL Set Of Books.

FA ADDITIONS
Additions can come into 2 ways. Directly adding into the system manually or by entry through the Mass Additions tables.

Manual Entry - User enters data through the form and the information is inserted into the following tables
FA_ADDITIONS
FA_BOOKS
FA_ASSET_HISTORY
FA_DISTRIBUTION_HISTORY
FA_TRANSACTION_HEADERS
FA_DEPRN_SUMMARY
FA_DEPRN_DETAIL
FA_TRANSFER_DETAILS

Mass Additions come into the Oracle Assets by a spreadsheet entry through ADI (Applications Desktop Integrator) or through a concurrent process called Create Mass Additions (This can be from Payables or from Projects depending on the nature of the business)

Data is inserted into the following tables
FA_MASS_ADDITIONS (asset details)
FA_MASSADD_DISTRIBUTIONS (invoice and invoice distribution details)

The data is reviewed and updated with Category, Location and Depreciation Expense account by the user. Upon completion, a concurrent process is submitted called Post Mass Additions. Any record marked as POST will be picked up and added to the tables listed above in the Manual Entry section.

FA GENERATE ACCOUNTS

This is a concurrent program that can be submitted both stand-alone or it will submit as part of the run depreciation process.

This process when submitted looks at the FA_DISTRIBUTION_ACCOUNTS table for every active asset distribution in the book that the process is running. If there is no account listed, this process creates a new account in this table.

DEPRECIATION - BEHIND THE SCENES
When you run depreciation, Oracle Assets processes each asset according to the transactions that you have performed on the asset since the last depreciation.

FADEPR uses the following tables:
FA_DEPRN_DETAIL
For each depreciable asset, Oracle Assets inserts one row per distribution line that was active at any time during the current period.

BOOK_TYPE_CODE
ASSET_ID
PERIOD_COUNTER
DISTRIBUTION_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
ADDITION_COST_TO_CLEAR
COST
DEPRN_ADJUSTMENT_AMOUNT
DEPRN_EXPENSE_JE_LINE_NUM
DEPRN_RESERVE_JE_LINE_NUM
REVAL_AMORT_JE_LINE_NUM
REVAL_RESERVE_JE_LINE_NUM
JE_HEADER_ID
REVAL_AMORTIZATION
REVAL_DEPRN_EXPENSE
REVAL_RESERVE YTD

FA_DEPRN_SUMMARY
Oracle Assets inserts one row per depreciable asset.

BOOK_TYPE_CODE
ASSET_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
DEPRN_SOURCE_CODE
ADJUSTED_COST
BONUS_RATE
LTD_PRODUCTION
PERIOD_COUNTER

FA_BOOK_CONTROLS
If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'C' (Completed) or 'E' (Error), the form submits the concurrent request and sets the DEPRN_STATUS = 'S' (Submitted). Oracle Assets now locks this row to prevent you from entering any transaction when depreciation is running. If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'R' (Running) or 'S' (Submitted), then Oracle Assets displays the errors message "CHECK_BOOK_STATUS" or "Failed to obtain lock on FA_BOOK_CONTROLS row for book."

Oracle Assets also checks if the depreciation request is for the current open period. If the LAST_PERIOD_COUNTER in the FA_BOOK_CONTROLS is that of the last period, the program proceeds. Oracle Assets updates the LAST_PERIOD_COUNTER, LAST_DEPRN_RUN_DATE, DEPRN_REQUEST_ID, DEPRN_STATUS, and CURRENT_FISCAL_YEAR for the book.

FA_DEPRN_PERIODS
Oracle Assets closes the row corresponding to the current period (by entering a PERIOD_CLOSE_DATE) and inserts a new row for the book and the new period.

FA_FISCAL_YEARS
If the new fiscal year has not been created, Oracle Assets automatically extends the fiscal year definition.

FA_ADJUSTMENTS
Retroactive transactions and expensed depreciation adjustments.

TRANSACTION_HEADER_ID
SOURCE_TYPE_CODE
ADJUSTMENT_TYPE
CODE_COMBINATION_ID
BOOK_TYPE_CODE
ASSET_ID
ADJUSTMENT_AMOUNT
DISTRIBUTION_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
ANNUALIZED_ADJUSTMENT
JE_HEADER_ID
JE_LINE_NUM
PERIOD_COUNTER
ADJUSTED PERIOD_COUNTER
ASSET_INVOICE_ID

FA_BOOKS
Look up information needed for depreciation calculation and check the following:
PERIOD_FULLY_RESERVED = NULL
PERIOD_FULLY_RETIRED = NULL
DEPRECIATE_FLAG = YES
ADJUSTMENT_REQUIRED_STATUS is not NONE or TFR (Prior Period Transfer)
DATE_INEFFECTIVE = NULL

BOOK_TYPE_CODE
ASSET_ID
DATE_PLACED_IN_SERVICE
DATE_EFFECTIVE
DEPRN_START_DATE
DEPRN_METHOD
LIFE_IN_MONTHS
RATE_ADJUSTMENT_FACTOR
ADJUSTED_COST
COST
ORIGINAL_COST
SALVAGE_VALUE
PRORATE_CONVENTION
PRORATE_DATE
DATE_INEFFECTIVE
TRANSACTION_HEADER_ID_IN
TRANSACTION_HEADER_ID_OUT
ITC_AMOUNT_ID
ITC_AMOUNT
RETIREMENT_ID
TAX_REQUEST_ID
ITC_BASIS BASIC_RATE
ADJUSTED_RATE BONUS_RULE
CEILING_NAME
RECOVERABLE_COST
LAST_UPDATE_LOGIN
ADJUSTED_CAPACITY
FULLY_RSVD_REVALS_COUNTER
PERIOD_COUNTER_CAPITALIZED
PERIOD_COUNTER_FULLY_RESERVED
PERIOD_COUNTER_FULLY_RETIRED PRODUCTION_CAPACITY REVAL_AMORTIZATION_BASIS
REVAL_CEILING UNIT_OF_MEASURE
PERIOD_COUNTER_LIFE_COMPLETE

FA_DISTRIBUTION_HISTORY
Look up active distributions.

FA_CEILINGS
Look up ceiling information.

FA_CALENDAR_PERIODS
Look up period information.

FA_CONVENTIONS
Look up prorate convention information.

THE BOTTOM LINE
~~~~~~~~~~~~~~~
Historical depreciation calculations (DEPRN_EXPENSE, ACCUMULATED_DEPRN) can be found in FA_DEPRN_DETAIL and FA_DEPRN_SUMMARY. The main difference between these tables is that FA_DEPRN_DETAIL shows depreciation information for each distribution line (i.e. each active row in FA_DISTRIBUTION_HISTORY), whereas FA_DEPRN_SUMMARY shows summary depreciation information for an asset.

NOTE: Because referential integrity is not maintained at the RDBMS level, the depreciation tables do not reference FA_DISTRIBUTION_HISTORY or FA_ADJUSTMENTS.

COMMON DEPRECIATION ERRORS
~~~~~~~~~~~~~~~~~~~~~~~~~~
APP-48260 Module CHECK_BOOK_STATUS ended with error.
Cause: The MASS_REQUEST_ID for this book is NOT NULL.
Resolution: Check to verify that all mass requests for this book have
completed normal. NULL the MASS_REQUEST_ID in the FA_BOOK_CONTROLS table.

APP-00988 ORA-1403 in fadccs.
Cause: fadccs failed due to ORA-01403 no data found.

APP-47670 Unable to validate depreciation periods.
Cause: You ran depreciation for the first time in your book. The problem
is that there is a missing row in FA_DEPRN_PERIODS. FADEPR expects a row in
FA_DEPRN_PERIODS for one period less than the active period.
Resolution: Insert the missing row in FA_DEPRN_PERIODS table. Contact
Oracle Support Services for the datafix script.

APP-47984 in fazccp and ORA-1403 in fazgtcp.
Cause: The depreciation calendar needs to go as far back as the oldest DPIS,
or if you have changed the calendar, there may be gaps or period overlaps.
Resolution: Correct the calendar.

APP-00988 ORACLE error 1403 in fadubc
APP-47649 Error: Unable to set depreciation status in FA_BOOK_CONTROLS table
APP-47640 Error: Unable to update FA_BOOK_CONTROLS table
Cause: You ran depreciation for a book with no assets and you are in the
last period of the fiscal year.
Resolution: You need to apply patch for BUG 605315 or higher.

APP-00988 Oracle error 1555 in faddep
Cause: faddep failed due to ORA-01555: snapshot too old (rollback segment
too small).
Resolution: Increase the rollback segment size.

APP-47191 and ORA-1403 in fazgtbc
Cause: Depreciation is submitted with number of parallel requests set to
more than 1 (FA:Number of Parallel Requests) and the BOOK_TYPE_CODE has a
space in between 2 words. Example: US CORPORATE
Resolution: This is BUG 456936. Apply Patch 605315 or higher. As a
workaround, you need to submit depreciation in single mode.

APP-00988 ORACLE error 1 in faenicp
Cause: faenicp failed due to ORA-00001:unique constraint
(FA.FA_CALENDAR_PERIODS_U3) violated.
Resolution:

1) Make sure that the version of faeofy.lpc in FADEPR is version 70.15
or Higher.

2) Check the calendar periods for gaps or overlap.

3) Check that the Fiscal Start and End Dates match the Start and End Dates
of the first and last periods of the fiscal year respectively.

4) Make sure that there are no gaps or overlap between Fiscal Year Start
and End Dates.

5) Be sure that all of the periods for a Fiscal Year have been created.

ORA-1 in fadaid/fadais
Common causes:

- Updating DEPRN_STATUS in FA_BOOK_CONTROLS to C (Completed).
- Partially committed or incomplete transfers, adjustments, or partial
retirements.
- Transfers that happened after depreciation has errored in a book.
- Orphaned adjustment records.
- Orphaned distributions.

Resolution: Run the provided ORA-1 diagnostic scripts. Contact Oracle
Support Services for any required datafix scripts.

ORA-1 DIAGNOSTIC SCRIPTS
========================
Script to check for adjustment rows with invalid distribution_id:

select aj.asset_id, aj.distribution_id
from fa.fa_distribution_history dh, fa.fa_adjustments aj
where aj.period_counter_created = &PCounter
and aj.book_type_code = '&BOOK'
and NOT exists (select dh.asset_id
from FA.fa_distribution_history dh
where dh.distribution_id = aj.distribution_id
and dh.asset_id = aj.asset_id);

Another script to check for adjustment rows with invalid distribution_id:

select aj.asset_id
from fa.fa_transaction_headers th,
fa.fa_distribution_history dh,
fa.fa_adjustments aj,
fa.fa_deprn_periods dp
where dp.book_type_code = '&BOOK'
and dp.period_close_date is null
and dp.period_counter = aj.period_counter_created
and dp.book_type_code = aj.book_type_code
and aj.transaction_header_id = th.transaction_header_id
and th.transaction_type_code = 'TRANSFER'
and aj.distribution_id = dh.distribution_id(+)
and dh.code_combination_id is null;

The best script to check for adjustment rows with invalid distribution_id:

select distinct aj.asset_id
from fa.fa_adjustments aj, fa.fa_deprn_detail dd
where aj.book_type_code = &book
and aj.distribution_id = dd.distribution_id(+)
and aj.period_counter_created = dd.period_counter(+)
and aj.book_type_code = dd.book_type_code(+)
and dd.deprn_amount is null
and exists (select 'fine' from fa.fa_deprn_detail dd2
where dd2.asset_id = aj.asset_id
and dd2.book_type_code = aj.book_type_code
and dd2.period_counter = aj.period_counter_created);

Script to check for invalid rows in FA_DISTRIBUTION_HISTORY:

select dh.asset_id
from fa.fa_distribution_history dh1,
fa.fa_distribution_history dh
where dh.transaction_header_id_out is not null
and dh.transaction_header_id_out = dh1.transaction_header_id_in(+)
and dh1.code_combination_id is null;

GENERAL INFORMATION
===================

DEPRECIATION CALENDAR
The depreciation calendar determines the number of accounting periods in
your fiscal year.

PRORATE CALENDAR
The prorate calendar determines what rate Oracle Assets uses to calculate annual depreciation by mapping each date to a prorate period, which corresponds to a set of rates in the rate table.

PERIOD CLOSE
Oracle Assets automatically closes the book's current period and opens the next when you run the depreciation program. You cannot have more than one open period for a given depreciation book.

YEAR-END PROCESSING
You can close the year independently in each depreciation book. The depreciation program automatically resets year-to-date amounts on a book the first time the depreciation program is run on that book in a fiscal year. Oracle Assets automatically creates the depreciation and prorate periods for your new year when you run depreciation for the last period of the previous fiscal year.

SUSPEND DEPRECIATION
You can suspend depreciation by un-checking Depreciate flag in the Books form. If you suspend depreciation of an asset when you add the asset, Oracle Assets expenses the missed depreciation in the period you start depreciating the asset.

For table and calculated methods, Oracle Assets calculates depreciation expense for the asset based on an asset life that includes the periods you did not depreciate it. If you suspend depreciation after an asset has started depreciating, Oracle Assets catches up the missed depreciation expense in the last period of life.

For flat-rate methods, Oracle Assets continues calculating depreciation expense for the asset based on the flat-rate. For flat-rate methods that use net book value, Oracle Assets uses the asset net book value at the beginning of the fiscal year in which you resume depreciation. The asset continues depreciating until it becomes fully reserved.

RECOVERABLE COST
For depreciation methods with a calculation basis of cost, Oracle Assets calculates depreciation using the recoverable cost. The recoverable cost is calculated as the lesser of either the cost less the salvage value less the investment tax credit basis reduction amount, or the cost ceiling.

Oracle Assets depreciates the asset until the accumulated depreciation equals the recoverable cost.

ADJUSTMENTS
The following are some examples of financial adjustments you can expense or amortize:
- Recoverable Cost Adjustments
- Depreciation Method Adjustments
- Life Adjustments
- Rate Adjustments
- Capacity Adjustments

PRIOR PERIOD TRANSACTIONS
- Prior Period Additions
If you enter an asset with a date placed in service before the current accounting period, Oracle Assets automatically calculates the missed depreciation and adjusts the accumulated depreciation on the next depreciation run. If you provide accumulated depreciation when you add the asset, Oracle Assets does not recalculate the accumulated depreciation. It accepts the amount you entered. For table and calculated methods, even if the entered accumulated depreciation differs from what Oracle Assets would have calculated, Oracle Assets does not depreciate the asset beyond the recoverable cost. If the accumulated depreciation is too low, Oracle Assets takes additional depreciation in the last period of the asset's life so that the asset becomes fully reserved. If the asset's accumulated depreciation is too high, Oracle Assets stops depreciating the asset when it becomes fully reserved, effectively shortening the asset life.

- Prior Period Transfers
If you backdate an asset transfer, Oracle Assets automatically reallocates depreciation expense by reversing some of the depreciation charged to the from account, and redistributing it proportionally to the to accounts. Retroactive transfers do not impact the total depreciation. You cannot backdate a transfer to a prior fiscal year.

- Prior Period Retirements / Reinstatements
If you backdate a retirement, Oracle Assets automatically adjusts the depreciation for the year by the appropriate amount, resulting in a one-time adjustment in depreciation expense for the period. Oracle Assets then computes the gain or loss using the resulting net book value. You cannot backdate a retirement to a previous fiscal year, nor can you reinstate a retirement performed in a previous fiscal year.

- Prior Period Amortized Adjustments
If you backdate an amortized adjustment, Oracle Assets automatically calculates depreciation from the retroactive amortization start date, and adds the retroactive depreciation to the current period.

- Negative Cost (Credit) Assets
You can enter a credit asset as an asset with a negative cost and Oracle Assets credits depreciation expense and debits accumulated depreciation each period for the life of the asset.

11i Oracle Purchasing Technical Document

2009-02-18T22:24:00.000-08:00

Oracle Purchasing is multi-org sensitive. The complexity depends on the set up.

Requisition Entry
Requisitions can be entered manually by the user or from the Requisition Import process.

Manual entry – Users will enter the data using the Requisitions form. At the header level, the user will enter the requisition number (if the app is set up to accept manual numbering), a requisition type and a description. At the line level, the user will enter the line type (Goods, Service, Labor etc.), and item (not required), a category (required), a description of the item or category, the units and the amount, the requestor, inventory org and location. Vendor and Vendor Site are optional. The user can enter multiple lines. After the line is entered, the user will enter the distributions for each line. Each line must have at least one distribution, but can consist of multiple. The distribution will be the code combination and/or project information for the purchase. When the user saves, the PR Account Generator is called to validate the code combination or if customized build the code combination based on auto-accounting rules from Oracle Projects. When the entry is saved, records are created in the following tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Import Entry – In order to import requisitions, the source must be mapped to the fields needed in the requisition interface. Coding will need to be done to utilize this functionality. Data will import using a concurrent process called Requisition Import. The data is brought into Oracle Purchasing and creates the header, lines and distributions. The main tables are:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Requisition Approval
Requisitions are approved based on specific application set-ups. The main set ups consist of determining of Approval Groups that determine the dollar value by document and/or account combination. The approval group is then assigned to a job title set up in the Oracle HR system. The amount assigned to the title determines who can have final approval authority. The user will submit the requisition from approval. The workflow then calls a process to build the initial approval list based on employee/supervisor (or position hierarchy if the system is set up that way). The list will include all approvers necessary to approve the requisition total. If there is no person with a high enough authority to approve, the requisition goes back to an incomplete status and the users will determine where the gap is and correct the assignments. If the requisition is on the incorrect path, the approver may forward the requisition to the correct person. The workflow will then call a procedure to rebuild the approval list based upon the forward to person’s employee/supervisor relationship. The procedure ends the current approval list header and creates a new header and lines. The history and status of the requisition is kept in the action history, which is viewable from the requisition form or from the requisition notification. The main tables are:
PER_ALL_PEOPLE_F
PER_JOBS
PER_ALL_ASSIGNMENTS_F
GL_CODE_COMBINATIONS
PO_CONTROL_RULES
PO_CONTROL_GROUPS_ALL
PO_CONTROL_FUNCTIONS
PO_POSITION_CONTROLS_ALL
PO_ACTION_HISTORY
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES

Purchase Order Entry
Purchase orders can be created 2 ways, autocreating from an approved requisition or by manual entry into the from.

Autocreate – In the autocreate form, the user will enter a requisition or search criteria to pull multiple requisitions available to be turned into purchase orders. The query will produce the lines available and the user can select all or any combination to create the purchase order. Example, if there are 3 requisitions approved for one vendor, the user can create one purchase order with all of the requisition information. The main tables are
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Manual entry – The user enters into the Purchase Order form the same information as entry for the requisitions. The tables listed above are the main tables.

11i Oracle Payables Technical Information

2009-02-18T22:22:00.000-08:00

Expense Report/Invoice Entry
Expense reports are entered using Web Expenses. This is multi-org sensitive, therefore each instance is set up with a template. The template is stored in AP_EXPENSE_REPORTS_ALL. The expense reports are submitted for approval and routed using workflow. When the expense report is approved, the payables department reviews and approved the expense report. Expense Report information is stored in the following tables
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL

The accounts payable department runs a concurrent process call “Payables Invoice Import”. This process turns approved expense reports into invoices. The process populates the following tables
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_AL.

These are the exact tables that are populated when an invoice is entered directly into the AP Invoice Entry form. Invoices must be approved and accounted. Approval can be done on individual invoices through the form or by running a concurrent process called “Payables Approval”. Once the invoice is approved, the accounting process can be run. This can also be done by through the AP Invoice Entry form or by running the concurrent process “Payables Accounting Process”. The process populates the following tables
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL

These are the tables that are used to create the journal entries that will go to the General Ledger. This process is called “Payables Transfer to General Ledger”. The process takes all lines in the AP_AE tables (whose GL_TRANSFER_FLAG = N and are within the parameters set by the user submitting the program) and inserts them into the GL_INTERFACE table. If the user marks Journal Import to Y when submitting, the completion of the transfer to GL process will kick off the Journal Import. This will then create the data in the GL_JOURNAL_BATCHES, GL_JOURNAL_HEADERS, and
GL_JOURNAL_LINES tables.

Creating Payments

Creating payments is the same basic navigation as creating invoices. An Invoice must be approved (does not have to have the accounting run yet). The payment can be created in the Invoice Entry form after the invoice is approved. The payments can also be created in batches. From the Payment Batches form, the user will enter the criteria to build the payment batch. The user will then choose actions and build the batch. The data will be inserted into the following tables:
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL

After the batch is built, formatted and confirmed, the user will need to create accounting and transfer to GL, as explained above.

How to remove page breaks from 6i reports

2009-02-17T21:47:00.000-08:00

Navigate to System Administrator Responisbility and query for the report name in the concurrent define section...

System Administrator -> Concurrent -> Program -> Define

Use the Printer style as BACS and check the Style Required box.

Once the above steps are done save the changes and navigate to the concerned responsibility to run the report. The report output will not have page breaks anymore. Generally this style is used for creating output files through reports.

How to authorize an employee to enter expense reports for another employee

2009-02-15T22:11:00.000-08:00

You can authorize an employee to enter expense reports for another employee. An employee who is assigned the responsibility to enter expense reports for another employee is an authorized delegate. There are 2 ways this could be done:

1. Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.

2. Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.

How to make a Oracle form 'Query Only'

2009-02-05T22:52:00.000-08:00

Follow the following steps inorder to make a form as Query only/Read Only.

1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..

Example: MODE="PROJECT" QUERY_ONLY=YES

ORA-29280: invalid directory path

2009-02-03T01:14:00.000-08:00

ORA-29280: invalid directory path

Before moving furthur in this post, please check if the directory has full permissions (Read, Write, Execute) or not. The error will also occour when there are no permissions to the directory. Once you set full permissions to the directory, try to run the program and if it still errors with the 'invalid directory path' error then please proceed furthur below for some more solutions....

Cause: A corresponding directory object does not exist.
Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.

We can derieve the directory object parameter from the following sql query:

-------------------------------------

SELECT VALUE
FROM v$parameter
WHERE NAME = 'utl_file_dir';

-------------------------------------

We can also use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.

Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in the init.ora file. In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.

Steps to create a example directory entry:

sql> Connect apps/apps
sql> create directory CESDIR_COMMON as '/home/common';
sql> grant read on directory CESDIR_COMMON to public;

Validating notification response

2009-02-03T01:04:00.000-08:00

I received a requirement from one of my clients to force the user(approver) to enter the comments value before approving the notification..This notification contains 2 responses (1. Approve, 2. Reject), so when the approver rejects the notification we need to make the Comments field in the notification as mandatatory and the notification should not go to the next stage untill he/she enters the comments field before clicking the Reject button in the workflow notification.

Fix: To implement this we need to write a Post notification function which validates the response and checks whether there is a value included in the response attribute. This post notification function needs to be placed in the workflow package and we need to call it the pl/sql block of the notification.

The code in the Post notification function would looks like this.

--------------------------------------------------------------------

PROCEDURE set_reject_comment (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2
)
IS
v_response VARCHAR2 (2000);
l_user_comment VARCHAR2 (2000);
approval_comment EXCEPTION;
BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
v_response :=
wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
l_user_comment :=
wf_notification.getattrtext (wf_engine.context_nid,
'APPROVAL_COMMENT'
);

/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
BEGIN
IF ( funcmode = 'RESPOND'
AND v_response = 'REJECTED'
AND l_user_comment IS NULL
)
THEN
RAISE approval_comment;
END IF;
EXCEPTION
WHEN approval_comment
THEN
raise_application_error
(-20010,
'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
);
RAISE;
END;

COMMIT;
END set_reject_comment;

--------------------------------------------------------------------

Pivot queries

2009-01-06T22:22:00.000-08:00

Pivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. Pivoting is a common technique, especially for reporting, and it has been possible to generate pivoted resultsets with SQL for many years and Oracle versions.

Lets explain this with an example:

1. The basic stucture of our table would be

2. We would like to see the output showing each of these 4 teams and their point tables for these 3 years.

3. Lets now see how to implement the requirement:

i) Here's the data

CREATE TABLE xx1 (Year1 NUMBER(4), team VARCHAR2(16), points NUMBER(3));
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India', 21);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India1', 28);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India2', 19);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India3', 10);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India', 18);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India1', 26);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India2', 2);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India3', 14);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India', 16);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India1', 27);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India2', 15);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India3', 10);

ii) Use "decode" to turn the year1 row into a column. Take a look at our "sparse" matrix.

SELECT team,
MAX (DECODE (year1, 2007, points, NULL)) Yr2007,
MAX (DECODE (year1, 2008, points, NULL)) Yr2008,
MAX (DECODE (year1, 2009, points, NULL)) Yr2009
FROM (SELECT year1, team, points FROM xx1)
GROUP BY team;

Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table

2008-12-15T00:41:00.000-08:00

STATUS_CODE Column:

A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting

PHASE_CODE column

C - Completed
I - Inactive
P - Pending
R - Running

DateTrack History view

2008-12-08T23:06:00.000-08:00

The name of the default DateTrack History view is the same as that of the base table, except that the suffix _F is replaced by _D. For example, if the base table is PER_ALL_PEOPLE_F, the application looks for a view called PER_ALL_PEOPLE_D.

Note: It is possible to define more than one History view for each datetracked table, so there might be examples where the History view name does not follow this naming convention.

When a view exists, the application reads the information about the entity name and column prompts from the DateTrack tables:
• DT_TITLE_PROMPTS_TL
• DT_DATE_PROMPTS_TL
• DT_COLUMN_PROMPTS_TL

If the column information is not available in the DT_COLUMN_PROMPTS_TL table, the information is obtained from the view definition. The DateTrack History code modifies the column names of the table or view before presenting them. Underscores are replaced by spaces and the first letter of each word appears in upper case.

Understanding the p_validate Control Parameter in HRMS API's

2008-12-08T23:00:00.000-08:00

Every published API includes the p_validate control parameter. When this parameter is set to FALSE (the default value), the procedure executes all validation for that business function. If the operation is valid, the database rows/values are inserted or updated or deleted. Any non warning OUT parameters, warning OUT parameters and IN OUT parameters are all set with specific values.

When the p_validate parameter is set to TRUE, the API only checks that the operation is valid. It does so by issuing a savepoint at the start of the procedure and rolling back to that savepoint at the end. You do not have access to these internal savepoints. If the procedure is successful, without raising any validation errors, then non-warning OUT parameters are set to null, warning OUT parameters are set to a specific value, and IN OUT parameters are reset to their IN values.

In some cases we may want to write our own PL/SQL routines using the public API procedures as building blocks. This enables us to write routines specific to our business needs. For example, say that we have a business requirement to apply a DateTracked update to a row and then apply a DateTrack delete to the same row in the future. We could write an "update_and_future_del" procedure that calls two of the standard APIs.

When calling each standard API, p_validate must be set to false. If true is used the update procedure call is rolled back. So when the delete procedure is called, it is working on the non-updated version of the row. However when p_validate is set to false, the update is not rolled back. Thus, the delete call operates as if the user really wanted to apply the whole transaction.

If we want to be able to check that the update and delete operation is valid, you must issue your own savepoint and rollback commands. As the APIs do not issue any commits, there is no danger of part of the work being left in the database. It is the responsibility of the calling code to issue commits.

Note: You should not use our API procedure names for the savepoint names. An unexpected result may occur if you do not use different names.

SMSing using PL/SQL

2008-11-19T04:14:00.000-08:00

1. Compile the below procedure on sql*plus

CREATE OR REPLACE PROCEDURE send_sms (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2
)
AS
mailcon UTL_SMTP.connection;
BEGIN
mailcon := UTL_SMTP.open_connection ('localhost');
UTL_SMTP.helo (mailcon, 'localhost');
UTL_SMTP.mail (mailcon, p_sender);
UTL_SMTP.rcpt (mailcon, p_recipient);
UTL_SMTP.DATA (mailcon,
'From: '
|| p_sender
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| UTL_TCP.crlf
|| 'Subject: SMS From Database'
|| UTL_TCP.crlf
|| p_message
);
UTL_SMTP.quit (mailcon);
END;

2. We need to execute the above procedure so as to send SMS. The systax is as follows:

exec SEND_SMS('arun@yahoo.com', '984812345@ideacellular.net','This is my first SMS');

Note: In place of the '984812345@ideacellular.net' you need to provide your mobile number. Also please refer to the below list of mobile operator and the area of the service.

Andhra Pradesh AirTel-----------Mobile No@airtelap.com
Andhra Pradesh Idea Cellular-----Mobile No@ideacellular.net
Chennai Skycell/Airtel------------Mobile No@airtelchennai.com
Chennai RPG Cellular-------------Mobile No@rpgmail.net
Delhi Airtel-----------------------Mobile No@airtelmail.com
Delhi Hutch-----------------------Mobile No@delhi.hutch.co.in
Gujarat Airtel---------------------Mobile No@airtelmail.com
Gujarat Idea Cellular--------------Mobile No@ideacellular.net
Gujarat Celforce/Fascel-----------Mobile No@celforce.com
Goa Airtel-------------------------Mobile No@airtelmail.com
Goa BPL Mobile-------------------Mobile No@bplmobile.com
Goa Idea Cellular------------------Mobile No@ideacellular.net
Haryana Airtel--------------------Mobile No@airtelmail.com
Haryana Escotel-------------------Mobile No@escotelmobile.com
Himachal Pradesh Airtel-----------Mobile No@airtelmail.com
Karnataka Airtel-------------------Mobile No@airtelkk.com
Kerala Airtel-----------------------Mobile No@airtelkerala.com
Kerala Escotel---------------------Mobile No@escotelmobile.com
Kerala BPL Mobile-----------------Mobile No@bplmobile.com
Kolkata Airtel----------------------Mobile No @airtelkol.com
Madhya Pradesh Airtel-------------Mobile No@airtelmail.com
Maharashtra Airtel-----------------Mobile No@airtelmail.com
Maharashtra BPL Mobile-----------Mobile No@bplmobile.com
Maharashtra Idea Cellular----------Mobile No@ideacellular.net
Mumbai Airtel----------------------Mobile No@airtelmail.com
Mumbai BPL Mobile----------------Mobile No@bplmobile.com
Punjab Airtel-----------------------Mobile No@airtelmail.com
Pondicherry BPL Mobile------------Mobile No @bplmobile.com
Tamil Nadu Airtel-------------------Mobile No@airtelmail.com
Tamil Nadu BPL Mobile-------------Mobile No@bplmobile.com
Tamil Nadu Aircel-------------------Mobile No@airsms.com
UP (West) Escotel-------------------Mobile No@escotelmobile.com

How to add comments on a table or a column.

2008-11-19T03:50:00.000-08:00

If we want to give a comment on a table :

COMMENT ON TABLE
IS 'the comments should go here';

If we want to give a comment on a column :

COMMENT ON TABLE | COLUMN table.column
IS 'the comments should go here';

The entries of these comments on the table goes in the following tables:

For tables:
select * from all_tab_comments
where table_name ='table_name'

select * from user_tab_comments
where table_name = 'table_name'

For Columns:
select * from all_col_comments
where table_name = 'table_name'

select * from user_col_comments
where table_name = 'table_name'

How to Compile and port a Form (fmb) in Oracle

2008-11-18T22:12:00.000-08:00

1. Port the attached form to unix directory to any top using FTP (Preferable we port the form to the AU_TOP)

NOTE: Copy .fmb files (form files) in Binary mode.

2. Connect to the telnet and change the directory to the top where we have ported the fmd.

Run the following commands in the above directory in Telnet:

a) FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US
b) f60gen module=formname.fmb userid=apps/apps output_file=formname.fmx module_type=form batch=no compile_all=special
example:
f60gen module=XXTRRBT.fmb userid=apps/apps output_file=XXTRRBT.fmx module_type=form batch=no compile_all=special

Excellent information on all Oracle Application module topics

2008-11-17T01:04:00.000-08:00

Please visit the following website to find excellent information on all Oracle Application Modules:

http://download-uk.oracle.com/docs/cd/A60725_05/html/comnls/us/index.htm

Script to exclude the control M(^M) from unix files

2008-11-17T00:34:00.000-08:00

The simple way to replace the control M character in UNIX format is as follows.

1. dos2unix filename newfilename
or
2. Open the file which contains the control M character and type the following
:%s/Control v control M//g

Workflow Table Information

2008-11-17T00:07:00.000-08:00

WF_ITEM_TYPES
The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE

WF_ITEM_ATTRIBUTES
The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES
WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES
The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES
WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS
WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.

WF_NOTIFICATION_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS
WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

WF_ITEM_ACTIVITY_STATUSES
The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)

WF_ITEM_ACTIVITY_STATUSES_H
The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES
WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS
The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES
The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

Approved Supplier List (ASL)

2008-11-16T21:46:00.000-08:00

Approved Supplier Lists in Oracle is the term used to describe a list of items and commodities that have approved sources from a list of suppliers.

All procurement organisations maintain lists that associate the items and services they buy with the companies who supply them, either formally or informally. Data stored in a controlled, global repository containing relevant details about each ship-from/ship-to/item relationship, is known as an Approved Supplier List (ASL).

Navigation for Approved Supplier List
PO Super User -> Supply base -> Approved Supplier List

Select the organization, then define the supplier for category or Item for which you want to define, and status to approved.

API's used to insert the Approved Supplier List's are as follows:
po_asl_ths.insert_row
po_asl_attributes_ths.insert_row

Index on Clob datatype

2008-11-14T04:04:00.000-08:00

CREATE INDEX ON () INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

The index creation includes a clause for setting the INDEXTYPE. I specified CTXSYS.CONTEXT as the type of index to create. CTXSYS is the Oracle Text schema, and this schema owns Oracle Text built-in features. The CTXAPP role granted to my user (TEXT_USER) provides permissions to use this index, but it must still be fully qualified with the schema name. CONTEXT is the type of index I am creating.

Overview of Inventory Transactions

2008-10-23T00:50:00.000-07:00

1. Transferring material between subinventories.
2. Performing miscellaneous transactions (miscellaneous issues and receipts).
3. Transferring material between Organizations.
4. Tracking lots and serial numbers for an item (Assigning Lot Numbers, Assigning Serial Numbers).
5. Generating material shortage alerts and material shortage notifications.
6. Entering and maintaining movement statistics information.
7. Viewing Material Transactions.
8. Viewing material transaction accounting distributions.
9. Viewing Transaction Summaries for a range of dates.
10. Viewing Pending Transactions.
11. Viewing and Updating pending Transaction Interface Activity.
12. Purging Transaction History.
13. Viewing Serial Genealogy.

Transferring Material Between Subinventories:
1. You can transfer material within your current organization between subinventories, or between two locators within the same subinventory.
2. You can transfer from asset to expense subinventories, as well as from tracked to non–tracked subinventories. (Here Asset, Expense, Tracked and Non-Tracked are the types of subinventories)
3. If an item has a restricted list of subinventories (Item is sub inventory controlled.), you can only transfer material from and to subinventories in that list.
4. Oracle Inventory allows you to use user–defined transaction types when performing a subinventory transfer.

Steps for entering a subinventory transfer:
1. Navigate to the Subinventory Transfer window.
2. Enter the date and time of entry for the transaction.
3. Enter a transaction type for the subinventory transfer. This can either be a predefined system type or one you defined.
4. Optionally, enter the source of the transaction type ex: Inventory
5. Optionally, indicate if inventory information should be defaulted from the serial number.
6. Choose Transaction Lines in the Subinventory Transfer window. The Transaction Lines Detail folder window appears.
7. Enter an inventory item to transfer. If you choose to default inventory information from the serial number (step5), enter a serial number.
8. Optionally, enter the revision for the item. You must enter a value here if the item is under revision control.
9. Enter the subinventories from and to which to transfer material. Enter the same subinventory in the Sub and To Sub fields to transfer material between locators.
10. Optionally, enter the locators from and to which to transfer the item. You must enter a value here if you established locator control. You can enter a new value in the To Locator field only if you defined locator control as dynamic entry.
11. Optionally, enter a lot number for the item. If you want to enter multiple lot numbers, complete the remaining steps then choose the Lot/Serial button to display the Lot Entry window.
12. Enter a unit of measure. This can be the primary unit of measure (the default) or any valid alternate unit of measure. If you enter an alternate unit of measure, Oracle Inventory issues the quantity you specify in this unit of measure. Oracle Inventory also converts the quantity to the primary unit of measure so that it can correctly update the on–hand quantity.
13. Enter the quantity of the inventory item to transfer, based on the unit of measure you specified.
14. Optionally, enter a reason code for the transaction. For example, you can use reason codes to allow you to mark exceptional charges to support a quality data collection and reporting system.
15. Optionally, enter up to 240 characters of free text that describes the transaction. (Reference field).

To view quantity available and quantity on hand values:
Look at the following fields in the form.

Available: Displays the quantity available to transfer, based on the unit of measure you specified. The available quantity is the quantity on hand less all reservations for the item. This amount could include the amount you have reserved if you enter a transaction source that has reservations against it. The available quantity includes reservations against current transaction source. The available quantity is specific to the revision level, lot number, From subinventory, and From locator you specify for the transfer.

Reservations will always be against a Transaction Source (Inventory, Purchase Order etc.)

On hand: Displays the current on–hand quantity for the item, based on the unit of measure you specified. The on–hand quantity is specific to the revision, lot number, From subinventory, and From locator you specify for the transfer. On–hand includes quantities for pending transactions in the MTL–MATERIAL–TRANSACTIONS table.

Performing Miscellaneous Transactions
With a miscellaneous transaction you can issue material to or receive material from general ledger accounts in your current organization.

Chr(13) appended to column values while performing sqlload

2008-10-16T23:35:00.000-07:00

Usually when we do the sqlload the '|' appends to the end of the string. This '|' is knows as Chr(13) and can be removed once the sqlload is done using the following update statement:

Update [table_name]
set [column_name] = trim(chr(13) from [column_name])

Deadlocks and how to break them?

2008-10-16T04:44:00.000-07:00

What is a deadlock?

Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

Deadlocks in Oracle result in this error:

ORA-00060: deadlock detected while waiting for resource

If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.

How to Break them?

SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id

SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'

ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'

All about MRP Sourcing Rules API and Single record insertion script

2008-10-15T22:55:00.000-07:00

Standard Validation
Oracle Master Scheduling/MRP validates all required columns in the Sourcing Rule/Bill of Distribution API. For specific information on the data implied by these columns, see your Oracle Master Scheduling/MRP Technical Reference Manual for details.

If you do not want to update a particular column in:
❏ MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
Do not enter NULL for its corresponding interface parameter unless the default in the PL/SQL specification is NULL. Either use one of the missing parameter constants defined in the FND_API package (G_MISS_...), or do not pass any value at all.

For all flag parameters, pass in a Boolean constant defined in FND_API (G_TRUE or G_FALSE). Each time the API is called, it will check the allocation percent for each receiving organization that belongs to the sourcing rule or bill of distribution. If the total allocation percent is 100, the plannning_active attribute is set to a value of 1. Otherwise the attribute is set to 2.

Creating Sourcing Rule API Entries
When you create a new sourcing rule the following item level validations:
■ sourcing_rule_name: must be defined in the MRP_SOURCING _RULES table.
■ sourcing_rule_type: must be a either (1) Sourcing Rule or (2) Bill of
Distribution.If the sourcing rule does not already exist in the system - the Status attribute is set to 1.
When you create a new sourcing rule, the following record level validations occur:
■ organization_id: must be a valid organization defined in ORG_ORGANIZATION_DEFINITIONS.
■ The organization_id attribute is associated with a valid organization, unless it is null.
When you create a new sourcing rule, the following object level validations occur:
■ At least one receiving organization record is created.
■ At least one shipping organization record is created.
If validation is successful, a record is inserted into the MRP_SOURCING_RULES table.

Procedure Parameter Descriptions for MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE
The following content describes all parameters used by the public API MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE procedure. Additional information on these parameters follows.

1. p_api_version_number
Used to compare the incoming API call's version number with the current version number. An error is returned if the version numbers are incompatible.

2. p_init_msg_list
Requests that the API initialize the message list on your behalf. If the x_msg_count is greater than 1, then the list of messages must be retrieved using the call FND_MSG_PUB.GET. The values are:
■ p_msg_index => I
■ p_encoded => F
■ p_data => 1_message
■ p_msg_index_out => 1_msg_index_out
where 1_message and 1_msg_index_out are local variables of types Varchar2(2000 and Number respectively.
Default Value: FND_API.G_FALSE

3. p_return_values
Requests that the API send back the values on your behalf.
Default Value: FND_API.G_FALSE

4. p_commit
Requests that the API update information for you after it completes its function.
Default Value: FND_API.G_FALSE

5. x_return_status
Requests that the API return the status of the data for you after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR

6. x_msg_count
Indicates number of error messages API has encountered.

7. x_msg_data
Displays error message text. If the x_msg_count is equal to 1, then this contains the actual message.

8. p_sourcing_rule_rec
The sourcing rule or bill of distribution record referenced by the API.
Default Value: G_MISS_SOURCING_RULE_REC

9. p_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.
Default Value: G_MISS_SOURCING_RULE_VAL_REC

10. p_receiving_org_tbl
The receiving organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_RECEIVING_ORG_TBL

11. p_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule.
Default Value: G_MISS_RECEIVING_ORG_VAL_TBL

12. p_shipping_org_tbl
The shipping organization information listed in the rule is returned to this parameter.
Default Value: G_MISS_SHIPPING_ORG_TBL

13. p_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule.
Default Value: G_MISS_SHIPPING_ORG_VAL_TBL

14. x_sourcing_rule_rec
Result of the API data after it completes its function for the sourcing rule/bill of distribution record.

15. x_sourcing_rule_val_rec
Resolves the values for the API, and then returns the information for the sourcing rule/bill of distribution record.

16. x_receiving_org_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the sourcing rule/bill of distribution record.

17. x_receiving_org_val_tbl
Resolves the values for the API, and then returns the information for the receiving organization listed in the rule.

18. x_shipping_org_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the sourcing rule/bill of distribution record.

19. x_shipping_org_val_tbl
Resolves the values for the API, and then returns the information for the shipping organization listed in the rule.

Record Parameter Descriptions

SOURCING_RULE_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SOURCING_RULE_REC_TYPE record with some additional information.

1. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM

2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR

3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR

4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM

5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE

6. description
Text describing the sourcing rule record type.
Default Value: FND_API.G_MISS_CHAR

7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM

8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE

9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM

10. organization_id
The identification number for the organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM

11. planning_active
Rule is active when the sum of the allocation percentages equals 100.
Default Value: FND_API.G_MISS_NUM

12. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM

13. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM

14. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE

15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM

16. sourcing_rule_name
Valid name of rule defined in the MRP_SOURCING _RULES table.
Default Value: FND_API.G_MISS_CHAR

17. sourcing_rule_type
Valid types must be one of the following values:
■ (1) Sourcing Rule
■ (2) Bill of Distribution
Default Value: FND_API.G_MISS_NUM

18. status
If any validation fails, the API will return error status to the calling module. The Sourcing Rule API processes the rows and reports the following values for every record. If the sourcing rule does not already exist in the system - the Status attribute is set to 1. Processing status of the sourcing rule, valid values are:
■ (1)
■ (2)
Default Value: FND_API.G_MISS_NUM

19. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR

20. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR

21. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR

RECEIVING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the RECEIVING_ORG_REC_TYPE
record with additional information:

1. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM

2. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR

3. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR

4. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM

5. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE

6. disable_date
Date the receipt organization is no longer effective.
Default Value: FND_API.G_MISS_DATE

7. effective_date
Beginning date the receipt organization becomes effective.
Default Value: FND_API.G_MISS_DATE

8. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM

9. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE

10. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM

11. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM

12. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM

13. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE

14. receipt_organization_id
Identifier of the organization that serves as the destination for the sourcing rule or bill of distribution.
Default Value: FND_API.G_MISS_NUM

15. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM

16. sourcing_rule_id
Identification number for the sourcing rule or bill of distribution record referenced by the API.
Default Value: FND_API.G_MISS_NUM

17. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR

18. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR

19. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR

SHIPPING_ORG_REC_TYPE
The procedure passes information to record groups and PL/SQL tables. The information below describes all records that are used by the SHIPPING_ORG_REC_TYPE record with additional informatioin.

1. sr_source_id
Primary key in the sourcing rule or bill of distribution table.
Default Value: FND_API.G_MISS_NUM

2. allocation_percent
Percentage allocated to each source organization/supplier site destination.
Default Value: FND_API.G_MISS_NUM

3. attribute 1 - 15
Descriptive text for flexfields.
Default Value: FND_API.G_MISS_CHAR

4. attribute_category
The category of the flexfield described in the attribute column.
Default Value: FND_API.G_MISS_CHAR

5. created_by
Identification number for user initiating this program session.
Default Value: FND_API.G_MISS_NUM

6. creation_date
Date this program session was created.
Default Value: FND_API.G_MISS_DATE

7. last_updated_by
User ID for user creating this program session.
Default Value: FND_API.G_MISS_NUM

8. last_update_date
Date program was last updated.
Default Value: FND_API.G_MISS_DATE

9. last_update_login
User login for user updating this program.
Default Value: FND_API.G_MISS_NUM

10. program_application_id
Application identifier of the program that has made a call to the Sourcing Rule API if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM

11. program_id
Identifier of the program that has made a call to the Sourcing Rule API, if it is registered as a concurrent program in Oracle Application Object Library.
Default Value: FND_API.G_MISS_NUM

12. program_update_date
The date when the program inserts or updates the sourcing records into the appropriate tables.
Default Value: FND_API.G_MISS_DATE

13. rank
Rank of the sources, valid values are non-zero integers.
Default Value: FND_API.G_MISS_NUM

14. request_id
The request ID determines which profile values are used as a default.
Default Value: FND_API.G_MISS_NUM

15. secondary_inventory
Currently not used.

16. ship_method
Method used when transporting material between source and destination.
Default Value: FND_API.G_MISS_CHAR

17. source_organization_id
Identifier of the source organization.
Default Value: FND_API.G_MISS_NUM

18. source_type
Indicator of the type of source. Valid values are:
■ Make
■ Transfer
■ Buy
Default Value: FND_API.G_MISS_NUM

19. sr_receipt_id
Identification number for the receiving organization referenced in the sourcing rule or bill of distribution record.
Default Value: FND_API.G_MISS_NUM

20. vendor_id
Identifier of the vendor suppling the materials.
Default Value: FND_API.G_MISS_NUM

21. vendor_site_id
Identifier where the vendor’s materials are located.
Default Value: FND_API.G_MISS_NUM

22. return_status
Processing status of the API after it completes its function. Valid values include:
■ Success: FND_API.G_RET_STS_SUCCESS
■ Error: FND_API.G_RET_STS_ERROR
■ Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
Default Value: FND_API.G_MISS_CHAR

23. db_flag
Indicator of the record existing in the database.
Default Value: FND_API.G_MISS_CHAR

24. operation
Indicator of whether the record is inserted, updated, or deleted. Valid values include:
■ Create
■ Update
■ Delete
Default Value: FND_API.G_MISS_CHAR

25. receiving_org_index
Foreign key to the receipt organization PL/SQL table.
Default Value: FND_API.G_MISS_NUM

Single Record insertion script for MRP Sourcing Rules

DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_err_count NUMBER := 0;
l_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
l_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
l_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
l_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
l_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
l_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
o_sourcing_rule_rec MRP_SOURCING_RULE_PUB.SOURCING_RULE_REC_TYPE;
o_sourcing_rule_val_rec
MRP_SOURCING_RULE_PUB.SOURCING_RULE_VAL_REC_TYPE;
o_receiving_org_tbl MRP_SOURCING_RULE_PUB.RECEIVING_ORG_TBL_TYPE;
o_receiving_org_val_tbl
MRP_SOURCING_RULE_PUB.RECEIVING_ORG_VAL_TBL_TYPE;
o_shipping_org_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_TBL_TYPE;
o_shipping_org_val_tbl MRP_SOURCING_RULE_PUB.SHIPPING_ORG_VAL_TBL_TYPE;
BEGIN
fnd_message.clear;
l_sourcing_rule_rec := MRP_SOURCING_RULE_PUB.G_MISS_SOURCING_RULE_REC;
l_sourcing_rule_rec.sourcing_rule_name := '1234'; --SR Name
l_sourcing_rule_rec.Organization_Id:=207;
l_sourcing_rule_rec.planning_active := 1; -- Active?
l_sourcing_rule_rec.status := 1; -- Update New record
l_sourcing_rule_rec.sourcing_rule_type := 1; -- 1:Sourcing Rule
l_sourcing_rule_rec.operation := 'UPDATE';
l_sourcing_rule_rec.OPERATION=>'CREATE';
l_receiving_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_RECEIVING_ORG_TBL;
l_shipping_org_tbl := MRP_SOURCING_RULE_PUB.G_MISS_SHIPPING_ORG_TBL;
--l_receiving_org_tbl(1).Sr_Receipt_Id:=207;
l_receiving_org_tbl(1).effective_date := trunc(sysdate)+1;
l_receiving_org_tbl(1).disable_date := trunc(sysdate)+7;
l_receiving_org_tbl(1).receipt_organization_id:=207;
l_receiving_org_tbl(1).operation := 'CREATE'; -- Create or Update
--l_shipping_org_tbl(1).Sr_Source_Id:=228;
l_shipping_org_tbl(1).rank := 1;
l_shipping_org_tbl(1).allocation_percent := 100; -- Allocation 100
l_shipping_org_tbl(1).source_type := 3; -- BUY FROM
l_shipping_org_tbl(1).vendor_id := 21;
l_shipping_org_tbl(1).receiving_org_index := 1;
l_shipping_org_tbl(1).operation := 'CREATE';
dbms_output.put_line('before call');
dbms_output.put_line('Operation before call
'||l_sourcing_rule_rec.operation);
MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE(
p_api_version_number =>1.0
,p_init_msg_list =>fnd_api.g_true
,p_commit => fnd_api.g_true
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
,p_sourcing_rule_rec => l_sourcing_rule_rec
,p_sourcing_rule_val_rec => l_sourcing_rule_val_rec
,p_receiving_org_tbl => l_receiving_org_tbl
,p_receiving_org_val_tbl => l_receiving_org_val_tbl
,p_shipping_org_tbl => l_shipping_org_tbl
,p_shipping_org_val_tbl => l_shipping_org_val_tbl
,x_sourcing_rule_rec => o_sourcing_rule_rec
,x_sourcing_rule_val_rec => o_sourcing_rule_val_rec
,x_receiving_org_tbl => o_receiving_org_tbl
,x_receiving_org_val_tbl => o_receiving_org_val_tbl
,x_shipping_org_tbl => o_shipping_org_tbl
,x_shipping_org_val_tbl => o_shipping_org_val_tbl
);
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Success!');
else
dbms_output.put_line('count:'||l_msg_count);
IF l_msg_count > 0 THEN
FOR l_index IN 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(
p_msg_index => l_index,
p_encoded => FND_API.G_FALSE);
dbms_output.put_line(substr(l_msg_data,1,250));
END LOOP;
dbms_output.put_line('MSG:'||o_sourcing_rule_rec.return_status);
END IF;
dbms_output.put_line('Failure!');
end if;
END;

Single Record insertion script for MRP Sourcing Rule Assignments

DECLARE
l_session_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (1000);
l_msg_index_out NUMBER;
l_count NUMBER;
l_org_cnt NUMBER;
l_vendor_cnt NUMBER;
l_org_class VARCHAR2 (3);
l_org_num NUMBER;
l_line_num NUMBER := 0;
l_err_count NUMBER := 0;
p NUMBER;
l_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
o_assignment_set_rec mrp_src_assignment_pub.assignment_set_rec_type;
o_assignment_set_val_rec mrp_src_assignment_pub.assignment_set_val_rec_type;
o_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
o_assignment_val_tbl mrp_src_assignment_pub.assignment_val_tbl_type;
BEGIN
fnd_message.CLEAR;
-- l_assignment_set_rec.assignment_set_id := 3530;
-- l_assignment_set_rec.assignment_set_name := 'Test_src1_set';
-- l_assignment_set_rec.operation := 'CREATE';
l_assignment_tbl (1).assignment_set_id := 1;
l_assignment_tbl (1).assignment_type := 6;
l_assignment_tbl (1).operation := 'CREATE';
l_assignment_tbl (1).organization_id := 107;
l_assignment_tbl (1).inventory_item_id := 37002;
l_assignment_tbl (1).sourcing_rule_id := 1014;
l_assignment_tbl (1).sourcing_rule_type := 1;
mrp_src_assignment_pub.process_assignment
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_assignment_set_rec => l_assignment_set_rec,
p_assignment_set_val_rec => l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl => l_assignment_val_tbl,
x_assignment_set_rec => o_assignment_set_rec,
x_assignment_set_val_rec => o_assignment_set_val_rec,
x_assignment_tbl => o_assignment_tbl,
x_assignment_val_tbl => o_assignment_val_tbl
);

IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
ELSE
DBMS_OUTPUT.put_line ('count:' || l_msg_count);

IF l_msg_count > 0
THEN
FOR l_index IN 1 .. l_msg_count
LOOP
l_msg_data :=
fnd_msg_pub.get (p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data, 1, 250));
END LOOP;

DBMS_OUTPUT.put_line ('MSG:' || o_assignment_set_rec.return_status);
END IF;

DBMS_OUTPUT.put_line ('Failure!');
END IF;
END;
/

Script to reset OM Records, while performing the conversions using interface approach

2008-10-15T22:52:00.000-07:00

UNDEFINE NEW_SOURCE_ID
UNDEFINE OLD_SOURCE_ID

select s.order_source_id, substrb(s.name,1,20)
from oe_order_sources s;

select s.order_source_id, substrb(s.name,1,20) source, count(distinct h.header_id) headers
from oe_order_headers_all h,
oe_order_sources s
where h.order_source_id = s.order_source_id
group by s.order_source_id, s.name;

update oe_order_headers_all
set order_source_id = &&NEW_SOURCE_ID
where order_source_id = &&OLD_SOURCE_ID;

update oe_order_lines_all
set order_source_id = &&NEW_SOURCE_ID
where order_source_id = &&OLD_SOURCE_ID;

select s.order_source_id, substrb(s.name,1,20) source, count(distinct h.header_id) headers
from oe_order_headers_all h,
oe_order_sources s
where h.order_source_id = s.order_source_id
group by s.order_source_id, s.name;

delete oe_headers_iface_all
where order_source_id = &&OLD_SOURCE_ID;

delete oe_lines_iface_all
where order_source_id = &&OLD_SOURCE_ID;

delete oe_actions_iface_all
where order_source_id = &&OLD_SOURCE_ID;

Import errors while loading Item Costs

2008-10-13T22:19:00.000-07:00

There is a good documentation for the import issues on the item costs in the metalink. Please refer to the doc id: 213583.1

Oracle Inventory, Units of Measure

2008-10-13T22:13:00.000-07:00

First start with defining the Unit of Measure.

For example, one class might be "Volume". How does your business measure "Volume" ? For example, the concrete business measures their product in cubic yards. Utilities such as a water department measures their product in gallons. These are aspects of the class known as "Volume". Another class might be "Distance". How does your business measure "Distance" ?

For example, the concrete business measures distance in miles (number of miles dispatch makes a delivery to the customer). A water department may measure distance in feet or even inches (amount of pipe laid to each customer. These are all aspects of defining the Unit of Measure Classes, which is understanding the needs of the business. Most businesses need many difference classes set up, for measures such as distance, volume, time, weight, etc. Some may not be applicable.

The base unit of measure is normally the smallest possible unit of measure. If the concrete company rounds off their miles, regardless and never is concerned about 1/10th of a mile, then 'mile' would be their base unit for distance.

Second define your Units of Measure. A primary unit of measure must be selected as the stocking unit of measure for an item in the organization.

Third define your Unit of Measure Conversions. Oracle does not know that 24 cans of soda make up a case. This is an intra-class conversion - like items. Suppose a can is the smallest unit of measure in this situation. 24 case equal a case. 12 cases equal a box. 8 boxes equal a pallet. 32 pallets equal a truck load. These are the types measures that need the to be set up for intra-class conversions for all of your items. If all of your items are cans, then this needs to be set up once. If you have many different dissimiliar items, then intra-class conversions need to be set up for each different type of item.

Interclass conversions. Lets suppose that you measure your soda cans in ounces, 12 ounces to a can. But your business also ships globally where the measure is performed in milliliters. Therefore a interclass conversion needs to be set up that denotes that one fluid ounce equals 29.583 milliliters of product.

If you are in the concrete business, an you normally measure your product in cubic yards. However many of your customers need to know how many cubic yards of concrete are necessary to cover 500 square feet (with an industry standard of 4 inch thicknesss). This is an Intraclass conversion - you are converting cubic yards into square feet of product.

Oracle Support and Why upgrading to R12?

2008-10-13T21:39:00.000-07:00

Oracle Support

Premier Support:
- Includes certification with new third party products/versions and Oracle products

Extended Support:
- Costs more than Premium Support
- Certification is with most existing instead of new third party products/versions and Oracle products
- Extended Support may not include certification with some new third party products/versions
- So if you find a new problem with an existing certified configuration, Oracle Support will help you.
- If you're still running Oracle 9i you're on Extended Support(which ends July 2010)

Sustaining Support:
- Does not include new updates, fixes, security alerts, data fixes and critical patch updates; new tax, legal and regulatory updates; new upgrade scripts; certification with new third-party products/versions; certification with new Oracle Products
- If you have a question and there's already an answer in Metalink, Oracle Support will point you to it
- If you're still running Release 11.0.3, you're on Sustaining Support(which end Jan 2009)

What's the risk in not upgrading to R12?
- The biggest risk - Security/Performance
- Critical Patch Updates(CPUs)
- CPUs address security vulnerabilities
- Oracle release a new CPU every quarter
- R12 Apps CPUs are cumulative
- Relase 12.0.4 includes the Jan 2008 CPU
- Oracle follows strict compilance with ATG_PF RUP policy(N-1)
* January 2008 CPU will only support ATG RUP 5 and RUP 6
* To stay current on security, you must stay relatively current on ATG RUPs, which means that you must stay relatively current on which releases of the applications you are running
- If Sarbanes-Oxley compilance is important to your company, can you afford to fall behind on security updates?
- If you know there's a Critical Patch Update, and you know what vulnerabilities it fixes, so do hackers

SQL*Loader

2008-10-08T01:02:00.000-07:00

This section will provide a basic understanding of SQL*Loader. Most of the information contained in this section is DIRECTLY extracted from 'ORACLE Server Utilities Users Guide' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the 'ORACLE Server Utilities Users Guide'.

SQL*Loader is a product for moving data from external files into tables in an ORACLE database. SQL*Loader loads data in a variety of formats, performs filtering (selectively loading records based upon the data values), and loads multiple tables simultaneously. During execution SQL*Loader produces a detailed log file with statistics about the load, and may also produce a bad file (records rejected because of incorrect data) and a discard file (records that did not meet your selection criteria). You have control over several loading options.

You must provide two types of input to SQL*Loader to load data from external files into an ORACLE database: the data itself, and control information describing how to perform the load. You must provide a file called the control file as an input to SQL*Loader. The control file tells SQL*Loader how to interpret the data file. For example, it describes the following:

1. The names of the data files
2. The format of the data files
3. The character sets used in the data files
4. The datatypes of the fields in those files
5. How to identify the start and end of data fields
6. Which tables and columns to load

The control file's datatype specifications tell SQL*Loader how to interpret the fields in the data files. SQL*Loader uses this information when working with the fields, and uses it to describe the data that is being passed to ORACLE. ORACLE then converts the data into the datatype specified by the table definition. Some information is mandatory (such as where to find the data and how it corresponds to the database tables). However, many options are also available to describe and manipulate the file data. For example, the instructions can include directions on how to format or filter the data, or to generate unique ID numbers.

You may load data in various formats. It is usually read from one or more data files, but the data may also be placed in the control file after the control file information. Data records may be in fixed or variable format. In fixed format, the data is contained in records which all have the same (fixed) format. That is, the records have a fixed length, and the data fields in those records have fixed length, type, and position.

In variable format (sometimes called stream format), each record is only as long as necessary to contain the data. With character data, if the first item is shorter than the second one, the first record is shorter. Also, the type of data in each record may vary. One record may contain a character string, the next may contain seven integers, the third may contain three decimals and a float, and so on. Operating systems use a record terminator character (such as newline) to mark where variable records end.

Delimited data is of two types: terminated or enclosed. Terminated data is followed by a specified character such as a comma, as in the following example:
1,1,2,3,5,8,13

Enclosed data is preceded and followed by a specified character such as a quotation mark, as in the following example:
'BUNKY'

A final distinction concerns the difference between logical and physical records.

A record or line in a file (either of fixed length or terminated) is referred to as a physical record. Logical record, on the other hand, corresponds to a row in a database table. Sometimes the logical and physical records are equivalent; such is the case when only a few short columns are being loaded.

However, sometimes several physical records must be combined to make one logical record. The examples below will illustrate some of the features of the SQL*Loader.

Example 1. Loading Data into Multiple Tables

CONTROL FILE: The control file for this example.
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO listed
-- for each employee

LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'

a. REPLACE

b. INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)

b. INTO TABLE proj
-- PROJ has two columns, both not null: EMPNO and PROJNO

c. WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
c. projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj

b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj

b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj

---------------------------------------------------------------
NOTES:

(a) REPLACE indicates that if there is data in the tables to be loaded
(EMP and PROJ), that data should be deleted before new rows are loaded.

(b) Multiple INTO clauses are used to load two tables, EMP and PROJ. The
same set of records is processed three times using different combinations
of columns each time, to load table PROJ.

(c) WHEN is used to load only rows with non-blank project numbers. When
PROJNO is defined as columns 25..27, rows are inserted into PROJ only if
there is a value in those columns.

DATA FILE - Part of the data file follows.
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE5.CTL LOG=ULCASE5.LOG

Example 2 Loading a Delimited, Free-Format File

CONTROL FILE - The control file for this example.
-- Variable-length, delimited and enclosed data format

LOAD DATA
a. INFILE *

b. APPEND

INTO TABLE emp
c. FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
d. hiredate DATE "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno,
e. loadseq SEQUENCE(MAX,1))

f. BEGINDATA
7782, "CLARK", "Manager", 7839, 09-June-1981, 2572.50, 10:101
7839, "King", "President", , 17-January-1982, 920.00, 10:102

---------------------------------------------------------------
NOTES:

(a) INFILE * signifies the data is found at the end of the control file.

(b) APPEND indicates that data may be loaded even if the table already
contains rows; the table need not be empty.

(c) The default terminator for the data fields is a comma, and some fields
may be enclosed by a double quote.

(d) The data to be loaded into column HIREDATE appears in the format
DD-Month-YYYY.

(e) The SEQUENCE function is used to generate a unique value in the column
LOADSEQ. This function finds the current maximum value in column LOADSEQ
and adds the increment (1) to it to obtain the value for LOADSEQ for each
row inserted.

(f) BEGINDATA signifies the end of the control information and the
beginning of the data.

INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE3.CTL LOG=ULCASE3.LOG

Control File Syntax
The control file usually begins with the phase LOAD DATA, followed by several phrases that describe the data to be loaded. Only comments or the OPTIONS phrase can precede the LOAD DATA phase.

For a complete control file syntax diagram see Appendix C in this manual. Only a subset of the syntax will be explained below. For a complete explanation of the above syntax, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''. Comments Comments may appear anywhere in the command section of the file, but they should not appear in the data. Comments are preceded with a double dash, which may appear anywhere on a line. All text to the right of the double dash is ignored,
until the end of line.

The OPTIONS Clause
The OPTIONS clause is useful when you usually invoke a control file with the same set of options, or when the command line and all its arguments becomes very long. This clause allows you to specify runtime arguments in the control file rather than on the command line.
SKIP = n -- Number of logical records to skip (DEFAULT 0)
LOAD = n -- Number of logical records to load (DEFAULT all)
ERRORS = n -- Number of errors to allow (DEFAULT 50)
ROWS = n -- Number of rows in conventional path bind array (DEFAULT 64)
BINDSIZE = n -- Size of conventional path bind array in bytes
SILENT = {HEADER | FEEDBACK | ERROR | DISCARDS | ALL }
-- Suppress messages during run

For example:
OPTIONS (BINDSIZE=10000, SILENT=(ERRORS, FEEDBACK) )
Values specified on the command line override values specified in the control file. With this precedence, the OPTIONS keyword in the control file established default values that are easily changed from the command line. Continuing Interrupted Loads If SQL*Loader runs out of space for data rows or index entries, the load is discontinued. (For example, the table might reach its maximum number of extents.) Discontinued loads can be continued after more space is made available. When a load is discontinued, any data already loaded remains in the tables, and the tables are left in a valid state. SQL*Loader's log file tells you the state of the tables and indexes and the number of logical records already read from the input data file. Use this information to resume the load where it left off.
For example:
SQLLOAD / CONTROL=FAST1.CTL SKIP=345
CONTINUE\_LOAD DATA statement is used to continue a discontinued direct path load involving multiple tables with a varying number of records to skip. For more information on this command, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''.

Identifying Data Files
To specify the file containing the data to be loaded, use the INFILE or INDDN keyword, followed by the filename. A filename specified on the command line overrides the first INFILE or INDDN statement in the control file. If no filename is specified, the filename defaults to the control filename with an extension or filetype of DAT.

Loading into Non-Empty Database Tables
SQL*Loader does not update existing records, even if they have null columns. If the tables you are loading already contain data, you have three choices for how SQL*Loader should proceed:

1. INSERT - This is the default option. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.
2. APPEND - If data already exists in the table, SQL*Loader appends the new rows to it; if data doesn't already exist, the new rows are simply loaded.
3. REPLACE - All rows in the table are deleted and the new data is loaded. This option requires DELETE privileges on the table.

You can create one logical record from multiple physical records using CONCATENATE and CONTINUEIF. See chapter 6 of 'ORACLE7 Server Utilities Users Guide'.

Loading Logical Records into Tables
1. The INTO TABLE clause allows you to tell which table you want to load data into. To load multiple tables, you would include one INTO TABLE clause for each table you wish to load. The INTO TABLE clause may continue with some options for loading that table. For example, you may specify different options (INSERT, APPEND, REPLACE) for each table in order to tell SQL*Loader what to do if data already exists in the table.
2. The WHEN clause appears after the table name and is followed by one or more field conditions. For example, the following clause indicates that any record with the value ``q'' in the fifth column position should be loaded:
WHEN (5) = 'q'
A WHEN clause can contain several comparisons as long as each is preceded by AND. Parentheses are optional but should be used for clarity with multiple comparisons joined by AND. For example:
WHEN (DEPTNO = '10') AND (JOB = 'SALES')
To evaluate the WHEN clause, SQL*Loader first determines the values of all the fields in the record. Then the WHEN clause is evaluated. A row is inserted into the table only if the WHEN clause is true. When the control file specifies more fields for a record than are present in the record, SQL*Loader must determine whether the remaining (specified) columns should be considered null, or whether an error should be generated. TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. For example, if the following
data
10 Accounting

is read with the following control file

INTO TABLE dept
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE )
and the record ends after DNAME, then the remaining LOC field is set to null. Without the TRAILING NULLCOLS clause, an error would be generated, due to missing data.

Specifying Datatypes
The datatype specification in the control file tells SQL*Loader how to interpret the information in the data file. The server defines the datatypes for the columns in the database. SQL*Loader extracts data from a field in the input file, guided by the datatype specification in the control file. SQL*Loader then sends the field to the server to be stored in the appropriate column. The server does any data conversion necessary to store the data in the proper internal format. The datatype of the data in the file does not necessarily have to be the same as the datatype of the column in the ORACLE table. ORACLE automatically performs conversions - but you need to ensure that the conversion makes sense and does not generate errors.
SQL*Loader does not contain datatype specifications for ORACLE internal datatypes like NUMBER or VARCHAR2. SQL*Loader's datatypes describe data that can be produced with text editors (character datatypes) and with standard programming languages (native datatypes).

Native Datatypes
Some datatypes consist entirely of binary data, or contain binary data in their implementation. These non-character datatypes are the native datatypes:
INTEGER ZONED SMALLINT
VARCHAR FLOAT GRAPHIC
DOUBLE GRAPHIC EXTERNAL BYTEINT
VARGRAPHIC (packed) DECIMAL RAW

These datatypes will not be discussed as most of the datatypes that you will be using will be character datatypes. For more information on SQL*Loader datatypes,

Character Datatypes
The character datatypes are CHAR, DATE, and the numeric EXTERNAL datatypes (INTEGER and DECIMAL). These fields can be delimited, and can have lengths (or maximum lengths) specified in the control file.

1. CHAR - This data field contains character data. The length is optional, and is taken from the POSITION specification if it is not present here. If present, this length overrides the length in the POSITION specification. If no length is given, CHAR data is assumed to have a length of 1. A field of datatype CHAR may also be variable-length delimited or enclosed.

2. To Load LONG Data: If the column in the database table is defined as LONG, you must explicitly specify a maximum length either with a length-specifier on the CHAR keyword, or with the POSITION keyword. This guarantees that a large enough buffer is allocated for the value, and is necessary even if the data is delimited or enclosed.

3. DATE - This data is character data that should be converted to an ORACLE date using the specified date mask. The length specification is optional, unless a varying-length data mask is specified. With a specification like:
DATE "Month dd, YYYY"
the date mask is 14 characters, while the length of a field like
September 31, 1991
is 18 characters. In this case, a length must be specified. Similarly, a length is required for any Julian dates (date mask ``J'') - a field length is required any time the length of the date string could exceed the length of the mask. An explicit length specification, if present, overrides the length in the POSITION clause. Either of these overrides the length derived from the mask. The mask may be any valid ORACLE date mask. If you omit the mask, the default ORACLE date mask of 'dd-mon-yy' is used.

4. Numeric EXTERNAL - The numeric external datatypes are the numeric datatypes (INTEGER, FLOAT, DECIMAL, and ZONED) specified with the EXTERNAL keyword along with optional length and delimiter specifications. These datatypes are the human-readable, character form of numeric data. The data is a number in character form (not binary representation). As such, these datatypes are identical to CHAR and are treated identically, with one exception: the use of DEFAULTIF. If you want the default to be null, use CHAR;

5. delimiter_spec - The boundaries of CHAR, DATE, or numeric EXTERNAL fields may also be marked by specific delimiter characters contained in the input data record. You indicate how the field is delimited by using a delimiter specification after specifying the datatype. Delimited data can be TERMINATED or ENCLOSED.

All About 'FND_GLOBAL' in Oracle

2008-10-07T05:32:00.000-07:00

Before calling a concurrent request from the backend, we need to set the Oracle Applications Context. Use the API FND_GLOBAL.APPS_INITIALIZE to set the applications context in standalone sessions that were not initialized through normal means. Typically, you would use this API in external custom programs that are establishing their own connections.

FND_GLOBAL.APPS_INITIALIZE
Procedure APPS_INITIALIZE (user_id in number, resp_id in number, resp_appl_id in number security_group_id in number);

Description:
This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session).

The typical use for this routine would be as part of the logic for launching a separate non-Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection.

You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session.

Arguments (input)
1. USER_ID - The User ID number.

SELECT *
FROM FND_USER
WHERE USER_NAME='SYSADMIN'

2. RESP_ID - The ID number of the responsibility.

SELECT *
FROM FND_RESPONSIBILITY_TL
WHERE RESPONSIBILITY_NAME ='Bills of Material'
AND LANGUAGE='US'

3. RESP_APPL_ID - The ID number of the application to which the responsibility belongs.

SELECT *
FROM FND_APPLICATION
WHERE APPLICATION_ID=702

4. SECURITY_GROUP_ID - The ID number of the security group. This argument is automatically defaulted by the API. The caller should not pass a value for it.

Example:
fnd_global.APPS_INITIALIZE (1010, 20417, 201);

Different types of Oracle Applications Updates.

2008-10-06T23:34:00.000-07:00

Oracle Applications organizes updates based on granularity:

One-off:
This is the most granular update required to change a particular behavior or fix a particular defect. These updates tend to be smaller, but due to product dependencies may be large.

Mini-pack:
This is an aggregation of all updates for a particular product, such as General Ledger (GL). The updates are aggregated into a single, easily applied patch that will take you to the latest code level for a particular product area. Mini-packs are named successively by letter. For example, 11i.GL.A, 11i.GL.B, etc. Mini-packs replace the previous concepts of Applications patchsets, mega-patches, or rollups.

Family Pack:
This is an aggregation of all updates for a particular family, such as the Procurement Family (PRC_PF), which is made up of Purchasing, Internet Procurement, Supplier Scheduling, and Internet Supplier Portal. The updates are aggregated into a single, easily applied patch that will take you to the latest code level for the entire product family.
Family packs are named successively by letter. For example, 11i.PRC_PF.F, 11i.PRC_PF.G, etc.

Maintenance Pack:
This is an aggregation of all updates for the entire E-Business Suite, including all ERP and CRM products. The updates are aggregated into a single, easily applied patch that will take you to the latest code level for the entire E-Business Suite. Maintenance Packs are numbered as point releases, 11.5.3, 11.5.4, 11.5.5, etc. Additionally, for customers not yet on 11i, the Rapid Install is repackaged at each Maintenance Pack level.

Oracle Applications updates can also be classified by type:

Interoperability patch:
This is an update to Oracle Applications code that will enable a change to the underlying technology stack. For example: Applications Release 11i interoperability patch for Oracle 9i, Applications Release 11i interoperability patch for Developer 6i patch 9, etc.

Diagnostic patch:
This type of patch is intended to gather additional information about a particular issue being encountered at a customer site. It is often used when the issue cannot be reproduced on-site at Oracle.

Translated update:
This is a translated version of a patch. It contains only the components requiring translation. Language independent portions of a base patch are not included in a translated patch, therefore you must always apply the base US patch as well as the translated patch.

Product Introduction:
This type of update introduces a new product to a release using the patching infrastructure. Examples include Oracle Process Manufacturing for Release 11.0, and Oracle Enterprise Asset Management for Release 11i.

Note: All patches can be downloaded via Oracle MetaLink for any supported platform or language. Customers can query a specific patch, or they can query for patches based on Release, Products, Platforms, or Languages. The search can also be limited to high priority patches, mini-packs, or patches not in the latest product mini-pack. The patch READMEs can also be viewed online.

Benefits of the Oracle E-Business Suite

2008-10-06T05:22:00.000-07:00

Process Automation with Enterprise Integration
The Oracle E-Business Suite provides comprehensive automation across your enterprise because all the applications comprising the suite are engineered to work together. Best-of-breed applications automate only bits and pieces of any given process, so marketing remains disconnected from sales, sales remains disconnected from finance, and so on.

Complete Automation
If your applications do not work together and do not automate business processes, then you will never get good information out of your system. If you automate sales, but not marketing, you will never know how effective your marketing is. If you automate sales, but not financials, you'll never know how many customers you sold to actually got invoiced. Because you don't know, you don't ask. If you don't ask, your business never gets more efficient. Without improved efficiency your business never spends less capital. Oracle E-Business Suite provides end-to-end transactional automation that gives you more for less.

Functionally Complete
The Oracle E-Business Suite integrates your business, capturing the information needed to provide a complete view of what’s going on in your business. Oracle’s suite is functionally complete with over 100 integrated product modules for every aspect of your business, including customer relationship management, finance, human resources, professional services automation, supply chain management, enterprise asset management, and project management.

Complete, Automated E-Business Flows
To enable rapid automation of key E-Business flows inherent in the E-Business Suite, Oracle provides FastForward Flows. These fixed-price, rapid-time services enable you to reap the benefits of Oracle's applications in record time by automating critical business processes within your enterprise and providing end-to-end integration.

Complete for Targeted Industries
The unique requirements of many targeted industries are included with the Oracle E-Business Suite. Oracle's complete offering meets the needs of: aerospace and defense, high tech, consumer packaged goods, automotive, pharmaceuticals, public sector, and many more industries.

Complete, Global Business Offering
The Oracle E-Business Suite is also globally comprehensive, supporting 30 languages, all character sets, unique global business practices, and local statutory and regulatory requirements. Oracle's applications enable you to unify information and to run your business consistently and accurately worldwide.

Oracle's Open, Modular System for Easier Integration
Many customers have implemented the E-Business Suite with zero customizations. All of Oracle's applications are engineered to work together, so you can significantly reduce your IT expenses by eliminating the need for software interfacing, data translation, and integration. If you implement one or several modules of the E-Business Suite, Oracle makes integration with your legacy and third-party applications easier since Oracle's software architecture is built on open, industry standards. To further reduce implementation costs, the Oracle E-Business Suite requires few, if any, customizations.

Complete Information With A Single Database
With other software vendors, you can spend millions of dollars on application software and still not have the information you need to run your business. Why? Information is spread throughout a company -- financials data is in one database, HR data in another, and so forth. This fragmented data makes it difficult and expensive to collaborate and access information across geographies and business processes.

Single, Complete Data Model Provides Better Information at a Lower Cost
Oracle's E-Business Suite is the first and only complete set of applications for the enterprise that are integrated around a single, comprehensive data model. Oracle provides a single definition of your customers, suppliers, employees, products -- all aspects of your business. Everyone in your company can access the same enterprise-wide data. Your marketing organization accesses the same customer information that your sales and order management departments are using.

One Database For Consolidated Information, Accessible by All
Companies need to seamlessly connect all their business processes, and store all their information in as few databases as possible, one being the ideal. The fewer databases you use to store your information, the easier it is to share information between organizations, enabling better co-operation and greater productivity across departments. What's more, it actually costs less to get better information! You can eliminate the duplicate data centers, hardware, and IT costs involved with multiple databases around the world.

Daily Business Close
For the first time you can access daily organizational metrics and use this data to drive change managing your businesses by fact, instead of by guesswork. Currently, businesses close their financial books every 30 days and report business metrics on a quarterly basis. At best you get critical, corporate-wide information only 4 times a year and have to wait an additional 2 weeks to get the data. The Oracle EBusiness Suite enables companies to do a Daily Business Close, delivering critical information for all lines of business via a personalized portal. You can now review daily, your lead positions, sales positions, inventory levels, headcount, and other key performance metrics.

With Oracle's complete suite and end-to-end process automation, every application has access to the same information. Every application is built using a single data model, providing a single source of truth for a daily business close. Information is continuously summarized and displayed in customizable portals, viewable by line of business, geography, department or product. And because the business intelligence system and the data sit in the same system, the information is real-time so you don't have to wait for data to pass through a separate data aggregation and analysis system. Building a data warehouse to consolidate fragmented data is time consuming, expensive, and still doesn't provide answers. If your business applications can't deliver daily key indicators for your business then perhaps you've got the wrong IT information architecture.

R12 HRMS: Single record insertion for Creating Employee

2008-10-05T22:39:00.001-07:00

DECLARE
ip_p_person_id NUMBER;
ip_p_assignment_id NUMBER;
ip_p_per_object_version_number NUMBER;
ip_p_asg_object_version_number NUMBER;
ip_p_per_effective_start_date DATE;
ip_p_per_effective_end_date DATE;
ip_p_full_name VARCHAR2 (1000);
ip_p_per_comment_id NUMBER;
ip_p_assignment_sequence NUMBER;
ip_p_assignment_number VARCHAR2 (1000);
ip_p_name_combination_warning BOOLEAN;
ip_p_assign_payroll_warning BOOLEAN;
ip_p_orig_hire_warning BOOLEAN;
ip_employee_number VARCHAR2 (1000);
BEGIN
--ip_employee_number := 'Apps-101';
hr_employee_api.create_employee
(p_hire_date => SYSDATE,
p_business_group_id => 202,
p_last_name => 'Arun',
p_sex => 'M',
p_person_type_id => 13,
p_per_comments => NULL,
p_date_employee_data_verified => NULL,
p_date_of_birth => TO_DATE
('01-April-84',
'DD-MONTH-RR'
),
p_email_address => 'arun_apps@yahoo.com',
p_employee_number => ip_employee_number,
p_expense_check_send_to_addres => NULL,
p_first_name => 'Rathod',
p_known_as => 'Arun',
p_marital_status => 'S',
p_middle_names => NULL,
p_nationality => 'IND',
p_national_identifier => NULL,
p_previous_last_name => NULL,
p_registered_disabled_flag => NULL,
p_title => 'MR.',
p_vendor_id => NULL,
p_work_telephone => '781-1234-456',
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_per_information_category => NULL,
p_per_information1 => NULL,
p_per_information2 => NULL,
p_per_information3 => NULL,
p_per_information4 => NULL,
p_per_information5 => NULL,
p_per_information6 => NULL,
p_per_information7 => NULL,
p_per_information8 => NULL,
p_per_information9 => NULL,
p_per_information10 => NULL,
p_per_information11 => NULL,
p_per_information12 => NULL,
p_per_information13 => NULL,
p_per_information14 => NULL,
p_per_information15 => NULL,
p_per_information16 => NULL,
p_per_information17 => NULL,
p_per_information18 => NULL,
p_per_information19 => NULL,
p_per_information20 => NULL,
p_per_information21 => NULL,
p_per_information22 => NULL,
p_per_information23 => NULL,
p_per_information24 => NULL,
p_per_information25 => NULL,
p_per_information26 => NULL,
p_per_information27 => NULL,
p_per_information28 => NULL,
p_per_information29 => NULL,
p_per_information30 => NULL,
p_date_of_death => NULL,
p_background_check_status => 'N',
p_background_date_check => NULL,
p_blood_type => 'B+',
p_correspondence_language => NULL,
p_fast_path_employee => NULL,
p_fte_capacity => NULL,
p_honors => NULL,
p_internal_location => NULL,
p_last_medical_test_by => NULL,
p_last_medical_test_date => NULL,
p_mailstop => NULL,
p_office_number => '781-2345-567',
p_on_military_service => NULL,
p_pre_name_adjunct => NULL,
p_rehire_recommendation => NULL,
p_projected_start_date => NULL,
p_resume_exists => NULL,
p_resume_last_updated => NULL,
p_second_passport_exists => NULL,
p_student_status => NULL,
p_work_schedule => NULL,
p_suffix => NULL,
p_benefit_group_id => NULL,
p_receipt_of_death_cert_date => NULL,
p_coord_ben_med_pln_no => NULL,
p_coord_ben_no_cvg_flag => NULL,
p_coord_ben_med_ext_er => NULL,
p_coord_ben_med_pl_name => NULL,
p_coord_ben_med_insr_crr_name => NULL,
p_coord_ben_med_insr_crr_ident => NULL,
p_coord_ben_med_cvg_strt_dt => NULL,
p_coord_ben_med_cvg_end_dt => NULL,
p_uses_tobacco_flag => NULL,
p_dpdnt_adoption_date => NULL,
p_dpdnt_vlntry_svce_flag => NULL,
p_original_date_of_hire => NULL,
p_adjusted_svc_date => NULL,
p_town_of_birth => NULL,
p_region_of_birth => NULL,
p_country_of_birth => 'IN',
p_global_person_id => NULL,
p_party_id => NULL,
p_person_id => ip_p_person_id,
p_assignment_id => ip_p_assignment_id,
p_per_object_version_number => ip_p_per_object_version_number,
p_asg_object_version_number => ip_p_asg_object_version_number,
p_per_effective_start_date => ip_p_per_effective_start_date,
p_per_effective_end_date => ip_p_per_effective_end_date,
p_full_name => ip_p_full_name,
p_per_comment_id => ip_p_per_comment_id,
p_assignment_sequence => ip_p_assignment_sequence,
p_assignment_number => ip_p_assignment_number,
p_name_combination_warning => ip_p_name_combination_warning,
p_assign_payroll_warning => ip_p_assign_payroll_warning,
p_orig_hire_warning => ip_p_orig_hire_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;

R12 HRMS: Single record insertion for Address

2008-10-05T22:38:00.000-07:00

DECLARE
ip_p_address_id NUMBER;
ip_p_object_version_number NUMBER;
BEGIN
hr_person_address_api.create_person_address
(p_validate => NULL,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => 27754,
p_primary_flag => 'Y',
p_style => 'US',
p_date_from => '23-jan-1980',
p_date_to => SYSDATE,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => '5675 Clay Street',
p_address_line2 => NULL,
p_address_line3 => NULL,
p_town_or_city => 'Redwood Shores',
p_region_1 => 'San Mateo',
p_region_2 => 'CA',
p_region_3 => NULL,
p_postal_code => 94065,
p_country => 'US',
p_telephone_number_1 => '650-778-6065',
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
p_party_id => NULL,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;

R12 HRMS: Single record insertion for Assignment

2008-10-05T22:36:00.002-07:00

DECLARE
l_cagr_grade_def_id NUMBER;
l_cagr_concatenated_segments VARCHAR2 (30);
l_concatenated_segments VARCHAR2 (280);
l_soft_coding_keyflex_id NUMBER;
l_comment_id NUMBER (15);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_no_managers_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_hourly_salaried_warning BOOLEAN;
l_gsp_post_process_warning VARCHAR2 (30);
l_api_mode VARCHAR2 (20);
l_object_version_number NUMBER;
l_assignment_id NUMBER;
BEGIN
l_api_mode := 'CORRECTION';
l_object_version_number := 2;
l_soft_coding_keyflex_id := 91;
hr_assignment_api.update_emp_asg
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => l_api_mode,
p_assignment_id => 27920,
p_object_version_number => l_object_version_number,
--l_object_version_number,
p_assignment_number => NULL,
p_change_reason => 'WS',
p_assignment_status_type_id => 1,
p_comments => NULL,
p_date_probation_end => NULL,
p_default_code_comb_id => NULL,
p_frequency => 'W',
p_internal_address_line => NULL,
p_manager_flag => 'Y',
p_normal_hours => 40,
p_perf_review_period => 1,
p_perf_review_period_frequency => 'Y',
p_probation_period => NULL,
p_probation_unit => NULL,
p_sal_review_period => 1,
p_sal_review_period_frequency => 'Y',
p_set_of_books_id => NULL,
p_source_type => NULL,
p_time_normal_finish => NULL,
p_time_normal_start => NULL,
p_bargaining_unit_code => NULL,
p_labour_union_member_flag => NULL,
p_hourly_salaried_code => NULL,
--emp_upload_rec.rate_type,
p_ass_attribute_category => NULL,
p_ass_attribute1 => NULL,
p_ass_attribute2 => NULL,
p_ass_attribute3 => NULL,
p_ass_attribute4 => NULL,
p_ass_attribute5 => NULL,
p_ass_attribute6 => NULL,
p_ass_attribute7 => NULL,
p_ass_attribute8 => NULL,
p_ass_attribute9 => NULL,
p_ass_attribute10 => NULL,
p_ass_attribute11 => NULL,
p_ass_attribute12 => NULL,
p_ass_attribute13 => NULL,
p_ass_attribute14 => NULL,
p_ass_attribute15 => NULL,
p_ass_attribute16 => NULL,
p_ass_attribute17 => NULL,
p_ass_attribute18 => NULL,
p_ass_attribute19 => NULL,
p_ass_attribute20 => NULL,
p_ass_attribute21 => NULL,
p_ass_attribute22 => NULL,
p_ass_attribute23 => NULL,
p_ass_attribute24 => NULL,
p_ass_attribute25 => NULL,
p_ass_attribute26 => NULL,
p_ass_attribute27 => NULL,
p_ass_attribute28 => NULL,
p_ass_attribute29 => NULL,
p_ass_attribute30 => NULL,
p_title => NULL,
p_segment1 => NULL,
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => NULL,
p_contract_id => NULL,
p_establishment_id => NULL,
p_collective_agreement_id => NULL,
p_cagr_id_flex_num => NULL,
p_cag_segment1 => NULL,
p_cag_segment2 => NULL,
p_cag_segment3 => NULL,
p_cag_segment4 => NULL,
p_cag_segment5 => NULL,
p_cag_segment6 => NULL,
p_cag_segment7 => NULL,
p_cag_segment8 => NULL,
p_cag_segment9 => NULL,
p_cag_segment10 => NULL,
p_cag_segment11 => NULL,
p_cag_segment12 => NULL,
p_cag_segment13 => NULL,
p_cag_segment14 => NULL,
p_cag_segment15 => NULL,
p_cag_segment16 => NULL,
p_cag_segment17 => NULL,
p_cag_segment18 => NULL,
p_cag_segment19 => NULL,
p_cag_segment20 => NULL,
p_notice_period => NULL,
p_notice_period_uom => NULL,
p_employee_category => NULL,
p_work_at_home => NULL,
p_job_post_source_name => NULL,
p_supervisor_assignment_id => NULL,
p_cagr_grade_def_id => l_cagr_grade_def_id,
p_cagr_concatenated_segments => l_cagr_concatenated_segments,
p_concatenated_segments => l_concatenated_segments,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning,
p_hourly_salaried_warning => l_hourly_salaried_warning,
p_gsp_post_process_warning => l_gsp_post_process_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;

R12 HRMS: Single record insertion for Assignment(Jobs, Position..)

2008-10-05T22:36:00.001-07:00

DECLARE
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (500);
l_tax_district_changed_warning BOOLEAN;
l_object_version_number NUMBER;
l_people_group_id NUMBER;
l_special_ceiling_step_id NUMBER;
l_group_name VARCHAR2 (500);
l_effective_start_date DATE;
l_effective_end_date DATE;
BEGIN
--l_people_group_id := 155;
l_object_version_number := 3;
--l_group_name := '1A';
hr_assignment_api.update_emp_asg_criteria
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => 27920,
p_object_version_number => l_object_version_number,
p_job_id => 18,
p_grade_id => 37,
p_position_id => NULL,
p_organization_id => 202,
p_location_id => 206,
p_pay_basis_id => 2,
p_payroll_id => 155,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_people_group_id => l_people_group_id,
--emp_upload_rec.people_group_id,
p_segment1 => NULL,
p_segment2 => NULL,
p_segment3 => NULL,
p_segment4 => NULL,
p_segment5 => NULL,
p_segment6 => NULL,
p_segment7 => NULL,
p_segment8 => NULL,
p_segment9 => NULL,
p_segment10 => NULL,
p_segment11 => NULL,
p_segment12 => NULL,
p_segment13 => NULL,
p_segment14 => NULL,
p_segment15 => NULL,
p_segment16 => NULL,
p_segment17 => NULL,
p_segment18 => NULL,
p_segment19 => NULL,
p_segment20 => NULL,
p_segment21 => NULL,
p_segment22 => NULL,
p_segment23 => NULL,
p_segment24 => NULL,
p_segment25 => NULL,
p_segment26 => NULL,
p_segment27 => NULL,
p_segment28 => NULL,
p_segment29 => NULL,
p_segment30 => NULL,
p_concat_segments => '1A.Boston.No....',
p_employment_category => 'FR',
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;

R12 HRMS: Single record insertion for salary

2008-10-05T22:35:00.000-07:00

DECLARE
l_element_entry_id NUMBER := NULL;
l_inv_next_sal_date_warning BOOLEAN;
l_proposed_salary_warning BOOLEAN;
l_approved_warning BOOLEAN;
l_payroll_warning BOOLEAN;
l_perf_object_version_number NUMBER;
l_pay_proposal_id NUMBER (15);
l_assignment_id NUMBER;
l_performance_review_id NUMBER := NULL;
BEGIN
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id,
p_assignment_id => 27920,
p_business_group_id => 202,
p_change_date => TRUNC (SYSDATE),
p_comments => NULL,
p_next_sal_review_date => TRUNC (SYSDATE + 30),
p_proposal_reason => 'NEWH',
p_proposed_salary_n => 10000,
p_forced_ranking => NULL,
p_performance_review_id => l_performance_review_id,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_object_version_number => l_perf_object_version_number,
p_multiple_components => 'N',
p_approved => 'Y',
p_validate => FALSE,
p_element_entry_id => l_element_entry_id,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;

Bills of Material

2008-10-05T01:23:00.000-07:00

What is Bills of Material?

The Bill of Material is a list of all items that make an assembly, and is structured level by level so as to show the parent/component relationship.

Let us take the simple case of a bicycle. It is made of two wheels. Here the bicycle is the parent and wheel is its component. The wheel consists of a rim. The wheel here is the parent item and the rim is the component (child). Thus the first level of the BOM is Bicycle, the second level is Wheel, and the third level is Rim. You could even continue further to the level of Spokes. Thus, depending upon the item, one can go further and further down in the BOM level.

However, the maximum BOM level that Oracle supports is 60.

We use BOM for various purposes, namely:
- To manufacture assemblies and sub-assemblies
- To configure sales orders
- To calculate standard cost
- To forecast (planning)

BOM Conversion
First before stepping into boms we have to seriously study the client requirement. Once we are through with the client requirement, we have to check the legacy data that is provided by the client. The obtained legacy data should be entered into the staging table, the column of the staging table will be the same as our legacy data columns, and also we will be given the columns and data for two mapping tables by the client.

We need to construct a convertion staging table as xx_bom_bill_of_mtls_conv_stg, this staging table contains the following columns. The conversion staging table is as fallows

row_id
old_organization_code
new_organization_code
organization_id
old_assembly_item_number
new_assembly_item_number
assembly_item_id
old_operation_seq_num
new_operation_seq_num
old_component_item_number
new_component_item_number
component_item_id
item_num
component_quantity
component_yield_factor
effectivity_date
disable_date
planning_factor
wip_supply_type_meaning
wip_supply_type
supply_subinventory
supply_locator_code
supply_locator_id
operation_lead_time_percent
status_flag

Some of the validations to be performed are

1. Validating the orgnization_code for null
2. Validating the organization_code and geting the organization_id from mtl_parameters
3. Validating the assembly_item_number and getting the related inventory_item_id
4. Validating component_item_number for null
5. Validating and getting the inventory_item_id for component_item_number
6. Validating the wip_supply_type_meaning and getting the corresponding wip_supply_type
7. Validating the supply_locator_code and getting the supply_locator_id related to supply_locator_code