Friday, 14 December 2012

XML report publisher concurrent program from backend.

XML report publisher

At times you might need to take the xml output of an existing program and apply an XML Publisher / BI Publisher Template to it. The standard use case is if the output is generated by pro*c code/ a spawned or host concurrent program. The XML Report Publisher concurrent program can help achieve this.

The Report takes the Concurrent request id, template application id, template name, template locale, template type and output type as parameters.

A sample piece of code is shown below.

DECLARE

l_req_id NUMBER;

BEGIN

fnd_global.apps_initialize(6087,

20420,

0);

l_req_id := fnd_request.submit_request('XDO',

'XDOREPPB',

NULL,

FALSE,

FND_GLOBAL.CONC_REQUEST_ID,

1919318,

20003, -- Receivables

'XXGILGMDWOPICKLIST', -- Statement Generate

'en-US', -- English

'N',

'RTF',

'PDF');

dbms_output.put_line(l_req_id);

commit;

END;

--
Best Regards,
Boge Prasanth kumar Reddy.

Enabling Audit Trail in Oracle Apps Release 12

1. Setting the AuditTrail Profile Value:

2. Creating the Audit group:

3.Creating the Audit Columns for the Audit Table:

4. Enabling audit for APPS User. Similarly query the AR user and enable Audit for the AR user.

5. Running the AuditTrail Update Tables Request:

5. Changing the status of the account number XXXXX to Invalid on Receivables Customer page:

6. Query the audit table hz_cust_accounts_a. One can see the record in the audit table and the status field shows it as Inactive.

Note:This materail is copied from applikast.net

--
Best Regards,
Boge Prasanth kumar Reddy.

Following SQL query can be used to find out the exact version of the oracle applications you are currently working on.

Following SQL query can be used to find out the exact version of the oracle applications you are currently working on.

SELECT substr(a.application_short_name, 1, 5) code,

substr(t.application_name, 1, 50) application_name,

p.product_version version

FROM fnd_application a,

fnd_application_tl t,

fnd_product_installations p

WHERE a.application_id = p.application_id

AND a.application_id = t.application_id

AND t.language = USERENV('LANG')

Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well.

Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well.

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

Monday, 17 September 2012

Sample script to insert secondary price lists to the primary price list

Sample script to insert secondary price lists to the primary price list

Sample script to insert secondary price lists to the primary price list with

mandatory columns to be populated in the following bulk loader interface tables:

QP_INTERFACE_LIST_HEADERS

QP_INTERFACE_QUALIFIERS

This script assumes that both primary price list and the secondary price lists

have been created already and exist. This script populates

* QP_INTERFACE_LIST_HEADERS table with ORIG_SYS_HEADER_REF of the secondary price list.

* QP_INTERFACE_QUALIFIERS table with primary price list Name in

QUALIFIER_ATTR_VALUE_CODE. Alternatively populate primary price list header id in

QUALIFIER_ATTRIBUTE_CODE.

/* Attach two secondary price lists to the Sample_BLK_PL price list.*/

/* Attach Secondary Price List 1 */

INSERT INTO QP_INTERFACE_LIST_HEADERS (

ORIG_SYS_HEADER_REF,

LIST_TYPE_CODE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG,

REQUEST_ID,

PROCESS_ID

) VALUES (

'SAMPLE_HEADER11', /* ORIG_SYS_HEADER_REF of the secondary price list to be attached.*/

'PRL', /* List Type Code */

1318, /* Last Updated By */

sysdate, /* Last Update Date */

'UPDATE', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/

'Y', /* Process Flag */

'P', /* Process Status Flag */

NULL, /* Request Id */

999 /* Process Id */

);

INSERT INTO QP_INTERFACE_QUALIFIERS (

ORIG_SYS_QUALIFIER_REF,

ORIG_SYS_HEADER_REF,

QUALIFIER_GROUPING_NO,

QUALIFIER_CONTEXT,

QUALIFIER_ATTRIBUTE_CODE,

QUALIFIER_ATTR_VALUE_CODE,

COMPARISON_OPERATOR_CODE,

QUALIFIER_PRECEDENCE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG,

PROCESS_ID

) VALUES (

'SAMPLE_QUAL11', /* ORIG_SYS_QUALIFIER_REF */

'SAMPLE_HEADER11', /* ORIG_SYS_HEADER_REF of the secondary price list.*/

-1, /* Qualifier Grouping No */

'MODLIST', /* Qualifier Context */

'PRICE_LIST', /* Qualifier Attribute Code */

'Sample_BLK_PL', /* Qualifier Attribute Value Code. Primary Price List Name.*/

'=', /* Comparison Operator Code */

100, /* Qualifier Precedence */

'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/

'Y', /* Process Flag */

'P', /* Process Status Flag */

999 /* Process Id */

);

/* Attach Secondary Price List 2.*/

INSERT INTO QP_INTERFACE_LIST_HEADERS (

ORIG_SYS_HEADER_REF,

LIST_TYPE_CODE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG,

REQUEST_ID,

PROCESS_ID

) VALUES (

'SAMPLE_HEADER2012', /* ORIG_SYS_HEADER_REF of the secondary price list.*/

'PRL', /* List Type Code */

1318, /* Last Updated By */

sysdate, /* Last Update Date */

'UPDATE', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/

'Y', /* Process Flag */

'P', /* Process Status Flag */

NULL, /* Request Id */

999 /* Process Id */

);

INSERT INTO QP_INTERFACE_QUALIFIERS (

ORIG_SYS_QUALIFIER_REF,

ORIG_SYS_HEADER_REF,

QUALIFIER_GROUPING_NO,

QUALIFIER_CONTEXT,

QUALIFIER_ATTRIBUTE_CODE,

QUALIFIER_ATTR_VALUE_CODE,

COMPARISON_OPERATOR_CODE,

QUALIFIER_PRECEDENCE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG,

PROCESS_ID

) VALUES (

'SAMPLE_QUALIFIER', /* ORIG_SYS_QUALIFIER_REF */

'SAMPLE_HEADER2012', /* ORIG_SYS_HEADER_REF of the secondary price list.*/

-1, /* Qualifier Grouping No */

'MODLIST', /* Qualifier Context */

'PRICE_LIST', /* Qualifier Attribute Code */

'Sample_BLK_PL', /* Qualifier Attribute Value Code. Primary Price List Name.*/

'=', /* Comparison Operator Code */

100, /* Qualifier Precedence */

'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/

'Y', /* Process Flag */

'P', /* Process Status Flag */

999 /* Process Id */

);

COMMIT;

EXIT;

Thursday, 13 September 2012

OM faqs

OM Questions 1) What are the Base Tables and Interface Tables for Order Management? Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc) Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information OE_ORDER_LINES_ALL: Items Information OE_PRICE_ADJUSTMENTS: Discounts Information OE_SALES_CREDITS: Sales Representative Credits. Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES. 2) What are the Base Tables and Interface Tables for Order Management? Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc) Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information OE_ORDER_LINES_ALL: Items Information OE_PRICE_ADJUSTMENTS: Discounts Information OE_SALES_CREDITS: Sales Representative Credits. Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES. 3) What is Order Import and What are the Setup's involved in Order Import? A) Order Import is an open interface that consists of open interface tables and a set of API's. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables. B) Setups: · Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills. · Define and enable the order import sources using the order import source window. 4) Explain the Order Cycle? i) Enter the Sales Order ii) Book the Sales Order(SO will not be processed until booked(Inventory confirmation)) iii) Release sales order(Pickslip Report is generated and Deliveries are created) (Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details. iv) Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas) v) Launch Pick Release ( vi) Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables)) vii) Auto invoice and closed 5) Explain the Order to Cash Flow? I. Enter the Sales Order II. Book the Sales Order(SO will not be processed until booked(Inventory confirmation)) III. Release sales order(Pickslip Report is generated and Deliveries are created) (Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details. IV. Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped) V. Launch Pick Release VI. Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables)) VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module) VIII.Autolockbox ( Appling Receipts to Invoices In AR) IX. Transfer to General Ledger ( Populates GL interface tables) X. Journal Import ( Populates GL base tables) XI. Posting ( Account Balances Updated). 5. What are the Process Constraints? A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations. Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement. 6. What are Validation Templates? A) Validation Templates are used to define the validation conditions in process constraints. A validation template names a conditions and defines the semantic of how to validate that condition. These are used in processing constraints framework to specify the constraining conditions for a given constraint. These conditions are based on 1 Where the entity is in its work flow. 2 The state of attributes on an entity. 3 Any other validation condition that cannot be modeled using the above condition. 7. What are different types of Holds? 1 GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers) 2 Credit Checking Hold( Used for credit checking feature Ex: Credit Limit) 3 Configurator Validation Hold ( Cause: If we invalidate a configuration after booking) 8. What is Document Sequence? A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types. Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type. 9. What are Defaulting Rules? A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes. It involves the following steps 1 Defaulting Conditions - Conditions for Defaulting 2 Sequence – Priority for search 3 Source – Entity ,Attribute, Value 4 Defaulting source/Value 10. When an order cannot be cancelled? A) An order cannot be cancelled if, 1 It has been closed 2 It has already been cancelled 3 A work order is open for an ATO line 4 Any part of the line has been shipped or invoiced 5 Any return line has been returned or credited. 11. When an order cannot be deleted? A) you cannot delete an order line until there is a need for recording reason. 12. What is order type? A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order. 13. What are primary and secondary price lists? A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list. The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list. 14. What is pick slip? Types? A) It is an internal shipping document that pickers use to locate items to ship for an order. 1 Standard Pick Slip – Each order will have its own pick slip with in each picking batch. 2 Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch. 15. What is packing slip? A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment. 16. What are picking rules? A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer. 17. Where do you find the order status column? A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL. 18. When the order import program is run it validates and the errors occurred can be seen in?A) Responsibility: Order Management Super User Navigation: Order, Returns > Import Orders > Corrections

Scheduling the Concurrent program

Scheduling the Concurrent program

We can submit the Concurrent program future date or date by using

the schedule button in SRS window

As soon as possible: This is default option whenever we submit the

request it will submit the as soon as possible

Once: It will submit the rest only once for future date.

Periodically: WE can specify the from_date and to_date to submit

program periodically no of. Days months, hours, minutes and so on.

Specific Days: If we want submit concurrent program in the specific

days we write select this option

Save this Schedule: This check box will be used to save the schedule

and apply same schedule to other concurrent programs

by selecting the button called 'Apply save schedule'

NOTE: After schedule the Concurrent program we can also
cancel by selecting the cancel button.

Monday, 10 September 2012

HOW TO COMPILE AND OPEN ORACLE FORMS IN UNIX:

Below are the commands to open and compile Oracle Forms in UNIX.

FORMS6I:
to open- f60desm
to compile/generate - f60genm

FORMS 9I:
to open - frmbld
to compile/generate – frmcmp

f90genm userid=scott/tiger@bs817 batch=yes module=$i module_type=form

compile_all=yes window_state=minimize

For 10gR1 = forms 9.0.4.x, you can use f90gen also

For 10gR2 = forms 10.x, you can use frmcmp.sh or frmcmp_batch.sh

`Demo script (Linux) – Compiling forms application`

frmcmp_batch.sh Module_type=LIBRARY Module=$ff userid=username/password@database upgrade=yes batch=yes window_state=minimize compile_all=yes

Value Sets

Dependent value Set:  ====================  This is another LOV which will be used to displays the   list of values which are depending on the previous parameter value.    Before going to create Dependent first we have to create Independent  then we have to create Dependent  First parameter will be Independent  Second parameter will be Dependent.    Note:Without Independent we can not create Dependent Value set.    Country	IND  	        US  	        UK  City   Banglore	Chennai 	Delhi	 Mumbai  Pune         Chikago  California      Anderson         London   Hungrant    1)We have to create Independent value set and enter the values.  2)Create Dependent value set attach independent and then enter values.      Job	  Manager  	  Developer  	  Programmer    Position   Delivery Manager   Project manager Financce manager  	   Software Developer Test Developer  	   Trainee  Fresher    Table Value set :    Table value set will be used to displays the list of values from the  oracle apps base tables.  we have to give the table name and column name which will automatically  displays the values.    Note: If values are not stored in the database table then we have to         go for Independent  value set.        If values are there in the table then we will create table value        set.    1.Open the value set form Select  validation type as table select the     button called Edit Information enter table name and column name     in the value field   2.Use where/Order By clause to implement Where/Order By clause.  3.Use Additional Columns field to displays extra columns for reference    purpose.  4.Use the ID column to pass the ineternally other columns data     for ex displaying username to the user and pass userID internally.  5.If multiple tables are required then enter the table names in the     table name field with alias name and enter the Join Condition in the    Where clause field.    6.If we know the table name we can find the Table application name from    Application Developer responsibility   Application Developer => Application => Database => table   Query the records based on the table Name.      Translated Independent and Translated Dependent:  ================================================  Both  value sets will work like Independent and Dependent value sets  will be used to displays the transalation values which will be enabled  if there is multilanguage implementation.    Special and Pair:  =================  Both Value sets will be used to displays the Flexfield data as LOV to  the User.

Tuesday, 4 September 2012

R12 SLA Tables join conditions to AP, AR, INV,Payments and Receiving

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

R12 SLA (Sub ledger Accounting)

1) All accounting performed before transfer to the GL. Accounting data generated and stored in "Accounting Events" tables prior to transfer to GL

2) Run "Create Accounting" to populate accounting events (SLA) tables. User can "View Accounting" only after "Create Accounting" is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables

– Creates detailed data per accounting rules, stores in SLA "distribution links" table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

xte.entity_code =

'TRANSACTIONS' or

'RECEIPTS' or

'ADJUSTMENTS' or

'PURCHASE_ORDER' or

'AP_INVOICES' or

'AP_PAYMENTS' or

'MTL_ACCOUNTING_EVENTS' or

'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 =

'INVOICE_ID' or

'CHECK_ID' or

'TRX_NUMBER'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = 'AP_PMT_DIST'

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

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

xdl.source_distribution_type = 'AP_INV_DIST'

and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

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

xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'

and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id

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

xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

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

xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

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

xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

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

xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'

and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

Friday, 13 July 2012

Query to get alerts information

SELECT
alv . *
FROM ALR_ALERTS al ,
ALR_ACTION_HISTORY aah ,
ALR_OUTPUT_HISTORY aoh ,
ALR_ACTIONS_V alv ,
alr_alert_historY_view aahv
WHERE al . alert_name = <name of alert > ---------'XX_ALERTS_SAMPLE'
AND al . alert_id = aah . alert_id
AND aah . check_id = aoh . check_id
AND alv . ALERT_ID = aah . alert_id
AND aahv . alert_name = al . alert_name;

--

Wednesday, 25 April 2012

QUERY FOR FINDING REQUEST GROUP

QUERY FOR FINDING REQUEST GROUP

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name,
       fe.executable_name
FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
WHERE     frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id
       AND fe.executable_id = fcp.executable_id
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = 'XX_PC_PURCHASE';
--

Create directory in Oracle

Create directory in Oracle

create or replace directory foo_dir as '/tmp';

Directories must be created if external tables are used.
Created directories are shown in either dba_directories or all_directories. There is no user_directories.

Privileges

When a «directory» has been created, the read and write object privileges can be granted on it:

create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

An example

The following example shows how create directory and utl_file can be used to write text into a file:

create or replace directory dir_temp as 'c:\temp';

declare
f utl_file.file_type;
begin
f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.put_line(f, 'line two: more text');
utl_file.fclose(f);
end;
/

Friday, 13 April 2012

How to deleted from the interface tables after it has been loaded in import standard purchase order

How is data deleted from the interface tables after it has been loaded ?

After loading the data from the interface tables into the system successfully, the data is not being removed (cleaned) from the interfaces.
They will have process_code = 'ACCEPT' in the interface tables.

To remove the processed data from the interface, a concurrent program is available. The program name is : purge purchasing open interface processed data(POXPOIPR)

Run this program with paramenter purge accepted data = Yes
Then the process_code = 'ACCEPTED' records will be removed from the interface tables.

Purchasing Interface Errors Report(choose parameter : PO_DOCS_OPEN_INTERFACE)

What document can be viewed after running purchasing document open interface with records rejected?

Check the process_code in the po_headers_interface and po_lines_interface, if it is 'REJECTED',

select process_code from po_headers_interface;
select process_code from po_lines_interface;

please do the following:
Run the program - Purchasing Interface Errors Report
choose parameter : PO_DOCS_OPEN_INTERFACE

The report will list all the errors you have during importing. You can fix the data, then reset process_code = Null in both interface tables, rerun the Purchasing Document Open Interface.

update po_headers_interface set process_code = null
where process_code = 'REJECTED';
update po_lines_interface set process_code = null
where process_code = 'REJECTED';

What Actions are supported in import standard purchase order ?

What Actions are supported?

ORIGINAL - create a new document
ADD - add new lines onto an existing document
UPDATE - update existing line information for a document
REPLACE - Replace the existing document
Which interface tables are involved?

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE(Used for Standard PO only)

Release 12.0, the “Import Standard Purchase Orders" concurrent program fails with the following error: ERROR ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT. Procedure init_sys_parameters.0

On Release 12.0, the "Import Standard Purchase Orders" concurrent program fails with the following error:

ERROR
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure init_sys_parameters.0
ORA-01422: exact fetch returns more than requested number of rows in Package
po.plsql.PO_PDOI_PVT. Procedure init_startup_values.10
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure start_process.50
User-Defined Exception in Package po.plsql.PO_PDOI_Concurrent. Procedure POXPDOI.30

Steps To Reproduce:
1. Populate the interface table with the PO details.
2. Navigate to Requests -> Run -> Single request.
3. Select 'Import Standard Purchase Orders'.

Cause

The multi_org_category flag was not set correctly.

Verify by running the following script:

Select multi_org_category
from fnd_concurrent_programs
where concurrent_program_name='POXPOPDOI';

Ideally, the above script should return a value 'S', which indicates it is set to single org. In problematic case it returns no rows.

Solution

To implement the solution, please execute one of the following set of steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:

Update fnd_concurrent_programs
set multi_org_category = 'S'
where concurrent_program_name='POXPOPDOI';

3. Commit the transaction using 'commit' command.

4. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:
Select multi_org_category from fnd_concurrent_programs where concurrent_program_name='POXPOPDOI';
-- should return a value 'S'.

5. Confirm that the data is corrected, run the "Import Standard Purchase Orders" concurrent program.

6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.

*** Alternately, this can be achieved via the application with the following steps:

1. Assign yourself the responsibility System Administration. (note it is not system administrator).

2. Navigate to responsibility System Administration - Concurrent Programs form
- Search on POXPOPDOI as short name
- Choose Update
- Move to Request tab
- Off to the right it shows - Operating Unit Mode
- Ensure this is single

Make sure it is S - using this sql -

Select multi_org_category from fnd_concurrent_programs where concurrent_program_name='POXPOPDOI';

After saving.

3. Retest the import and confirm if that has properly corrected the problem.

Friday, 6 April 2012

How to compile form in R12

Compile forms in R12

command:
frmcmp_batch userid=apps/apps module=XXXLPNINSPECTION.fmb output_file=XXXCLPNINSPECTION.fmx module_type=form batch=no compile_all=yes

--

MATCH_OPTION COLUMN / METHOD MISSING IN Purchasing Documents Open Interface

Bug 1930586: MATCH_OPTION COLUMN/METHOD MISSING IN PDOI

=========================================================================== 
                             PROBLEM DESCRIPTION 
=========================================================================== 
  ** DESCRIPTION OF PROBLEM, INCLUDING ALL ERRORS: 
     There is no column that accepts the value for match_option (invoice
    matching 'P'or'R') in PO_LINES_INTERFACE. (The enhanced PDOI now supports
   standard PO import.) If there is a method to populate the matching
   option value via the PDOI, it should be documented in the PDOI update
    release note. "matching option" is stored in the following table in EBS.    
 PO_LINE_LOCATIONS_ALL.MATCH_OPTION 
 
=========================================================================== 
                            ADDITIONAL DETAILS 
 =========================================================================== 
  ** TAR NUMBER (ALSO ENSURE BUG NUMBER FIELD IS UPDATED IN TAR): 
  ** LIST ADDITIONAL DOCUMENTATION AVAILABLE (LOG FILE, REPORT, TRACE, ETC.): 
 N/A     
  ** HOW WILL DEVELOPMENT RECEIVE THE ADDITIONAL DOCUMENTATION?: 
Via Email or ess30 upon request. 
  ** DESCRIBE ANY WORKAROUND(S) AVAILABLE TO THE CUSTOMER: 
Open POXPOEPO and update the option for all imported POs, which is rediculous. 
 
  ** LIST NAME & VERSION OF ALL MODULES INVOLVED (FORM,REPORT,PACKAGE,ETC.): 
EBS 11.5.3
Please ask if you need specific file versions. 
  ** IS THE PROBLEM OCCURRING IN TEST OR PRODUCTION? 
Production. 
   ** DOES THE CUSTOMER HAVE ANY CUSTOMIZATIONS OR 3RD PARTY PRODUCTS? 
No. 

=========================================================================== 
                                 HISTORY 
=========================================================================== 
   ** WAS THE CUSTOMER ABLE TO COMPLETE THE SAME PROCESS PREVIOUSLY?: 
No. 
  ** LIST PATCHES APPLIED RECENTLY WHICH COULD AFFECT THIS PROBLEM: 
N/A 

Defaulting of match_option is as follows
 1. From Supplier Site
2. From Supplier
3. From Financials System Parameters
The HLD for STD PO do not mention anything about this column.
Will be an ER to create the column in CASE and to add the necessary 
 validations.PREMCOR REFINING GROUP is requesting this ER be changed to a priority 3 bug so 
a fix can be included in a future PO Family Pack.   They have 100's of PO's 
 that they import from a 3rd party system (Maximo) using the PDOI.  These 
multi-line POs are primarily Service Type POs and whether the line can either
be Invoice Match Option to Receipt or Invoice Match Option to Purchase Order 
 needs to be controlled at the Line level when inserting into the PO Interface 
tables.





Here is the solution for the invoice matching, someone needs to update the 
bug to include this solution:
 
Invoice matching, populate the following columns in PO_LINES_INTERFACE table:
'2WAY'  inspection_required_flag = 'N'
         receipt_required_flag    = 'N'

'3WAY'  inspection_required_flag = 'N'
          receipt_required_flag    = 'Y'

'4WAY'  inspection_required_flag = 'Y'
         receipt_required_flag    = 'Y'

Tuesday, 3 January 2012

Calling Stored procedure through Forms Personalization

Calling stored procedure through Forms Personalization in Oracle apps for the user entered values.

Following are the steps to be followed :

Conditions :

Trigger Event : WHEN-VALIDATE-RECORD

Trigger Object : As appropriate

Condition : As appropriate

Actions :

Seq : 10

Type : Builtin

Description : Calling Stored procedure

Language : All

Builtin Type : Execute a Procedure

Argument :

='DECLARE
l_retcode NUMBER
l_errbuf VARCHAR2(2000);
BEGIN

xx_custom_package.main_procedure(l_retcode,l_errbuf,'||${item.BLOCK_NAME.ITEM_NAME.value}||','||
${item.BLOCK_NAME.ITEM_NAME.value}||');

END'

Friday, 14 December 2012

XML report publisher

Enabling Audit Trail in Oracle Apps Release 12

Monday, 17 September 2012

Thursday, 13 September 2012

Monday, 10 September 2012

HOW TO COMPILE AND OPEN ORACLE FORMS IN UNIX:

Demo script (Linux) – Compiling forms application

Tuesday, 4 September 2012

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

Friday, 13 July 2012

Wednesday, 25 April 2012

Friday, 13 April 2012

How is data deleted from the interface tables after it has been loaded ?

What document can be viewed after running purchasing document open interface with records rejected?

What Actions are supported?

Cause

Solution

Friday, 6 April 2012

Compile forms in R12

Tuesday, 3 January 2012

Calling Stored procedure through Forms Personalization

Labels

`Demo script (Linux) – Compiling forms application`