Setup to perform OA Page personalization

To perform OA Page personalization a profile option (“Personaliz Self-Service Defn”) has to be assigned to a user login.
Navigation: “Application Administrator” Responsibility --> Application Administrator -->Profile
Here in this screen select ‘User’ Checkbox and then select your login user name and Select the Profile option “Personaliz Self-Service Defn” in the Profile text box. After selecting the profile option, click on Find button. This action opens System profile options screen.
In the screen select ‘Yes’ Option from the LOV corresponding to the user column.
Then save and close the form.

How to get table information? How to get About this Page link in OA page in R12

How to get table information? How to get About this Page link in OA page in R12

Applies to:
OA Pages Version: 12.0.0
Information in this document applies to any platform.

Goal
Q1: How to find supplier related view / table information in web page environment?
Q2: How to get the About this Page link in OA page?

Solution
A1: Please follow the following steps to get the Supplier related table information:
> Navigate to the Supplier Page
> Click on the About this page link
> Click Expand All
> You will be seeing SuppSrchVO, SupplierVO and SitesVO
> If you click on that view it will show you the query used.

A2: To get the About this Page Link:

Please ensure the below Profile option is set to Yes at site level:
FND: Diagnostics
System Administrator > Profile > System

The FND: Diagnostics profile option controls whether the Diagnostics button is rendered.
It also controls the display of the About this Page link.

You may also set below

Personalize Self-Service Defn : Yes
FND: Personalization Region Link Enabled : Yes
FND: Diagnostics : Yes

Add the below responsibilities to the user:
System Administrator > Security > User > Define

1. FND Html Forms
2. Functional Administrator
3. Functional Developer
4. Once done bounce the apache
5. Retest the issue
6. Migrate the solution to other environments as appropriate.

iRecruitment workflow

How to view Item Key and Workflow internal name for iRecruitment vacancy related workflows

SELECT transaction_document
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9876;

(drop the 'IRC' prefix when using transaction_ref_id).

The following query will return you item_type,item_key thorugh which you can search the workflow

SELECT item_type,item_key
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9204;

select * from PER_ALL_VACANCIES
where name ='IRC9204'

How to clear the Cache in R12 JSP Pages

How to clear the Cache in R12 JSP Pages

Logon to oracle applications than navigate to

Functional Administrator-->Home--> Click on 'Core Services'--> click on 'Caching Framework'

Here you can clear Cache.

This is useful when you add responsibilities to your user or want to clear any cache

Operating Unit and Inventory Organization link

Query which gives Operating Unit Information and corresponding Inventory Orgs related information also

SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC

How to Delegate to enter the expense for some other person

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.

Tehnical Flow of OM

Understanding data flow for “Standard Order”


1. Order Entry
This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.
•oe_order_headers_all (Here the flow_status_code as entered)
•oe_order_lines_all (flow_status_code as entered) ( order number is generated)

2.Order Booking
This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.
•oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
•oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated)
•wsh_new_deliveries (status_code OP open)
•wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.
Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:
•oe_order_lines_all (flow_status_code ‘PICKED’ )
•wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
•mtl_txn_request_headers
•mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
•Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

5.Pick Confirm
Items are transferred from saleble to staging Subinventory.
•mtl_material_transactions
•mtl_transaction_accounts
•wsh_delivery_details (released_status ‘Y’‘Released’ )
•wsh_delivery_assignments

6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries
•oe_order_lines_all (flow_status_code ‘shipped’)
•wsh_delivery_details (released_status ‘C’ ‘Shipped’)
•mtl_transaction_interface
•mtl_material_transactions(linked through Transaction source header id)
•mtl_transaction_accounts
•Data deleted from mtl_demand,mtl_reservations
•Item deducted from mtl_onhand_quantities

7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details.
•Invoicing workflow activity transfers shipped item information to Oracle Receivables.
•ra_interface_lines_all (interface table into which the data is transferred from order management)T
•Then Autoinvoice program imports data from this
•Table which get affected into this stage are recievables base table.
ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)

8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.
•oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.
These are the table get affected in this step.
•oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)
These are the typically data flow of a order to cash model for a standard order.

Oracle XML Publisher Bursting Sample Program

Here we are trying to send the PDF output to Server Output Folder.

xapi:delivery - Explains where the output should be delivered
xapi:filesystem - explains the output is destined to filesystem
xapi:template - specifies the template shortcode prefixed with app short code, for eg: AR.INVOICE - AR-app short code and INVOICE is the xml template short code.

location="xdo://AR.INVOICE.en.US/?getSource=true"

Above line is very important, it takes care of pulling the latest template uploaded in the XML Publisher Administrator responsibility, you don't need to put your template hanging in any UNIX servers.

en - Language
US - Territory code (when you create template definition, you make sure that you select United States or other country)

The same above example holds good for sending the output through email, fax, etc.,
By: chandramouly

How to Create a Barcode in Oracle Reports

How to Create a Barcode in Oracle Reports please view below link


http://www.youtube.com/watch?v=vvQOp1YmOIc

views in Oracle Property Manager Module

List of views in Oracle Property Manager Module

PN_ACT_RENT_DETAILS_V
PN_ADDRESSES_ALL_DFV
PN_ADJUSTMENT_DETAILS_V
PN_AEL_BILL_GL_V
PN_AEL_PAY_GL_V
PN_BUILDINGS_V
PN_COMPANIES_ALL_DFV
PN_COMPANIES_V
PN_COMPANY_SITES_ALL_DFV
PN_COMPANY_SITES_V
PN_CONTACT_ASSIGN_HISTORY_V
PN_CONTACT_ASSIGNMENTS_ALL_DFV
PN_CONTACT_ASSIGNMENTS_V
PN_CONTACTS_ALL_DFV
PN_CONTACTS_V
PN_DISTRIBUTIONS_ALL_DFV
PN_DISTRIBUTIONS_HISTORY_V
PN_DISTRIBUTIONS_V
PN_EXCLUDE_TERMS_V
PN_EXP_PAYMENT_ITEMS_V
PN_FLOORS_V
PN_FOR_RENT_DETAILS_V
PN_GL_PERIOD_V
PN_INDEX_EXCLUDE_TERM_V
PN_INDEX_HISTORY_HEADERS_DFV
PN_INDEX_HISTORY_HEADERS_V
PN_INDEX_HISTORY_LINES_V
PN_INDEX_LEASE_CONSTRAINTS_DFV
PN_INDEX_LEASE_CONSTRAINTS_V
PN_INDEX_LEASE_PERIODS_ALL_DFV
PN_INDEX_LEASE_PERIODS_V
PN_INDEX_LEASES_ALL_DFV
PN_INDEX_LEASES_V
PN_INSUR_REQUIRE_HISTORY_V
PN_INSURANCE_REQUIREMENTS__DFV
PN_INSURANCE_REQUIREMNTS_V
PN_JE_CATEGORIES_V
PN_LANDLORD_SERVICE_HISTORY_V
PN_LANDLORD_SERVICES_ALL_DFV
PN_LANDLORD_SERVICES_V
PN_LEASE_CHANGES_ALL_DFV
PN_LEASE_CHANGES_V
PN_LEASE_CONTACT_ASSIGN_V
PN_LEASE_DETAILS_HISTORY_V
PN_LEASE_DETAILS_V
PN_LEASE_MILESTONES_ALL_DFV
PN_LEASE_MILESTONES_V
PN_LEASE_TRANSACTIONS_V
PN_LEASES_V
PN_LOC_ACC_MAP_HDR_V
PN_LOC_ACC_MAP_V
PN_LOCATION_CONTACT_ASSIGN_V
PN_LOCATION_FEATURES_ALL_DFV
PN_LOCATION_FEATURES_V
PN_LOCATION_PARKS_DFV
PN_LOCATION_PARKS_VL
PN_LOCATIONS_ALL_DFV
PN_LOCATIONS_ITF_V
PN_LOCATIONS_PUB_V
PN_LOCATIONS_V
PN_NOTE_DETAILS_VL
PN_NOTE_HEADERS_V
PN_OFFICES_V
PN_OPTIONS_ALL_DFV
PN_OPTIONS_HISTORY_V
PN_OPTIONS_V
PN_PAY_GROUP_BYS_V
PN_PAY_GROUP_RULES_V
PN_PAYMENT_ITEMS_V
PN_PAYMENT_SCHEDULES_ALL_DFV
PN_PAYMENT_SCHEDULES_V
PN_PAYMENT_TERMS_ALL_DFV
PN_PAYMENT_TERMS_HISTORY_V
PN_PAYMENT_TERMS_V
PN_PBUILDING_V
PN_PHONES_ALL_DFV
PN_PHONES_V
PN_PLAND_V
PN_PROPERTIES_ALL_DFV
PN_PROPERTIES_V
PN_RIGHTS_ALL_DFV
PN_RIGHTS_HISTORY_V
PN_RIGHTS_V
PN_SET_MILESTONES_VL
PN_SET_TYPES_VL
PN_SPACE_ALLOCATIONS_ALL_DFV
PN_SPACE_ALLOCATIONS_V
PN_SPACE_ASSIGN_CUST_ALL_DFV
PN_SPACE_ASSIGN_CUST_PUB_V
PN_SPACE_ASSIGN_CUST_V
PN_SPACE_ASSIGN_EMP_ALL_DFV
PN_SPACE_ASSIGN_EMP_PUB_V
PN_SPACE_ASSIGN_EMP_V
PN_TENANCIES_ALL_DFV
PN_TENANCIES_HISTORY_V
PN_TENANCIES_V
PN_TERM_HISTORY_HEADER_V
PN_TERM_TEMPLATES_ALL_DFV
PN_TERM_TEMPLATES_V
PN_TRX_LEASE_EXPENSE
PN_TRX_LEASE_REVENUE
PN_VAR_ABAT_DEFAULTS_V
PN_VAR_BKDT_DEFAULTS_V
PN_VAR_BKHD_DEFAULTS_V
PN_VAR_BKPTS_DET_ALL_DFV
PN_VAR_BKPTS_DET_V
PN_VAR_BKPTS_HEAD_ALL_DFV
PN_VAR_BKPTS_HEAD_V
PN_VAR_CONSTR_DEFAULTS_V
PN_VAR_CONSTRAINTS_ALL_DFV
PN_VAR_CONSTRAINTS_V
PN_VAR_DEDUCTIONS_ALL_DFV
PN_VAR_DEDUCTIONS_V
PN_VAR_LINE_DEFAULTS_V
PN_VAR_LINE_TEMPLATES_V
PN_VAR_LINES_ALL_DFV
PN_VAR_LINES_DATES_V
PN_VAR_LINES_V
PN_VAR_PERIODS_ALL_DFV
PN_VAR_PERIODS_DATES_V
PN_VAR_PERIODS_V
PN_VAR_RENT_ADJ_V
PN_VAR_RENT_DATES_V
PN_VAR_RENT_DETAILS_V
PN_VAR_RENT_INV_ALL_DFV
PN_VAR_RENT_INV_V
PN_VAR_RENT_SUMM_V
PN_VAR_RENTS_ALL_DFV
PN_VAR_RENTS_V
PN_VAR_SUMM_DET_V
PN_VAR_TEMPLATES_V
PN_VAR_TERMS_V
PN_VAR_VOL_HIST_ALL_DFV
PN_VAR_VOL_HIST_V
PN_XLA_EXTRACT_HEADERS_V
PN_XLA_EXTRACT_LINES_V

Oracle Property Manager Tables

Oracle Property Manager Tables:

PN_ACCOUNTING_EVENTS_ALL
PN_ADDRESSES_ALL
PN_ADJUSTMENT_DETAILS
PN_ADJUSTMENT_SUMMARIES
PN_AE_HEADERS_ALL
PN_AE_LINES_ALL
PN_COMPANIES_ALL
PN_COMPANY_SITES_ALL
PN_CONTACTS_ALL
PN_CONTACT_ASSIGNMENTS_ALL
PN_CONTACT_ASSIGN_HISTORY
PN_CURRENCIES
PN_DISTRIBUTIONS_ALL
PN_DISTRIBUTIONS_HISTORY
PN_EMP_SPACE_ASSIGN_ITF
PN_INDEX_EXCLUDE_TERM_ALL
PN_INDEX_HISTORY_HEADERS
PN_INDEX_HISTORY_LINES
PN_INDEX_LEASES_ALL
PN_INDEX_LEASE_CONSTRAINTS_ALL
PN_INDEX_LEASE_PERIODS_ALL
PN_INDEX_LEASE_TERMS_ALL
PN_INSURANCE_REQUIREMENTS_ALL
PN_INSUR_REQUIRE_HISTORY
PN_LANDLORD_SERVICES_ALL
PN_LANDLORD_SERVICE_HISTORY
PN_LEASES_ALL
PN_LEASE_CHANGES_ALL
PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY
PN_LEASE_MILESTONES_ALL
PN_LEASE_OPTIONS_ITF
PN_LEASE_TRANSACTIONS_ALL
PN_LOCATIONS_ALL
PN_LOCATIONS_ITF
PN_LOCATION_FEATURES_ALL
PN_LOCATION_PARKS
PN_LOC_ACC_MAP_ALL
PN_LOC_ACC_MAP_HDR_ALL
PN_MILESTONES_ITF
PN_NOTE_DETAILS
PN_NOTE_HEADERS
PN_OPTIONS_ALL
PN_OPTIONS_HISTORY
PN_PAYMENT_ITEMS_ALL
PN_PAYMENT_ITEMS_EFC
PN_PAYMENT_SCHEDULES_ALL
PN_PAYMENT_TERMS_ALL
PN_PAYMENT_TERMS_EFC
PN_PAYMENT_TERMS_HISTORY
PN_PAY_GROUP_BYS
PN_PAY_GROUP_RULES
PN_PHONES_ALL
PN_PROPERTIES_ALL
PN_REC_AGREEMENTS_ALL
PN_REC_AGR_LINABAT_ALL
PN_REC_AGR_LINAREA_ALL
PN_REC_AGR_LINCONST_ALL
PN_REC_AGR_LINES_ALL
PN_REC_AGR_LINEXP_ALL
PN_REC_ARCL_ALL
PN_REC_ARCL_DTLLN_ALL
PN_REC_ARCL_DTL_ALL
PN_REC_ARCL_EXC_ALL
PN_REC_CALC_PERIODS_ALL
PN_REC_EXPCL_ALL
PN_REC_EXPCL_DTLACC_ALL
PN_REC_EXPCL_DTLLN_ALL
PN_REC_EXPCL_DTL_ALL
PN_REC_EXPCL_INC_ALL
PN_REC_EXPCL_TYPE_ALL
PN_REC_EXP_ITF
PN_REC_EXP_LINE_ALL
PN_REC_EXP_LINE_DTL_ALL
PN_REC_LINBILL_ALL
PN_REC_PERIOD_BILL_ALL
PN_REC_PERIOD_LINES_ALL
PN_RENT_ROLL_LEASE_EXP_ITF
PN_RIGHTS_ALL
PN_RIGHTS_HISTORY
PN_SET_MILESTONES
PN_SET_TYPES
PN_SPACE_ALLOCATIONS_ALL
PN_SPACE_ALLOC_ITF
PN_SPACE_ASSIGN_CUST_ALL
PN_SPACE_ASSIGN_EMP_ALL
PN_SPACE_ASSIGN_LEASE_ITF
PN_SPACE_ASSIGN_LOC_ITF
PN_SPACE_UTIL_LEASE_ITF
PN_SPACE_UTIL_LOC_ITF
PN_SYSTEM_SETUP_OPTIONS
PN_TENANCIES_ALL
PN_TENANCIES_HISTORY
PN_TERM_TEMPLATES_ALL
PN_VAR_ABATEMENTS_ALL
PN_VAR_BKPTS_DET_ALL
PN_VAR_BKPTS_HEAD_ALL
PN_VAR_CONSTRAINTS_ALL
PN_VAR_DEDUCTIONS_ALL
PN_VAR_GRP_DATES_ALL
PN_VAR_LINES_ALL
PN_VAR_PERIODS_ALL
PN_VAR_RENTS_ALL
PN_VAR_RENT_DATES_ALL
PN_VAR_RENT_INV_ALL
PN_VAR_RENT_SUMM_ALL
PN_VAR_VOL_HIST_ALL
PN_VOL_HIST_BATCH_ITF
PN_VOL_HIST_LINES_ITF

Combining and Compressing Files at UNIX

Combining and Compressing Files

Create a tarfile command : tar cf file.tar file1 file2 ...fileN
tar combines files but does not compress
Create a zipfile zip filename
Unzip a file unzip filename


In Unix, how do I create or decompress zip files?
To create a zip file, at the Unix prompt, enter:
zip filename inputfile1 inputfile2

Replace filename with the name you want to give the zip file. The .zip extension is automatically appended to the end of the filename.
Replace inputfile1 and inputfile2 with the names of the files you wish to include in the zip archive. You can include any number of files here,
or you may use an asterisk (*) to include all files in the current directory.

To include the contents of a directory or directories in a zip archive, use the -r flag:
zip -r filename directory

Replace directory with the name of the directory you want to include. This will create the archive filename.zip that contains the files and
subdirectories of directory.

Files created by zip can normally be decoded by programs such as WinZip and StuffIt Expander.

To decompress a zip file in Unix, use the unzip command. At the Unix prompt, enter:
unzip filename Replace filename with the name of the zip archive.

For more information about zip and unzip, see their manual pages:
man zip
man unzip

R12 Banks Queries

R12 Banks Queries

SELECT aps.vendor_name "VERDOR NAME",
apss.vendor_site_code "VENDOR SITE CODE",
ieb.bank_name "BANK NAME",
iebb.bank_branch_name "BANK BRANCH NAME",
iebb.branch_number "BRANCH NUMBER",
ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE aps.vendor_id = apss.vendor_id
and iao.account_owner_party_id = aps.party_id
and ieba.ext_bank_account_id = iao.ext_bank_account_id
and ieb.bank_party_id = iebb.bank_party_id
and ieba.branch_id = iebb.branch_party_id
and ieba.bank_id = ieb.bank_party_id;


SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

Delete Duplicate Records in Oracle

Delete Duplicate Records in Oracle

There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);

INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);

SELECT * FROM dup_test;
Use subquery to delete duplicate rows:

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:

You can also detect and delete duplicate rows using Oracle analytic functions:

DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:

This is the simplest method to remove duplicity.

CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.

Supplier Conversion in R12

Supplier Conversion in R12
Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.

Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).

The Interface Tables are:

AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIERS_INT:

This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.

Mandatory Columns:

VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
VENDOR_NAME – Supplier name
Other important columns:

SEGMENT1 – Supplier Number
VENDOR_TYPE_LOOKUP_CODE – Supplier type
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
TERMS_NAME – Payment terms name
TAX_VERIFICATION_DATE – Tax verification date(1099)
VAT_REGISTRATION_NUM – Tax registration number
ATTRIBUTE1 -15 – Descriptive Flexfield Segments
PAY_GROUP_LOOKUP_CODE – Payment group type
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
NUM_1099 – Tax identification number (1099)
VAT_CODE – Default invoice tax code
HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
SUMMARY_FLAG – Key flexfield summary flag
ENABLED_FLAG – Key flexfield enable flag
EMPLOYEE_ID – Employee unique identifier if supplier is an employee
AP_SUPPLIER_SITES_INT:

This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.

Mandatory Columns:

VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:

ADDRESS_LINE1 – First line of supplier address
ADDRESS_LINE2 – Second line of supplier address
ADDRESS_LINE3 – Third line of supplier address
CITY – City name
STATE – State name or abbreviation
ZIP – Postal code
COUNTRY – Country name
PHONE – Phone number
FAX – Supplier site facsimile number
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
VAT_CODE – Invoice default tax code
PAY_GROUP_LOOKUP_CODE – Payment group type
TERMS_NAME – Payment terms name
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
EMAIL_ADDRESS – E-mail address of the supplier contact
PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold
AP_SUP_SITE_CONTACT_INT:

This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE and ORG_ID.

Mandatory Columns:

VENDOR_INTERFACE_ID – Supplier interface record unique identifier
VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:

FIRST_NAME – Contact First name
LAST_NAME – Contact last name
AREA_CODE – Area code of contact phone number
PHONE – Contact phone number
FIRST_NAME_ALT – Alternate Supplier contact first name
LAST_NAME_ALT – Alternate Supplier contact last name
EMAIL_ADDRESS – Email address for the Supplier Site contact
FAX – Facsimile number for the Supplier Site contact
VENDOR_ID – Supplier unique identifier
Validations:

Vendor Number (Check for duplicate records in ap_suppliers table)
Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
Terms Name (Check for proper record in ap_terms_tl table)
Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
Employee Id (Check for proper employee record in per_all_people_f table)
Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
Country Code ( Check for proper country code in fnd_territories_vl table)
Payment Method (Check for proper payment method in iby_payment_methods_vl table)
Interface programs:

Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
The data inserted via these interfaces are automatically populated into TCA tables.

Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.

Oracle R12 Banks Accounts Query

Oracle R12 Bank Accounts Query

SELECT owners.account_owner_party_id, asp.segment1 vendor_num,
asp.vendor_name,
(SELECT NAME
FROM hr_all_organization_units
WHERE TYPE = 'OU' AND organization_id = asa.org_id) ou_name,
asa.vendor_site_code, ieb.country_code
--, (select bank_name from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_name
--, (select bank_number from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_number
--, ieb.branch_id
, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name,
cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code,
ieb.bank_account_num, ieb.currency_code, ieb.iban,
ieb.foreign_payment_use_flag,
ieb.bank_account_name_alt bankacct_fromdata
FROM iby_pmt_instr_uses_all instrument,
iby_account_owners owners,
iby_external_payees_all payees,
iby_ext_bank_accounts ieb,
ap_supplier_sites_all asa,
ap_suppliers asp,
ce_bank_branches_v cbbv
WHERE owners.primary_flag = 'Y'
AND owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id
AND payees.ext_payee_id = instrument.ext_pmt_party_id
AND payees.payee_party_id = owners.account_owner_party_id
AND payees.supplier_site_id = asa.vendor_site_id
AND asa.vendor_id = asp.vendor_id
AND cbbv.branch_party_id(+) = ieb.branch_id
--and asa.org_id in (125)
AND asp.created_by = 1451
ORDER BY 4, 1, 3

Debugging Concurrent Programs

Concurrent Programs

For concurrent programs you can place in Log file / output file / fnd_log_messages table


For Log File
FND_FILE.PUT_LINE(FND_FILE.LOG,'your log statement');


For Output File
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'your output statement');


For Tracing it in a table FND_LOG_MESSAGES


fnd_log.string(log_level => fnd_log.level_statement
,module => 'custom module'
,message => 'you log message here');

Workflow Mailer Debugging Script for Debugging Emails issues and event subscriptions

Workflow Mailer Debugging Script for Debugging Emails issues and event subscriptions
This article containts various Workflow and Business Event debugging scripts.


--Checking workflow Components status wheather are they running or stopped.
select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
from fnd_svc_components
where component_type like 'WF%'
order by 1 desc,2,3;


--Query to get the log file of active workflow mailer and workflow agent listener Container
--Note All Workflow Agent Components logs will stored in single file ie. container log file.
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;



--Linux Shell script Command to get outbound error in Mailer
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix

--Linux Shell script Command to get inbound processing error in Mailer
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' | tail -10 ;


---Query to Check Workflow Mailer Backlog
--State=Ready implies that emails are not being sent & Waiting mailer to sent emails
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;


--Check any particular Alert Message email has be pending by Mailer
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'
and upper(wno.user_data.TEXT_VC) like '%%';


--Check The Workflow notification has ben sent or not
select mail_status, status from wf_notifications where notification_id=
--If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by loggin in application + click on preference + the notification preference


--Check Wheather workflow background Engine is workfing for given workflow or not in last 2 days
-- Note: Workflow Deferred activities are run by workflow background engine.
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1=''
order by last_update_date desc

-- Check wheather any business event is pending to process
-- ie. Query to get event status & parameters value of particular event in wf_deferred table.
select wd.user_Data.event_name,wd.user_Data.event_key,
rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name) as serial_no,
n.NAME Parameter_name, N.value Parameter_value ,
decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',
3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,
wd.user_Data.SEND_DATE,
wd.user_Data.ERROR_MESSAGE,
wd.user_Data.ERROR_STACK,
wd.msgid,wd.delay
from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n
where lower(wd.user_data.event_name)=''
order by wd.user_Data.event_name, wd.user_Data.event_key, n.name

Debugging OA pages

Debugging OA pages


a. Enable the profile option : FND: Debug Log Enabled -- Set to Yes
b. Enable the profile option : FND: Debug Log Level -- Set to Statement level
c. Add the below piece of code in your OA page
boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT);
if (isLoggingEnabled)
{
pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
}
To see log stmt on browser append below string to browser url and click on enter


&aflog_level=statement

R12 Suppliers Query

SELECT DISTINCT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
ASp.vendor_name "Supplier Name" ,
ASP.ATTRIBUTE15 "HDRS LEGACY SUPP NO" ,
ass.vendor_site_code "SITE NAME" ,
hou.NAME "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
ASS.ATTRIBUTE15 "SITES LEGACY SUPP NO" ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.EMAIL_ADDRESS
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE 1 = 1
--AND ASP.VENDOR_NAME = PERSON.PARTY_NAME
AND ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.ORG_PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASP.ATTRIBUTE15 IN ('1240')
ORDER BY hou.NAME