Advance Pricing Qualifier Upload

CREATE OR REPLACE PACKAGE BODY APPS.XX_EXC_DAP_DSL_ORDER_PKG IS
--
--
FUNCTION XX_DAP_DSL_ORDER_EXCLUDE1(p_order_number IN NUMBER )
RETURN CHAR IS
--
lc_order_type VARCHAR2(50);
--
CURSOR lcu_order_type (p_order_number NUMBER)
IS
SELECT 'Y'
FROM oe_order_headers_all oeh,
oe_transaction_types_tl otl,
fnd_lookup_values flv
WHERE oeh.order_number = p_order_number
AND oeh.order_type_id = otl.transaction_type_id
AND otl.LANGUAGE = USERENV('LANG')
AND flv.lookup_type = 'XXP4U_DAP_DSL_EXCLUSION_LIST'
AND flv.meaning = otl.NAME
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(flv.start_date_active, TRUNC(SYSDATE))
AND NVL(flv.end_date_active, TRUNC(SYSDATE));
BEGIN
--
OPEN lcu_order_type (p_order_number => p_order_number);
FETCH lcu_order_type INTO lc_order_type;
--
IF lcu_order_type%FOUND THEN
lc_order_type:='MATCH';
ELSE
lc_order_type:='NOMATCH';
END IF;
--
CLOSE lcu_order_type;
--
RETURN lc_order_type;
--
EXCEPTION
--
WHEN NO_DATA_FOUND THEN
--
RETURN 'NOMATCH';
--
WHEN OTHERS THEN
RETURN 'PLEASE CHECK THE SETUP-ERROR OCCURED';
END XX_DAP_DSL_ORDER_EXCLUDE1;
--
--
PROCEDURE XX_QUALIFIER_LOAD
(
p_qualifier_context IN VARCHAR2 ,
p_qualifier_attribute IN VARCHAR2 ,
p_comparison_operator_code IN VARCHAR2 ,
p_qualifier_attr_value IN VARCHAR2 ,
retcode OUT NUMBER ,
errbuff OUT VARCHAR2
)
AS
/* |
| Description: Procedure to update qualifiers for Existing Budgets
| This will be one time activity.
| This package also supports the Qualifier Attribute
| loading process.
+----------------------------------------------------------------------------*/
-- Local variables
ln_qual_cnt NUMBER := 1;
ln_msg_count NUMBER;
ln_error_location NUMBER;
lc_return_status VARCHAR2(100);
lc_msg_data VARCHAR2(4000);
lb_proceed BOOLEAN;
lt_qual_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_tbl ozf_offer_pvt.qualifiers_tbl_type;
ln_msg_index_out NUMBER(10);
ld_unique_header_line_cnt NUMBER(10);
lt_profile_value NUMBER ;
ln_count NUMBER:=0;
lc_header_value VARCHAR2(20):='N';
--
--------------------------------------------------
-------- Cursor for getting Budget Data --------
--------------------------------------------------
--
CURSOR LCU_ACT_BUDGET_NAME
IS
SELECT DISTINCT fund_name budget_name
from xxp4u.xxp4u_budget_upload_stg
where line_status='P' ;
--
CURSOR LCU_ACT_BUDGET_ID(p_budget_name VARCHAR2)
IS
SELECT list_header_id,NAME BUDGET_NUMBER ,description modifier_name
FROM qp_list_headers
WHERE ACTIVE_FLAG='Y'
AND UPPER(description)=UPPER(p_budget_name)
AND end_date_active IS NULL OR end_date_active >SYSDATE
GROUP BY list_header_id,NAME,description
ORDER BY NAME;
BEGIN
fnd_file.put_line (fnd_file.LOG,'Starting for Qualifier Update');
---------------------------------------
/* Block for capturing Profile values */
---------------------------------------
--lt_profile_value:=fnd_profile.VALUE('XXP4U_QUALIFIER_LOAD');
--
fnd_file.put_line (fnd_file.LOG,'Entering into loop for Updation');
--
FOR ACT_BUG IN LCU_ACT_BUDGET_NAME LOOP
FOR ACT_HDR_ID IN LCU_ACT_BUDGET_ID(ACT_BUG.budget_name) LOOP
BEGIN
/*--------------------------------------------------------------*/
-- Block to check wheather the Qualifier Exists for the Budget --
/*--------------------------------------------------------------*/
--
SELECT qq.list_header_id
INTO lc_header_value
FROM qp_qualifiers qq
WHERE qq.qualifier_attr_value = 'MATCH'--p_qualifier_attr_value
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE37'--p_qualifier_attribute
AND qq.list_header_id=ACT_HDR_ID.list_header_id
GROUP BY qq.list_header_id;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_header_value:='P';
--
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Problem in Section 11Y for header id'||ACT_HDR_ID.list_header_id);
--
END;
--
IF lc_header_value='P' THEN
--
fnd_file.put_line (fnd_file.LOG,'Calling the API...');
ln_count:=ln_count+1;
fnd_file.put_line (fnd_file.LOG,ACT_HDR_ID.BUDGET_NUMBER||
'- List_Header_id'||ACT_HDR_ID.list_header_id||
'- Budget Name'|| ACT_HDR_ID.modifier_name);
--
--For Attribute1
/* lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := p_qualifier_context; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := p_qualifier_attribute; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := p_qualifier_attr_value; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := p_comparison_operator_code;-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
*/
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := 'ORDER'; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE37'; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := 'MATCH'; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := 'NOT =';-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
-------------------------------------
/* Call The API to upload qualifier */
-------------------------------------
ozf_offer_pvt.process_market_qualifiers(
p_init_msg_list => fnd_api.g_true,
p_api_version => 1.0,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_qualifiers_tbl => lt_qual_tbl,
x_error_location => ln_error_location
);
COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,'For Budget Name :'||ACT_HDR_ID.modifier_name||'the qualifier already Exists');
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
fnd_file.put_line (fnd_file.LOG,'Problem in Setup'||SQLERRM);
fnd_file.put_line (fnd_file.LOG,'Successfully updated:'||ln_count||' Records');
END XX_QUALIFIER_LOAD;
--
PROCEDURE XX_QUALIFIER_1_LOAD
(
p_qualifier_context IN VARCHAR2 ,
p_qualifier_attribute IN VARCHAR2 ,
p_comparison_operator_code IN VARCHAR2 ,
p_qualifier_attr_value IN VARCHAR2 ,
retcode OUT NUMBER ,
errbuff OUT VARCHAR2
)
AS
/*
+-----------------------------------------------------------------------------
|
+-----------------------------------------------------------------------------
|
|
|
| Description: Procedure to update qualifiers for Existing Budgets
| This will be one time activity.
| This package also supports the Qualifier Attribute
| loading process.
+----------------------------------------------------------------------------*/
-- Local variables
ln_qual_cnt NUMBER := 1;
ln_msg_count NUMBER;
ln_error_location NUMBER;
lc_return_status VARCHAR2(100);
lc_msg_data VARCHAR2(4000);
lb_proceed BOOLEAN;
lt_qual_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_exc EXCEPTION;
ln_msg_index_out NUMBER(10);
ld_unique_header_line_cnt NUMBER(10);
lt_profile_value NUMBER;
ln_count NUMBER:=0;
CURSOR LCU_MODIFIER_ID
IS
SELECT list_header_id,NAME BUDGET_NUMBER ,description modifier_name
FROM qp_list_headers
WHERE ACTIVE_FLAG='Y'
--AND NAME IN('10023','10024','10025','10026','10027')
AND end_date_active IS NULL OR end_date_active >SYSDATE
GROUP BY list_header_id,NAME,description
ORDER BY NAME;

BEGIN
fnd_file.put_line (fnd_file.LOG,'Starting for Qualifier Update');
---------------------------------------
/* Block for capturing Profile values */
---------------------------------------
--lt_profile_value:=fnd_profile.VALUE('XXP4U_QUALIFIER_LOAD');
fnd_file.put_line (fnd_file.LOG,'Entering into loop for Updation');
FOR ACT_HDR_ID IN LCU_MODIFIER_ID LOOP
ln_count:=ln_count+1;
fnd_file.put_line (fnd_file.LOG,ACT_HDR_ID.BUDGET_NUMBER||
'- List_Header_id'||ACT_HDR_ID.list_header_id||
'- Budget Name'|| ACT_HDR_ID.modifier_name);
--For Attribute1
/*
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := p_qualifier_context; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := p_qualifier_attribute; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := p_qualifier_attr_value; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := p_comparison_operator_code;-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
*/
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := 'ORDER'; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE37'; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := 'MATCH'; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := 'NOT =';-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'

-------------------------------------
/* Call The API to upload qualifier */
-------------------------------------
ozf_offer_pvt.process_market_qualifiers(
p_init_msg_list => fnd_api.g_true,
p_api_version => 1.0,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_qualifiers_tbl => lt_qual_tbl,
x_error_location => ln_error_location
);
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG,'End for Qualifier Update');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Problem in Setup'||SQLERRM);
fnd_file.put_line (fnd_file.LOG,'Successfully updated:'||ln_count||' Records');
retcode := 2;
END XX_QUALIFIER_1_LOAD;
--
END XX_EXC_DAP_DSL_ORDER_PKG;
/

How To Set Email Style Preference For All Users At Once?

Applies to: Oracle Workflow Cartridge - Version: 11.5.9 to 11.5.10Information in this document applies to any platform.

Goal
The goal of that Note is to explain how to bulk update the Email Style preference for all E-Business Suite users at once. The same can be used to update the Email Style for a group of users only, for instance the users who have the preference set to "Disabled" ...
Usually, each user must reset the Email Style preference through the "Preferences" page available from his Home Page.

Solution
1. Unfortunately, currently there is no seeded way to do this. An enhancement request has been logged for this under Bug 5748131 (NEED PLEASANT WAY TO BULK RESET NOTIFICATION PREFERENCE FROM DISABLED).It is under Oracle Development's review ...
2. Currently, the only workaround to change the Email Style of all users is to update the tables.Note that there are 2 tables to update :
FND_USER_PREFERENCES and WF_LOCAL_ROLES.
NB:You should backup those tables before performing the updates.
3. Updates for All Users would look like :
a.
update wf_local_roles set notification_preference='' where orig_system in ('FND_USR','PER');
b.
update fnd_user_preferences set preference_value='' where preference_name='MAILTYPE' and
module_name='WF' and user_name <> '-WF_DEFAULT-';

4.
Updates for Users having the preference set to "Disabled" would look like :
a.
update wf_local_rolesset notification_preference=''where orig_system in ('FND_USR','PER')and name in(select user_namefrom fnd_user_preferenceswhere preference_name='MAILTYPE'and module_name='WF'and preference_value='DISABLED');b. update fnd_user_preferencesset preference_value=''where preference_name='MAILTYPE'and module_name='WF'and preference_value='DISABLED';

The where clauses of above SQL can be modified to match your needs

NB : Possible values for are :

QUERY (corresponds to preference value "Do not send me mail")
MAILTEXT (corresponds to preference value "Plain text mail")
MAILATTH (corresponds to preference value "Plain text mail with HTML attachments") MAILHTML (corresponds to preference value "HTML mail with attachments")
MAILHTM2 (corresponds to preference value "HTML mail")
SUMMARY (corresponds to preference value "Plain text summary mail")
SUMHTL (corresponds to preference value "HTML summary mail")
DISABLED (corresponds to preference value "Disabled")

ReferencesNote 360541.1 - 11.5.10 Users notification preference becomes Disabled in table FND_USER_PREFERENCES table

GL JOURNAL ENTRY, REVERSING, RECURRING FAQ

ORACLE GENERAL LEDGER

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

JOURNAL ENTRY, REVERSING, RECURRING

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

FREQUENTLY ASKED QUESTIONS

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

September 2008CONTENTS

--------

Journal Entry

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

1. How to delete a Journal Batch? The delete icon is greyed out. The journal batch does not have journals associated and it is unposted.

2. Why does an accounting period not appear in the accounting periods list of values on the journal entry form?

3. Why can not a journal batch be deleted or modified?

4. Which report shows details of a posted journal batch?

5. Is there a report that displays information on one specific journal entry - unposted/posted?

6. Can a posted journal batch be deleted?

7. Is possible to restrict users from entering negative amounts in journal lines?

8. How to set up journal approval in General Ledger?

9. How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?

10. How to easily copy a journal entry from one set of books to another?

Reversing Journals

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

11. An entire batch was reversed and posted, while trying to reverse and post just one journal entry in the batch. How can this be corrected?

12. Why is there a reversed posted journal in the next accounting period of a non posted journal?

13. Why is the reversal period of a journal being cleared out after the period is changed?

14. A journal entry with a source set up for automatic reversal is not reversed.

15. Does the reversal program generate separate reversal batches-journals for each journal in a batch that is reversed?

16. How do you reverse a journal entry that was already reversed, but its' reversed journal was deleted?

17. How do you restrict the ability to reverse unposted journals?

18. How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?

19. In Release 12, a reversal journal that is Unposted cannot be modified.

Why?QUESTIONS & ANSWERS

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

Journal Entry

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

1. How to delete a Journal Batch? The delete icon is greyed out.

The Journal Batch does not have journals associated and it is unposted.

Answer

------

The journals/enter first popup form looks only for batches with journals.

a. Go to Journals > Enter.

b. When the Find Journals window appears, click on the 'X' in the upper right corner to close the window.

c. Click the Review Batch button on the Enter Journals form. This will bring up the Find Batches window to query the batch.

d. Enter the batch Name and the Period.

e. Click the Find button and the batch should be displayed on the Batch window now.

f. Now it is possible to click on the delete icon to delete the unposted

journal batch.

Note 1036784.6

2. Why does an accounting period not appear in the accounting

periods list of values on the journal entry form?

Answer

------

The accounting period list of vlaues displays periods that have a closing

status of Open (O) or Future Enterable (F). New journal entries can be

defined in open and future enterable accounting periods. New journal entries

can be posted only in open accounting periods.

3. Why can not a journal batch be deleted or modified?

Answer

------

A journal batch can not be deleted or modified under the following

circumstances:

a. The source is frozen

b. Funds have been reserved for the batch

c. Funds are in the process of being reserved for the batch

d. The batch is in the process of being posted

e. The batch is posted

f. The batch is approved

g. The batch is in the process of being approved

A journal batch should not be updated if it comes from a sub-ledger.

Changing accounting information in a journal that originated in a

sub-module will unsynchronize the accounting information between the

ledger and the sub-ledger. Instead of changing the sub-ledger

journal, define a new journal to adjust the accounting information

if necessary.

A journal batch that has funds reverved can not be updated because

the funds would not be re-reserved appropriately.

4. Which report shows details of a posted journal batch?

Answer

------

Journals - General(180 Char) and Journals - General(132 Char)

reports display information relating to a particular journal batch.

5. Is there a report that displays information of one specific

journal entry - unposted/posted?

Answer

------

No. General Ledger reports display information of journal batches that

are posted or unposted.

Note 200787.1 gives a workaround solution.

6. Can a posted journal batch be deleted?

Answer

------

No, a posted journal batch can not be deleted. Reverse it to nullify

the accounting effects of the posted journal batch.

See Note 135532.1

7. Is possible to restrict users from entering negative amounts

in journal lines?

Answer

-------

Unfortunately, it is not possible to restrict users from entering negative

amounts in journal entry lines.

8. How to set up journal approval in General Ledger?

Answer

------

This is set up using Oracle Workflow Builder.

The complete procedure is

explained in Note 176459.1

9. How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?Answer

------

Please refer to Note 1074918.6

10. How do you easily copy a journal entry from one set of books to another?

Answer

------

There is no standard feature to copy journal entries between sets

of books. However, there are some alternatives. See Note 204082.1

Reversing Journals

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

11. An entire batch was reversed and posted, while trying to reverse and post

just one journal entry in the batch. How can this be corrected?

Answer

------

When you reverse an entire batch, one reversal batch is created for each

journal in that batch. To correct your problem, reverse the reversal batches

that contained the journals you reversed by mistake, then post them.

12. Why is there a reversed posted journal in the next accounting period

of a non posted journal?

Answer

------

This is the current funcionality. Reversing journals can be posted before

the original journal is posted.

Note 176329.1

13. Why is the reversal period of a journal being cleared out after

the period is changed?

Answer

------

This is the expected functionality in Release 11i. When you change the

period of a journal, the application can not determine what you want to

do with the reversal period, so it is cleared.

14. A journal entry with a source set up for automatic reversal

is not reversed.

Answer

------

General Ledger automatically submits the AutoReverse program when a period

is opened if the profile option, GL: Launch AutoReverse After Open Period,

is set to Yes. If a journal is created after the period has already been

opened, then the AutoReverse program will need to be submitted manually.

15. Does the reversal program generate separate reversal batches-journals

for each journal in a batch that is reversed?

Answer

------

Yes.

When a batch is reversed, General Ledger creates a reversing

journal entry for each journal entry in the batch. Note that this

also generates a separate reversal batch for each reversed journal.

16. How do you reverse a journal entry that was already reversed, but its

reversed journal was deleted?

Answer

------

General Ledger does not allow you to reverse a journal entry twice.

Confirm that the first reversed journal entry does not exist in the system.

Contact Oracle Support for the solution to Note 145043.1

17. How do you restrict the ability to reverse unposted journals?

Answer

------

Unposted journals can be reversed, this is the intended functionality.

Additional information can be found in Note 172016.1

This functionality changed in R12 - see Note 734848.1 In Release 12,

a batch must be Posted before it can be reversed.18. How do you automatically generate a reversal journal entry for a journal

category in the previous accounting period?

Answer

------

Automatic Journal Reversal is a feature that is included in Release 11i.

Additional information can be found in Note 151920.1

19. In Release 12, a reversal journal that is Unposted cannot be modified. Why?

Answer

------

This is the expected functionality in Release 12. However the profile

GL: Edit Reverse Journals can be set to allow the modification.

See Note 567641.1

Problem in deleting GL Batch(UNPOSTED)

Need to Delete a JE Batch (UNPOSTED)

Query the batch then Edit --> Delete --> Save

In case you need to delete the full batch click on Batch


In case you are not getting the above delete option then check below;

Cause:
A journal batch can not be deleted or modified under the following circumstances:

a. The source is frozen

b. Funds have been reserved for the batchs

c. The batch is in the process of being posted

d. The batch is approved

e. The batch is in the process of being approved


Solution:
If your batch status is not there in any of the above cases then please try to following to fix this problem:

To Modify a journal batch that is frozen, follow this procedure:

a). (N) Setup > Journal > Sources

b). Query the Journal Source

c). Uncheck the Freeze Journals box and save the changes before exiting the form.

d). The batch or journal will now be able to be modified or deleted

e). After deleting/modifying the batch, navigate back to the Journal Sources form and re-freeze the Source.

Please note that once the journal is deleted/modified it cannot be recreated and in most circumstances means you will not be able to reconcile your subledger.
To delete Batches that are found in Journals Enter form 1.

The Navigation Path is: Journals > Enter

2. Provide search criterion in the Find Journal form

3. Select the unposted batch that has to be deleted

4. Click the Delete Icon from the tool bar.

Select the Batch option to delete whole batch

Note that the above process works only for the transactions which have not come from the subledgers or where trasnactions have not been checked for funds in the sub-ledgers. The transactions from the subledger are funds checked and passed by subledgers and hence the funds check status can not be changed in General Ledger. Where transactions have passed funds check in the subledger the above process will not work. In such a case please log an SR with Oracle Support.

References (www.metalink.oracle.com)
Note 236996.1 - How to Delete an Unposted Journal Imported to GL

Note 330668.1 - Journal Batch is Stuck in Approval - Can't Delete, Modify or Approve It

AP Closing Process

The process to close an accounting period has changed in release 11i. The
form to control the status of an accounting period has been renamed from
“AP Accounting Periods” to “Control Payables Periods”. Its location in the
navigator has also changed. It has moved from the Setup section into the new
Accounting section of the menu.

The rules to close a period in Payables in release 11i are:


All payment batches must be confirmed
All transactions must be accounted
All accounting entries must be transferred to general ledger
All future dated payments which have reached maturity in the accounting


period must have their status updated to negotiable and be accounted
---------------------------------------------------------------------
Optionally, unaccounted transactions can be swept to the next accounting period (if
allowed by accounting rules).

There is a new program in 11i to support this called the
"Unaccounted Transactions Sweep".

This program can be submitted from the "Control
Payables Periods" form in both a review mode and an update mode. The program can
be called only if there are any unaccounted transactions in the period.

This new program replaces the "Unposted Invoice and Payment Sweep".
Before invoices can be accounted they must be approved by the Payables Approval
process.

Before payments can be accounted they must pass through the Confirm
process.

Typical steps to close a period in Payables release 11i are:

. Import all invoices and/or expense reports from AP Interface tables
. Run Approval
. Confirm any outstanding payment batches
. Run the Update Matured Future Payment Status program for any future
dated payments
. Run the Payables Accounting Process
. Review the accounting process output and correct any accounting errors
. Review the Unaccounted Transactions Report to identify transactions
which cannot be accounted
. Correct any transaction data and run the Payables Accounting Process again
. Review the Payables Account Analysis Report (This report replaces the
Expense Distribution Detail Report)
. Run the Payables Transfer to General Ledger
. Optionally run the Unaccounted Transactions Sweep
. Close the Payables period
. Reconcile AP to GL

You cannot Invoice on a Purchase Order when the quantity billed exceeds the

Problem Description
-------------------
You cannot Invoice on a Purchase Order when the quantity billed exceeds the
quantity invoiced.


Problem Explanation
-------------------
For an amount based Purchase Order, if the quantity received is lesser than the
quantity ordered and billed, the system creates a hold on the Invoice.
The system hold namely 'Quantity billed exceeds quantity received' cannot be
released.

Example:

Quantity ordered = 5000
Quantity received = 2822.61
Quantity billed = 5000

Invoice is on hold.

Responsibility: AP Manager
Navigation:
Navigator -> Invoices -> Entry -> Invoices


Search Words
------------
Quantity billed ,Invoice on hold, Purchase Order match.

Solution Description
--------------------
The Invoice on hold can be released by changing the Invoice Quantity Ordered
Tolerance and the Invoice Quantity Received Tolerance in Setup in Accounts
Payables. Alternatively, the Invoice can be released manually by disabling the
match option in Invoices.


Solution Explanation
--------------------
Quantity billed exceeds quantity received by tolerance amount. This is a system
defined hold.

This can be done manually by releasing the hold.

Responsibility: AP Manager
Navigation:
Navigator -> Setup -> Invoices -> Tolerances


1. Check the Maximum quantity received. This can be defined in terms of
percentage and amount.

2. The hold can be released by changing the tolerance amount (both quantity
ordered and quantity received).

4. The Purchase Order can then be approved after the hold is released.

Alternatively,

Navigate to Accounts Payables responsibility and disable the Match option.

Navigation:
Navigator -> Invoices -> Entry -> Invoices

The Invoice will be released from the hold and it can be approved.

APPROVAL ON 3WAY MATCH INVOICE THRU APXINWKB DOES NOT PLACE QTY REC HOLD

Problem Description:
=====================
In Oracle Payables, you are using 3 way matching, for which an invoice matched
to a po must have qty received to satisfy the match.
The QTY REC hold is not being set from invoice level or batch level.

Problem Explanation:
====================
You had set 3 way matching on the vendor level.
You tried to create an invoice and match to a po, where there was no quantity
received. This should have indicated a QTY REC hold, but it did not.

You set his tolerance to .0001%, at the vendor level quantity received tolerance.

This didnt make a difference.

Solution Description:
=====================
The tolerance needs to be set at the Tolerance form.
gui \ setup invoice tolerance
char \ nav setup invoice tolerance
Quantity Received set to 0 or above %.

Solution Explanation:
=====================
There are two tolerances on this form:
Quantity Ordered
and Quantity Received.
The quantity ordered would allow at most x% of qty billed greater than the
quantity received.

Quantity Ordered
----------------

Enter the percent difference above purchase order shipment line quantity
ordered that you allow vendors to invoice. When you submit AutoApproval,
Oracle Payables places a Qty Ord hold on an invoice when the quantity billed
exceeds the quantity ordered on the matched purchase order shipment line by
the percent you enter in this field.

Quantity Received
-----------------
Enter the percent difference above purchase order shipment line quantity
received that you allow vendors to invoice. When you submit AutoApproval,
Oracle Payables places a Qty Rec hold on an invoice when the quantity billed
exceeds the quantity received on the matched purchase order shipment line by
the percent you enter in this field.

The quantity received field should have 0 or greater in it.

The problem with max qty received is now fixed for online approval.

i-Expense Common setup and Trouble Shoot

Using Oracle iExpenses Module, employees can enter and submit expense reports. There is a Workflow process that is initiated behind when an expense report is submitted which automatically routes expense reports for approval. Oracle Internet Expenses(iExpenses) integrates with Oracle Payables to provide quick processing of expense reports for payment.

This article includes these topics:

Process Overview
Approval Hierarchy
Administrative features in iExpense
Workflow Notifications -- Error Handling

Process Overview:

The employees would be issued Corporate credit cards which could be used for organization's business expenses.
The bank would send in all the credit card transaction files on a daily basis or the agreed time interval.
These credit card transactions have to be loaded into AP_CREDIT_CARD_TRXNS_ALL table using credit card transaction
loader programs. After loading credit card transactions data into the AP_CREDIT_CARD_TRXNS_ALL table,
the Credit Card Transactions Validation program should be run to validate the loaded data. Once validated,
employees can enter these credit card transactions on expense reports to get re-imbursement from the company.

Once the employee vouchers the expenses through iExpense responsibility or
Expense report screen in Account Payables responsibility and submits it,
the workflow with the internal name APEXP is initiated which would validate the data entered, determine
the Approver, sends notifications to the approver. The Approver has to then Approve or Reject the notification accordingly.
Once the workflow activities are complete, use the concurrent program Expense Report Import to create Payables invoices
from expense reports you enter in Payables or Oracle Internet Expenses. You can then use Payables to validate and
pay these invoices and create accounting entries for them.


Approval Hierarchy in iExpense:

If the profile option "AME: Installed" is set to Yes, then the routing rules for approvals setup in Oracle Approvals Management are used, If set to No then the Employee/Supervisor Approval Process is used.
Employee/Supervisor Approval Process:
In the Employee/Supervisor Approval Process, the supervisor of the Expense report's owner would become the APPROVER. Every Employee should be assigned a Supervisor within Human Resources Responsibility- (New Hire: Assignments).An employee must be set up as a supplier prior to entering the expense report.
Managers can approve an expense report only if the total amount of the expense report does not exceed their signing limit. The Manager (Spending) Approval Process in the Expense Reporting workflow uses the signing limits you define(Navigation: Account Payables responsibility - Employees> Signing Limits )to determine which manager has authority to approve expense reports. If the amount on the expense report exceeds the signing limit of the approver, the expense report is automatically forwarded to the manager of the approver. This goes up the management hierarchy until an Approver is found who could approve the Expense report.

This process does not look at the purchasing Approval Hierarchy.
Administrative features in iExpense:
• Delegating the authorization to enter the expenses on one’s behalf:
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:
• 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.

 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.

• Re-direct or Auto respond to the notifications:
If the approver is on leave, the notification could be delegated or forwarded to the desired approver using Create Vacation Rule.

Steps to create Vacation rule:
1. iExpense Responsibility> Expenses Home
2. Select the "Vacation Rule" link
3. Select the "Create New Rule" link
4. For Item Type select "Expenses" for all Notifications or select the desired Notification Type from List of Values
5. Select the "Next" link to proceed to the next setup page for the rule
6. Enter both a a valid Start and End Date for the new rule
7. Ensure the "Reassign" radio button is selected
8. Keep "All Employees and Users" in the menu box
9. Select the "Delegate" radio button - Assign/Reassign action is prohibited for Purchasing Notifications
10. Select the Flashlight icon next to the blank menu box for selecting a user
11. A new sub-screen should pop-up to perform the user search
12. For "Search By" select "Username" - (DO NOT search by Name)
13. Perform the needed wildcard (%) search at the top of the page to locate the desired
username
14. Click the radio button next to the desired name of the user to have the notifications sent to
15. Press the "Select" button followed by the "Apply" button
16. Continue on with the saving of the Vacation Rule for latter use

• Assigning the Card to another person to voucher the expenses on someone’s behalf
This could be done if a person has to access the outstanding credit card charges of another person to voucher them (temporarily). The disadvantage with this would be, the charges will be vouchered under the assignee’s name and would go to the assignee’s manager for Approval.

Workflow Notifications Error Handling:

In this section we will mainly focus on the notifications that are sent to the APPROVER for the approval as it is very important to see that such notifications reach the APPROVER on time so that there is no delay in the processing of the expense report to avoid late fee enforced by the Credit Card company. These notifications will have the message_name as 'OIE_REQ_EXPENSE_REPORT_APPRVL' in wf_notifications table. The mail status of the notification can be SENT, CANCELED, MAIL, UNAVAIL, ERROR, NULL. Let us see in detail about these and the actions required to get the notification to SENT status..
The below is the script to check the notification status:
select wn.notification_id,
aerh.invoice_num,
TO_CHAR(wn.begin_date,'DD-MON-YYYY hh:mi:ss') begin_date,
wn.to_user,
wn.from_user,
wr2.display_name preparer,
wr3.display_name reported_for,
wn.status,
wn.mail_status mail_status,
aerh.expense_status_code ,
WR.email_address ,
aerh.total,
aerh.report_submitted_date ,
aerh.description,
from wf_notifications wn,
wf_item_activity_statuses was,
wf_items wi,
ap_expense_report_headers_all aerh,
WF_ROLES WR,
wf_roles wr2,
fnd_user fu,
wf_roles wr3
where 1=1
and wn.notification_id = was.notification_id
and was.item_type = wi.item_type
and was.item_key = wi.item_key
and wn.status IN ( 'OPEN', 'CANCELED')
and wn.message_name = 'OIE_REQ_EXPENSE_REPORT_APPRVL'
and aerh.invoice_num = wi.user_key
and aerh.expense_status_code IN ( 'PENDMGR', 'RESOLUTN' )
and WN.recipient_role= WR.name (+)
and wi.owner_role = wr2.name
and aerh.employee_id = fu.employee_id
and fu.user_name = wr3.name (+)


SENT: The notification has been successfully sent to the APPROVER.
CANCELED: The system is setup to send the notifications n times (n can be set in the workflow attributes) with a timeout after 5 days. If the Approver does not approve or reject within the time frame, the workflow will cancel the notification automatically and then the notification will end up in the status CANCELED.
Action: In such cases, RETRY would not work instead ask the preparer to withdraw and re-
submit the expense report.
MAIL: If the notification is not SENT to the approver, the status would be MAIL.
Action: Ask the DBA to bounce the workflow. This would solve the problem most times. But if it doesn’t i.e if the status is still MAIL, then RETRY the activity ‘Request Approval from APPROVER’ using the Workflow Administrator responsibility (demo below). If yet not sent, check the Attributes like Approver’s email etc.
BLANK: Sometimes the value of the status would be NULL.
Action: Sometimes even if the status is BLANK, the notification is sent to the approver. First check with the Approver to see if the notification is sent. If not sent, RETRY the activity.

UNAVAIL: This would happen usually when there is problem with the Approver’s preferences or Email address.
Action: Check the notification preference of the Approver using the below:
SELECT WFU.NAME, WFU.display_name,WFU.notification_preference, email_address ,wfu.status
FROM WF_USERS WFU
WHERE WFU.name in (‘NAME’) (NAME would be the user_name in FND_USER table)
The notification_preference can be set to one of the below
MAILTEXT
QUERY
DISABLED
MAILHTM2
MAILHTML
MAILATTH
Make sure that it is set to MAILHTML
Also check if the email address returned in the above query is a valid one and matches with the one in HR Employee record (hr_employee table), fnd_user table and the outlook email address.
Also check if the status is ACTIVE in the above query, if the APPROVER is an existing employee and his FND_USER account is ACTIVE.



FAILED:
Action RETRY would solve this case.
ERROR:
Action: Check the ERROR and act accordingly. Some sample Errors:

--Failed Activity AP Validate Expense Report
Activity Type Function
Error Name -20001
Error Message ORA-20001: APP-SQLAP-10000: ORA-00984: column not allowed here occurred in AddDistributionLine with parameters (&PARAMETERS) while performing the following operation: parse cursor
Error Stack AP_WEB_EXPENSE_WF.APValidateExpenseReport(APEXP, 142459, 0, Call DoAPValidationORA-00984: column not allowed here occurred in AddDistributionLine with parameters (&PARAMETERS) while performing the following operation: parse cursor) Wf_Engine_Util.Function_Call(AP_WEB_EXPENSE_WF.APVALIDATEEXPENSEREPORT, APEXP, 142459, 175587, RUN)
Check if the code combinations are all passed in correct and if it exists. There is something wrong in the data which results in this error. If the expense report errors out in this Stage i.e it has not passed the validation, Status in expense report header would not be WEBEXPENSE and hence cannot see this in AP.

--. No Approver/Performer ERROR:

Check to see if there is any supervisor assigned in HR
Check to see if Supervisor exists in USERS list and in HR.
If everything is fine, In the status monitor, query for the Item key
Click on Active History
Check Request approval from APPROVER, hit Update attributes.
Fill in the appropriate Manager Id (Employee ID), Manager name(WF_USER.NAME), Manager Display Name(WF_user.display_name), approver ID, approver name ,Approver display name and hit Apply.

Go back to Active history, Check Request approval from APPROVER, hit Rewind.

--DISCARD folder error:
Failed Activity Request Approval from APPROVER
Activity Type Notice
Error Name WFRSPPR_BOUNCE
Error Message 2405: Notification '1251131' has bounced. Check mailer log and discard folder. STATUS=ERROR ROLE=ABHI

Ask the DBA to resend the notification from Workflow mailer. This is due to Mail Format or OUTLOOK version issues.
--Environment context Error:
Failed Activity Request Approval from APPROVER

Activity Type Notice

Error Name WF_ERROR
Error Message [WF_ERROR] ERROR_MESSAGE=3835: Error '-20001 - ORA-20001: Oracle error -20001: ORA-20001: APP-FND-02500: Error occurred during product initialization for MO when executing 'begin MO_GLOBAL.INIT; end;'. SQLCODE = -20001 SQLERROR = ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option. has been detected in FND_GLOBAL.INITIALIZE.' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= Wf_Engine_Util.Function_Call(AP_WEB_EXPENSE_WF.CALLBACKFUNCTION, APEXP, 144392, SET_CTX) Wf_Engine_Util.Execute_Selector_Function(APEXP, 144392, SET_CTX) Wf_Engine_Util.Function_Call(AP_WEB_EXPENSE_WF.CALLBACKFUNCTION, APEXP, 144392, TEST_CTX) Wf_Engine_Util.Execute_Selector_Function(APEXP, 144392, TEST_CTX) Wf_Engine.CB(TESTCTX, APEXP:144392:175390, ::::) Wf_Engine.oldCB(TESTCTX, APEXP:144392:175390, ::::) WF_MAIL.SetContext(1252292) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 1252292) WF_XML.Generate(oracle.apps.wf.notification.send, 1252292) WF_XML.Generate(oracle.apps.wf.notification.send, 1252292) Wf_Event.setMessage(oracle.apps.wf.notification.send, 1252292, WF_XML.Generate) Wf_Event.dispatch_internal()
Error Stack

RETRY would resolve the problem.
If all the attempts were futile in sending the notification to the Approver, the last action to resolve would be to ask the approver to go to IExpense responsibility and approve manually.



RETRY Demo:• Log into Workflow Admin responsibility
• Go to Administrator Workflow > Status Monitor
• Key in the following values:
Type Internal Name: APEXP
User Key: Expense Report Number
• Click Go
• Select the Radio button and click on Activity History.



• Select the Activity you want to RETRY say “Request Approval from APPROVER” and click on the Retry button on the top.



• Click on Submit button in the next screen.

• Confirm the details in the next page by clicking the OK button.




REWIND Demo:• Log into Workflow Admin responsibility
• Go to Administrator Workflow > Status Monitor
• Key in the following values:
Type Internal Name: APEXP
User Key: Expense Report Number
• Click Go
• Select the Radio button and click on Activity History.



• Select the Activity which you want to REWIND for example say “Request Approval from APPROVER” and click on the Rewind button on the bottom.



• Choose the activity to which you want the workflow to return in the next screen and select the "Apply" button. This would REWIND the activity.


UPDATE ATTRIBUTES Demo:
In the “Activity History” screen, click on the “Update Attributes” Button at the bottom. This opens up a page with all the attributes where you could update the necessary and hit “Apply” button to save the changes.