AP: Invoice Aging Report

AP: Invoice Aging Report

Let’s see what the term Invoice Aging means? Why is it called Aging report? Where is the Aging report used? How does the Aging report work?

Why called Aging Report?: Aging comes from the word Age. “Age” is the chronological representation of any phenomenon. Be it a person, an organization. Invoice Aging is the calculation of Unpaid or Outstanding Invoice’s Age.In Oracle Payables this calculation is done using the Invoice Aging standard report.The calculation is represented inDays.

Where is the Aging report used?: It is used to calculate the Due and Overdue days of any outstanding supplier’s or invoice’s payment. Using this aging information the cash outflow is determined. And many other decision can be taken.

How does the Aging report works?: Oracle Payables uses the Invoice Date, Payment Terms and Term Date attached to it for calculating the invoice’s age. GL Date has no effect on the aging.

Following are the setup steps required to implement Invoice Aging report:

1. Define Aging Period
   • Aging Period is a user defined slabs of days. User can define one to many aging calendars.
2. Run the Standard Request using the defined aging period.

Let’s take a scenario to define the Aging Period. Suppose I want to know the payments Overdue by 30 days and payments Due in 30 days i.e. if Today is 1-April then this aging period will display the payments due from 2-March to 1-May. So here is how to define such periods.

Step 1:

Navigation: AP>Setup>Calendar>Aging Period

1. Enter the Name, let’s suppose “Monthly Aging Period”.
2. Enter the Description(optional), like “30 days due and overdue aging period”.
3. The Column Order will be by default 1, let it be.
4. Enter 16 in Days From and 30 in Days To. (Yes, number of days in positive will pick the past due payments)
5. Give the First Column Heading as 30 – 16 and Overdue in Second Column Heading, though it is optional. These column headings will appear on the aging report.
6. Go to next row, the column order will be automatically generated.
7. Enter 0 in Days From and 15 in Days To.
8. Give the First Column Heading as 15-0 and Overdue in Second Column Heading.
9. Go to next row.
10. Enter -15 in the Days From and 1 in Days To.
11. Give the First Column Heading as Due In and give 1-15 Days in Second Column Heading.
12. Go to next row i.e 4th or the last row, as the aging report has only four columns.
13. Enter -16 and -30 respectively in both columns.
14. Give the First Column Heading as Due In and give 16-30 Days in Second Column Heading.
15. Save the period.

Step 2: Now you have run the standard request name Invoice Aging Report. Give the following parameters

• Sort Invoice By:
  • Trading Partner: All invoices will be sorted and grouped by Supplier’s Name
  • Invoice Type: All the invoices will be sorted by the Invoice Type, like Standard, Prepayments, Credit Memos etc.
• Include Invoice Detail:
  • Yes: The invoice information will be displayed in detail like Invoice Number, Due Date, Unpaid%, Amount Remaining.
  • No: A summarized view of the
• Include Site Details:
  • Yes: The invoices will be categorized supplier site wise.
  • No: There will be no site information.
• Minimum Amount Due: There is no need to define this parameter. I think the name says it all :)
• Maximum Amount Due: There is no need to define this parameter. I think the name says it all :)
• Invoice Type: There is no need to define this parameter. I think the name says it all :)
• Trading Partner: Due to this parameter I also call this report as Supplier Aging Report.
• Aging Period Name: Give the name of the period of which you want to have the due calculate.

So with this you can have either Invoice wise or Supplier wise Aging analysis of your payables dues.

Key Flexfields

Oracle EBS: Key Flexfields

First let’s break the term to define it in more detail. “Key” means important, For Example somebody says General Ledger is the key module in Oracle EBS or he played the key role in winning this game, etc. So “Key” means important. The term “Flexfield” is composed of two words, “Flex” and “Field”, Flex means flexible. Field is self explanatory term and is referring to the input field in any software.

Key Flexfields are used by some modules like for example Oracle General Ledger uses Accounting Flexfields, OracleAssets uses Location, Category and Asset Key Flexfield, etc. To see which application uses which use the Segment Setup Window. In General Ledger the navigation is

GL>Setup>Financials>Flexfields>Key>Segment

On Application field click (B)Torch and press the initial letter of the application, Like “i” for Inventory.

Now what or how can any field is flexible? Well there are Three types of flexibilities you get from KFF.

1. Every KFF needs segments. The number of segment depends on the requirement. This segment is one form of flexibility. For Example: One organization decides the COA Structure or Accounting Flexfields as Company-Branch-Department-Natural Account. Another organization decides it COA as Company-Branch-Department-Product-Account.
2. Each segment has a value set. This value set is another form of flexibility. Both of these flexibilities are ONE TIME. Once you have decided the segments and value sets of any KFF structure, it is freeze.
3. The third type of Flexibility in KFF is related to Security. You can define multiple security rules on segment values for multiple responsibilities. How? We will find out later.

So this is the flexibility Oracle has in Key Flexfield. Or what I understand from the term key flexfields.

In General Ledger Segment provides the flexibility to define particular information to level of details required by the user/organization. Technically, it is column divided into further columns, those further columns are called segments.There can be 30 Segments in General Ledger, each can have 25 character length, but in actual the length supported by the application is of 240 characters.

Oracle EBS: Key Flexfield Segment Value Set

Oracle EBS: Key Flexfield Segment Value Set

The basic definition of a Value Set (VS) is the “Validation of values” for a particular segment. Now if I further simplify Validation it means the type of value a segment can have.

For Example: Take an example of Multinational Company (MNC) say SydWerkz International having multiple branches in different countries. Now if SydWerkz decides to implement Oracle EBS Financials then the COA structure would definately have a Branch Segment. The heirarchy of the branches decided by SydWerkz International was

• Continent(1)
  • Country(2)
    • City(3)
      • Branch(2)

The number written in front of each branch category is the character length. For example Country has a character length of 2, which means there can be 100 countries or at least 99 countries. The total charachter length of Branch segment will be 8 and code of the branch segment will be 10000000.

Now if we list down the above mentioned categories with the code, it will be

Regions:

1- Asia

2-Europe

Country:

01-Pakistan

01-Germany

02-India

City:

001-Karachi

001-Mumbai

Branch:

01-Branch A

02-Branch B

See the beauty of the heirarchy this code character will have:

10000000 – Asia

10100000 – Pakistan

10100100 – Karachi

10100102 – Branch B

20000000 – Europe

20100000 – Germany

So you see that the branch codes are the identifiers of Branch category. You can even identify which code is a parent and which one is the child. If the last two digits of the branch codes are 00 then the code is of City and it is a parent, if the last 5 codes are 0 then the code is of Country and it is a parent.

A branch value set in this case will have a “Char” format and “Numbers Only” Check Box enabled and the length of the Value Set will be 8.

The Button of Usage shows where the selected Value Set is used, in DFF or KFF in which Structure and in which Segment.

Validation can be done on the following attributes

• Type of segment list
  • List of Values: It displays Searching Criteria and Values. Useful if the quantity of value is from 50-500
  • Long List of Values: It only displays the searching criteria. Useful if the quantity of value is from 500 – 10000
  • Pop List: It is a dropdown for the value set. It is useful if the quantity of the value is from 1-15

• Type of Security that applies on that particular segment
  • Hierarchical Security: if applied to parent then child also has the same security rules. Likewise Non-Hierarchical security is applied only to that specific segment value has the security rule.
  • It for restricting segment values among Responsibilities. When you define security rules for any segment then you have to re-compile the COA Structure.
  • To define security you have to specify the segment, first include all the values then exclude the particular value.Save the rule. Assign the rule to particular responsibility.
• Format of the value set
  • Char is used for financial and other values.
  • Date & Time is specific to Purchasing & Order Management.
• Numbers Only: If this option is checked then the value set will only have numbers. To enable Alphabets keep this box uncheck.
• Upper Case Only: Only Capital letters will be allowed.
• Right Justify Zero fill: This option will automatically fill the zero on the right side. For Example if the character length of a value set is 4 and during entering a value, if “65″ is entered, the system will automatically insert 0 on the right and code will become “0065″
• Maximum Size of the value set:The total length of a value.
• Minimum Value: The minimum side of the range.
• Maximum Value: The maximum size of the value. Like in the above example of the Branch Segment, the maximum size can set to 7999999. as there are only 7 continent and cannot exceed this value at the moment.
• Value Validation: This needs more explanation and it will be discussed later. For now the definition of this attribute is “if the value of this segment depends on another segment or the values should be picked from a Table” then this validation defined. Generally we use the following
  • Independent
  • Dependent
  • Table

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. 

Oracle Payables Technical Information (11i)

 Oracle Payables Technical Information

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

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

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

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

Creating Payments

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

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

Printer and Pasta configuration on Oracle Applications

Printer and Pasta configuration on Oracle Applications

Setting up printer in Linux OS (Remote printer configuration)

- Run printtool as root

Illustration I: Printtool Command in Linux

Click new from the popup

Adding a New Print Queue
Here I gave name as HPLaser2105 and short description as HPLaserJet printer in Abdulla Machine (Just to identify)

Defining a Print Queue
You select queue type from the drop-down combo box (Networked Windows (SMB))

Select Queue Type
You click specify from the window.
Select Windows SMB

Here is the configuration window

Configuration - Printer residing Machine
It is finished with configuring. (It will be like (specified))
Configuration - Note the word (Specified)Next Select printer model, Here I selected PCL6/PCL XL Printer
Note for PCL from Provider

Next Test Print from the printer (It should be printed!) else review the steps.
Test Page Print

After configuration the printer configuration will be like this.
Finish Printer Configuration

You should share, and make the printer as default by clicking edit.

Now the Applications Configuration:

Login as sysadmin and navigate to system administrator then Install:Printer
Then navigate to Driver. Here I created HPLASER2105 (Printer type)
Selected SRW driver as L
Driver method as command and driver method parameters as Spool File
Arguments as
: lp –c –d$PROFILES$.PRINTER –n$PROFILES$.CONC_COPIES –t"$PROFILES$.TITLE" $PROFILES$.FILENAMETo test the argument, please run this command in os command

$lp –c –d -n1 –t"Print Test"

Here I used (example)
lp –c –dHPlaser2105 –n1 –t"Print Test" sqlnet.log

Command Line : Printer command for Linux (telnet)

The printer should work. This is the command we are going to pass as argument in Oracle Apps

Define Printer Driver from SYSADMIN -> Install Print. Provide SRW Driver as L for Landscape
You next navigate to

Install printer -> style
You query for Landscape with SRW driver as L

Look for SRW Driver L for Landscape

Then move to Printer Types
Here you have to define a new type called Hplaser2105 and description as PCL

Create a new Printer TYPE
You select as HPPD, HPW, LANDSCAPE, LANDWIDE,PORTRAIT, PORTRAITLASERCHECK, RL1PAPER styles and correspondence driver files with HPLJ4.

Now. You register the printer
Install:Printer-> Register

Registering PrinterHere I have defined the printer as HPLASER2105, Selected type as HPLASER2105 and Description as Windows Remote Printer Configuration.

Set Profile option for Printer at site level – Most important.

That’s all, we completed. Now
1. Bounce the concurrent Service. It is most important. Whenever, you are doing modification with printer, you should do bouncing the concurrent service.

2. Now run a concurrent request to print a report

3. Here Selected Active users

Print Test with any report (Here I used Active Users)Click Options.
There you select copies as 1.

Then submit with OK
Now, the concurrent manager should complete with status completed normal. Review the log file.
If any thing happened in middle, please review the steps. Thats All.. Finished Printer configuration in Oracle Applications.

Installing PASTA (FNDPSTAX)Pasta is Oracle Application utility to convert text report files to Post Script so that these can be printed by PostScript Printers. Executable name for Pasta is FNDPSTAX. For various printing options via Pasta, you can use pasta configuration file called as pasta.cfg

The pasta.cfg is there in $FND_TOP/resource.

Copy the Pasta.cfg to Pasta_(Your printername).cfg. (Oracle recommends to copy the name to your printer name) PASTA makes it easy to customize your printing configuration very easy. So use the OS Printer name to define the Pasta.
1. Replace printer_name parameter in pasta.cfg as one with your OS printer name. Here my printer name is HPlaser2105
The command lp –c –d{printername} should print with command line.
Please check before you make changes with your pasta.cfg.
Screen Shot.

Save and exit.
Login to Oracle Apps as System admin
Navigation Path: Install -> Printer ->Driver -> create a driver, PASTA_PORTRAIT. There will be default PASTA-DRIVERS in built with Oracle Apps, We can use them.
For Portrait -> use SRW driver as P
For LANDSCAPE -> use SRW driver as L
For LANDWIDE -> use SRW driver as LW
You please check the Spool File and program name as FNDPSTAX.
Next:
Install -> Printer -> Type
Here I have created PASTA_POSTSCRIPT_HPLASER2105 with all the drivers.
Then you register.
Install -> Printer -> Register.
Save and Exit.

Restart your concurrent Manager (Important)

Check with Active Reports
You please note, the language I selected As Arabic and PASTA as the printer. Submit the request, It should completed normal. In case any problem in the middle, then review the steps.

Cheers!!!

Customizations in Oracle Applications - Purchase, Inventory and Payables

 Customizations in Oracle Applications - Purchase, Inventory and Payables

Part A: Purchasing/Inventory
1. Item – Customer- Approved Supplier Trigger for PO
2. Item – Customer- Approved Supplier Trigger for RFQ
3. DFF Copy from Requisition to PO
4. DFF Copy from Requisition to RFQ5. Quotation Workflow trigger
Part B: Accounts Payable

1.Prepayment Trigger


**Please don't implement any of the customisation's on to your application untill aware of the architecture/ process flow. These changes are done as per client's needs and may not fit into your process. This post is only for reference purpose.


Part A: Purchase/Invenotry

1. Item – Customer- Approved Supplier Trigger for POBusiness Requirement: A particular Item is approved for a particular Supplier by a particular Customer.

Customization: Customer field added in Approved Supplier List line level DFF. Trigger GS_DWE_PO_ASL_CUST_TRG_PO written.

Result: While making POs this trigger enforces that only the Item-Supplier combination which is approved by a particular Customer are chosen. If the same is not done, then it raises an application error.
TRIGGER GS_DWE_PO_ASL_CUST_TRG_PO
before insert or Update on PO_LINES_ALL
for each row
Declare

Cursor C1 is
Select c.vendor_id ASL_VENDOR_ID,
c.vendor_site_id ASL_VENDOR_SITE_ID,
c.item_id,
c.ATTRIBUTE10 as pasl_attribute,
a.VENDOR_ID PO_VENDOR_ID,
a.VENDOR_SITE_ID PO_VENDOR_SITE_ID,
a.ATTRIBUTE14 as poha_attribute
from PO_HEADERS_ALL a,
--PO_LINES_ALL b,
PO_APPROVED_SUPPLIER_LIST c
Where c.ITEM_ID = :New.ITEM_ID
And c.ATTRIBUTE_CATEGORY = 'Customer'
And a.ATTRIBUTE_CATEGORY = 'Header Details'
And a.ATTRIBUTE14 = c.ATTRIBUTE10
And a.PO_HEADER_ID = :NEW.PO_HEADER_ID;

Cur_Row C1%ROWTYPE;

Begin

Open C1;

LOOP
Fetch C1 into Cur_Row;

Exit when C1%NOTFOUND;

If (Cur_Row.ASL_VENDOR_ID <> Cur_Row.PO_VENDOR_ID)
Then
Raise_application_error(-20001,'Item-Supplier is not approved for this customer selected in DFF');
--exit;
Elsif ((Cur_Row.ASL_VENDOR_ID = Cur_Row.PO_VENDOR_ID) and (nvl(Cur_Row.ASL_VENDOR_SITE_ID,0) <> Cur_Row.PO_VENDOR_SITE_ID))
Then
Raise_application_error(-20001,'Item-Supplier Site is not approved for this customer selected in DFF');
-- exit;
Else
null;
End if;


END LOOP;
Close C1;

End;


2. Item – Customer- Approved Supplier Trigger for RFQ

Business Requirement: A particular Item is approved for a particular Supplier by a particular Customer.

Customization: Customer field added in Approved Supplier List line level DFF. Trigger gs_approved_supp_trg_rfq written.

Result: While making POs this trigger enforces that only the Item-Supplier combination which is approved by a particular Customer are chosen. If the same is not done, then it raises an application error.

TRIGGER gs_approved_supp_trg_rfq
before insert or update on PO_RFQ_VENDORS
for each row
Declare
Customer_Supplier_Error exception;
cursor c12 is

select
c.vendor_id as poasl_ven_id
,c.vendor_site_id as poasl_ven_site_id
,c.item_id as poasl_item_id
,c.attribute10 as poasl_cust_name
,a.attribute14 as poha_cust_name
,:NEW.vendor_id as porfq_ven_id
,:NEW.VENDOR_SITE_ID as porfq_ven_site_id
from
po_headers_all a,
po_lines_all b,
PO_APPROVED_SUPPLIER_LIST c,
po_rfq_vendors d
where
a.ATTRIBUTE_CATEGORY = 'Header Details'
and
c.ATTRIBUTE_CATEGORY = 'Customer'
and
a.attribute14=c.attribute10
and
b.item_id=c.item_id
and
a.po_header_id=:NEW.po_header_id
and
a.po_header_id=b.po_header_id
--and
-- d.po_header_id = :NEW.po_header_id
;


cur_row C12%ROWTYPE;

Begin

Open C12;

LOOP
Fetch C12 into cur_row;

Exit when C12%NOTFOUND;

if(cur_row.poasl_ven_id !=cur_row.porfq_ven_id) then
--Raise_application_error(-20001,'Item-Supplier is not approved for this customer selected in DFF');
Raise Customer_Supplier_Error;
elsif((cur_row.poasl_ven_id =cur_row.porfq_ven_id) and (nvl(cur_row.poasl_ven_site_id,0)!= cur_row.porfq_ven_site_id)) then
--Raise_application_error(-20001,'Item-Supplier is not approved for this customer selected in DFF');
Raise Customer_Supplier_Error;
else
null;
end if;

END LOOP;
CLOSE C12;

EXCEPTION
WHEN Customer_Supplier_Error THEN
FND_MESSAGE.set_name('PO', 'GS_INVALID_CUSTOMER_SUPPLIER');
FND_MESSAGE.set_token('MSG', 'Supplier not approved for the selected item and customer combination');
FND_MESSAGE.raise_error;

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END;


3. DFF Copy from Requisition to PO
Business Requirement: We usually capture lot of information at header and line level DFF. Some of this information is repetitive in the Purchase cycle (Requisition to PO and Requisition-RFQ-Quotation-PO) and thus should be copied automatically.

Customization: Trigger gs_dff_copy_req2po written.

Result: As soon as the PO is auto created from the requisition, the required DFF fields are copied.
TRIGGER gs_dff_copy_req2po
after insert or update
on
PO_DISTRIBUTIONS_ALL
for each row
Declare

cursor DFFCOPY is

select distinct
a.REQUISITION_HEADER_ID as req_header_id
,TRIM(a.ATTRIBUTE_CATEGORY) as req_attribute_category
,TRIM(a.ATTRIBUTE10) as req_billable_status
,TRIM(a.ATTRIBUTE11) as req_project_number
,TRIM(a.ATTRIBUTE14) as req_customer_name
,d.PO_HEADER_ID as po_header_id

from


PO_REQUISITION_HEADERS_ALL a
,PO_REQUISITION_LINES_ALL b
,PO_REQ_DISTRIBUTIONS_ALL c
,PO_HEADERS_ALL d


where
((c.DISTRIBUTION_ID=:NEW.REQ_DISTRIBUTION_ID) OR (a.SEGMENT1=:NEW.REQ_HEADER_REFERENCE_NUM))
and
c.REQUISITION_LINE_ID=b.REQUISITION_LINE_ID
and
b.REQUISITION_HEADER_ID=a.REQUISITION_HEADER_ID
and
d.PO_HEADER_ID=:NEW.PO_HEADER_ID
;


/***** Declare cursor variabeles *****/

cur_row DFFCOPY%ROWTYPE;

BEGIN
OPEN DFFCOPY;
LOOP
FETCH DFFCOPY INTO cur_row;
EXIT WHEN DFFCOPY%NOTFOUND;

update
PO_HEADERS_ALL f
set
f.ATTRIBUTE_CATEGORY=cur_row.req_attribute_category
,f.ATTRIBUTE10=cur_row.req_billable_status
,f.ATTRIBUTE11=cur_row.req_project_number
,f.ATTRIBUTE14=cur_row.req_customer_name

where
f.PO_HEADER_ID=cur_row.PO_HEADER_ID
and
f.ATTRIBUTE_CATEGORY IS NULL
and
f.ATTRIBUTE15 IS NULL
;

END LOOP;
CLOSE DFFCOPY;

END;

4. DFF Copy from Requisition to RFQ

Business Requirement: We usually capture lot of information at header and line level DFF. Some of this information is repetitive in the Purchase cycle (Requisition to PO and Requisition-RFQ-Quotation-PO) and thus should be copied automatically.

Customization: Trigger gs_dff_copy_req2rfq written.

Result: As soon as the RFQ is auto created from the requisition, the required DFF fields are copied.
CREATE OR REPLACE TRIGGER gs_dff_copy_req2rfq
after insert or update
on
PO_LINE_LOCATIONS_ALL
for each row
Declare
cursor DFFCOPY is

select distinct
PORHA.REQUISITION_HEADER_ID as req_header_id
,TRIM(PORHA.ATTRIBUTE_CATEGORY) as req_attrib_cat
,TRIM(PORHA.ATTRIBUTE10) as req_billable_status
,TRIM(PORHA.ATTRIBUTE11) as req_project_number
,TRIM(PORHA.ATTRIBUTE14) as req_customer_name
,POHA.PO_HEADER_ID as po_header_id
from
PO_REQUISITION_HEADERS_ALL PORHA
,PO_REQUISITION_LINES_ALL PORLA
,PO_REQ_DISTRIBUTIONS_ALL PORDA
,PO_HEADERS_ALL POHA
where
POHA.PO_HEADER_ID=:NEW.PO_HEADER_ID
and
PORLA.LAST_UPDATE_DATE = POHA.CREATION_DATE
and
PORHA.REQUISITION_HEADER_ID = PORLA.REQUISITION_HEADER_ID
and
POHA.type_lookup_code = 'RFQ'
and
PORLA.ON_RFQ_FLAG = 'Y'
;

/***** Declare cursor variabeles *****/

cur_row DFFCOPY%ROWTYPE;

BEGIN
OPEN DFFCOPY;
LOOP
FETCH DFFCOPY INTO cur_row;
EXIT WHEN DFFCOPY%NOTFOUND;

update
PO_HEADERS_ALL a
set
a.ATTRIBUTE_CATEGORY=cur_row.REQ_ATTRIB_CAT
,a.ATTRIBUTE10=cur_row.req_billable_status
,a.ATTRIBUTE11=cur_row.req_project_number
,a.ATTRIBUTE14=cur_row.req_customer_name
where
a.PO_HEADER_ID=cur_row.PO_HEADER_ID
and
a.ATTRIBUTE_CATEGORY IS NULL
and
a.ATTRIBUTE15 IS NULL
;

END LOOP;
CLOSE DFFCOPY;

END;

5. Quotation Workflow trigger

This trigger checks if the Quotation from which a PO is being made is approved or not, if it is not then it raises an application error and blocks the creation of the PO.

The main query which selects a particular quotation number if it has been approved is pasted below. This query uses the wf_notifications table to pick out the quotations which have been approved.
select
to_number(substr(wfn.subject,instr(wfn.subject,'(')+1,instr(wfn.subject,')')-instr(wfn.subject,'(')-1)) as wf_qt_number
from
wf_notifications wfn
,po_headers_all poha

where
wfn.MESSAGE_TYPE='QTAPPRV'
and
substr(wfn.subject,1,1)='Q'
and
wfn.MESSAGE_NAME='APPROVER_TO_REQUESTOR_COMPLETE'
and
wfn.STATUS='CLOSED'
and
to_number(substr(wfn.subject,instr(wfn.subject,'(')+1,instr(wfn.subject,')')-instr(wfn.subject,'(')-1)) in
(select
poha.segment1
from
po_headers_all poha
where
poha.TYPE_LOOKUP_CODE='QUOTATION'
and
po_header_id in (select
poha.from_header_id
from
po_headers_all poha,
po_lines_all pola
where
poha.po_header_id=pola.po_HEADER_ID
and
poha.TYPE_LOOKUP_CODE in ('STANDARD', 'BLANKET')
and
poha.FROM_TYPE_LOOKUP_CODE='QUOTATION')
)

Part B: Accounts Payable

Business Requirement:

Payment should only be made to supplier when the PO has been ‘Accepted’ by him.Advance Payment should not be more than the same mentioned in Payment Terms. Also, ability to pay more than that amount should lie with few people (user) only.

Customization: Trigger GS_PREPAYMENT written.
Result:
1. Payment can only be made once PO is accepted by Supplier.
2. Prepayment cannot be done above the amount mentioned in Payment Terms. Only specified user can pay more than the amount mentioned in payment terms.
1. Prepayment Trigger

(to be added: check if invoice amount is more than agreed advance from payment terms)

TRIGGER GS_PREPAYMENT
BEFORE INSERT OR UPDATE ON AP_INVOICES_ALL
FOR EACH ROW
DECLARE

PREPAY_INVOICE_PO_ERROR exception;
PREPAY_INVOICE_CONFIRM_ERROR exception;
PREPAY_INV_UNAUTH_USER_ERROR exception;
PREPAY_INV_BYPASS_ERROR exception;

P_PO_Header_Id number;
P_Vendor_Type PO_VENDORS.VENDOR_TYPE_LOOKUP_CODE%TYPE;
P_User_Name FND_USER.USER_NAME%TYPE;

BEGIN


Select VENDOR_TYPE_LOOKUP_CODE
Into P_Vendor_Type
From PO_VENDORS a
Where a.VENDOR_ID = :New.VENDOR_ID;

If P_Vendor_Type = 'EMPLOYEE' Then
return;
End if;

Select User_Name
Into P_User_Name
From FND_USER a
Where a.USER_ID = :New.Created_By;



If :New.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' and :New.PO_HEADER_ID is null
Then
Raise PREPAY_INVOICE_PO_ERROR;
End if;

If :New.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' and :New.PO_HEADER_ID is not null Then
Select count(1)
into P_PO_Header_Id
from po_acceptances_v a
where a.ACCEPTED_FLAG = 'Y'
and a.PO_HEADER_ID = :New.PO_HEADER_ID;

If P_PO_Header_Id <> 1 Then
If nvl(:New.Attribute1,'x') = 'Y' and P_User_Name = 'PARVEZ' Then -- Hardcode. Pls change accordingly
return;
Elsif nvl(:New.Attribute1,'x') <> 'Y' and P_User_Name <> 'PARVEZ' Then
Raise PREPAY_INVOICE_CONFIRM_ERROR;
Elsif nvl(:New.Attribute1,'x') = 'Y' and P_User_Name <> 'PARVEZ' Then
Raise PREPAY_INV_UNAUTH_USER_ERROR;
Elsif nvl(:New.Attribute1,'x') <> 'Y' and P_User_Name = 'PARVEZ' Then
Raise PREPAY_INV_BYPASS_ERROR;
End if;
Else
return;
End If;

End if;

EXCEPTION
WHEN PREPAY_INVOICE_PO_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_INVALID_PREPAYMENT');
FND_MESSAGE.set_token('MSG', 'PO Number required for creating the Prepayment invoice');
FND_MESSAGE.raise_error;

WHEN PREPAY_INVOICE_CONFIRM_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_PREPAYMENT_PO_NOT_CONFIRM');
FND_MESSAGE.set_token('MSG', 'PO not confirmed. Prepayment can not be processed');
FND_MESSAGE.raise_error;

WHEN PREPAY_INV_UNAUTH_USER_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_INVALID_PREPAYMENT_USER');
FND_MESSAGE.set_token('MSG', 'You are unauthorized for bypassing PO confirmation');
FND_MESSAGE.raise_error;

WHEN PREPAY_INV_BYPASS_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_PREPAYMENT_BYPASS_PO');
FND_MESSAGE.set_token('MSG', 'Please set Bypass PO Confirmation to Yes before proceeding');
FND_MESSAGE.raise_error;

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END GS_PREPAYMENT;

Enabling DFF In Oracle Standard Forms

 Enabling DFF In Oracle Standard Forms

1. Identify Descriptive fields used in a form .



Go to Help -> Diagnostic -> Examine ->Block($DESCRIPTIVE FLEXFIELD)

2. Open Descriptive flexfield segment




Go to Setup ->Flexfields ->Descriptive ->Segments


3. Enable Descriptive Flexfield.




·Define Code , name & description for new descriptive flexfield
·Un-check Freeze Flexfield Definition


4. Define Segments for a flexfield


.Define Segment Name and Attibute.
.Define Valuset if any.


5. Compile the Flexfield


.Check Freeze Flexfield Definition.
.Compile the Flexfield to see effect on standard oracle form.


6. Flexfield on form

BACKUPS (DBA)

BACKUPS

Cold Backup & Hot Backup

Having the right backup and recovery procedures is the lifeblood of any database. Companies live on data, and, if that data is not available, the whole company collapses. As a result, it is the responsibility of the database administrator to protect the database from system faults, crashes, and natural calamities resulting from a variety of circumstances.

The choice of a backup and recovery mechanism depends mainly on the following factors:

Database mode (ARCHIVELOG, NOARCHIVELOG)

Size of the database

Backup and recovery time

uptime

Type of data (OLTP, DSS, Data Warehouse).

The types of backup are

Offline backup (Cold or closed database backup)

Online backup (Hot or open database backup)

Logical export

Logical exports create an export file that contains a list of SQL statements to recreate the database. Export is performed when the database is open and does not affect users work. Offline backups can only be performed when the database is shut down cleanly, and the database will be unavailable to users while the offline backup is being performed. Online backups are performed when the database is open, and it does not affect users work. The database needs to run in ARCHIVELOG mode to perform online backups.

The database can run in either ARCHIVELOG mode or NOARCHIVELOG mode. In ARCHIVELOG mode, the archiver (ARCH) process archives the redo log files to the archive destination directory. These archive files can be used to recover the database in the case of a failure. In NOARCHIVELOG mode, the redo log files are not archived.

When the database is running in ARCHIVELOG mode, the choice can be one or more of the following:

Export

Hot backup

Cold backup

When the database is running in NOARCHIVELOG mode, the choice of backup is as follows:

Export

Cold backup

Cold Backup

Offline or cold backups are performed when the database is completely shutdown. The disadvantage of an offline backup is that it cannot be done if the database needs to be run 24/7. Additionally, you can only recover the database up to the point when the last backup was made unless the database is running in ARCHIVELOG mode.

The general steps involved in performing a cold backup are shown in Figure 3.1. These general steps are used in writing cold backup scripts for Unix and Windows NT.

Steps for cold backup.

Step 1—Generating File List

An offline backup consists of physically copying the following files:

Data files

Control files

Init.ora and config.ora files

CAUTION

Backing up online redo log files is not advised in all cases, except when performing cold backup with the database running in NOARCHIVELOG mode. If you make a cold backup in ARCHIVELOG mode do not backup redo log files. There is a chance that you may accidentally overwrite your real online redo logs, preventing you from doing a complete recovery.

If your database is running in ARCHIVELOG mode, when you perform cold backup you should also backup archive logs that exist.

Before performing a cold backup, you need to know the location of the files that need to be backed up. Because the database structure changes day to day as more files get added or moved between directories, it is always better to query the database to get the physical structure of database before making a cold backup.

To get the structure of the database, query the following dynamic data dictionary tables:

V$datafile Lists all the data files used in the database

SQL>select name from v$datafile;

Backup the control file and perform a trace of the control file using

SQL>alter database backup controlfile to '/u10/backup/control.ctl';

SQL>alter database backup controlfile to trace;

Init.ora and config.ora Located under $ORACLE_HOME/dbs directory

Step 2—Shut down the database

You can shut down a database with the following commands:

$su – oracle

$sqlplus "/ as sysdba"

SQL>shutdown

Step 3—Perform a backup

In the first step, you generated a list of files to be backed up. To back up the files, you can use the Unix copy command (cp) to copy it to a backup location, as shown in the following code. You have to copy all files that you generated in Step 1.

$cp /u01/oracle/users01.dbf /u10/backup

You can perform the backup of the Init.ora and config.ora files as follows:

$cp $ORACLE_HOME/dbs/init.ora /u10/backup

$cp $ORACLE_HOME/dbs/config.ora /u10/backup

Step 4—Start the database

After the backup is complete, you can start the database as follows:

$su – oracle

$sqlplus "/ as sysdba"

SQL> startup

Hot Backup

An online backup or hot backup is also referred to as ARCHIVE LOG backup. An online backup can only be done when the database is running in ARCHIVELOG mode and the database is open. When the database is running in ARCHIVELOG mode, the archiver (ARCH) background process will make a copy of the online redo log file to archive backup location.

An online backup consists of backing up the following files. But, because the database is open while performing a backup, you have to follow the procedure shown in Figure 3.2 to backup the files:

Data files of each tablespace

Archived redo log files

Control file

Init.ora and config.ora files

Steps for hot backup.

The general steps involved in performing hot backup are shown in Figure 3.2. These general steps are used in writing hot backup scripts for Unix and Windows NT.

The steps in Figure 3.2 are explained as follows.

Step 1—Put the tablespace in the Backup mode and copy the data files.

Assume that your database has two tablespaces, USERS and TOOLS. To back up the files for these two tablespaces, first put the tablespace in backup mode by using the ALTER statement as follows:

SQL>alter tablespace USERS begin backup;

After the tablespace is in Backup mode, you can use the SELECT statement to list the data files for the USERS tablespace, and the copy (cp) command to copy the files to the backup location. Assume that the USERS tablespace has two data files—users01.dbf and users02.dbf.

SQL>select file_name from dba_data_files

where tablespace_name='USERS';

$cp /u01/oracle/users01.dbf /u10/backup

$cp /u01/oracle/users01.dbf /u10/backup

The following command ends the backup process and puts the tablespace back in normal mode.

SQL>alter tablespace USERS end backup;

You have to repeat this process for all tablespaces. You can get the list of tablespaces by using the following SQL statement:

SQL>select tablespace_name from dba_tablespaces;

Step 2—Back up the control and Init.ora files.

To backup the control file,

SQL>alter database backup controlfile to '/u10/backup/control.ctl';

You can copy the Init.ora file to a backup location using

$cp $ORACLE_HOME/dbs/initorcl.ora /u10/backup

Step 3—Stop archiving.

Archiving is a continuous process and, without stopping archiver, you might unintentionally copy the file that the archiver is currently writing. To avoid this, first stop the archiver and then copy the archive files to backup location. You can stop the archiver as follows:

SQL>alter system switch logfile;

SQL>alter system archive log stop;

The first command switches redo log file and the second command stops the archiver process.

Step 4—Back up the archive files.

To avoid backing up the archive file that is currently being written, we find the least sequence number that is to be archived from the V$LOG view, and then backup all the archive files before that sequence number. The archive file location is defined by the LOG_ARCHIVE_DEST_n parameter in theInit.ora file.

select min(sequence#) from v$log

where archived='NO';

Step 5—Restart the archive process.

The following command restarts the archiver process:

SQL>alter system archive log start;

Now you have completed the hot backup of database.

An online backup of a database will keep the database open and functional for 24/7 operations. It is advised to schedule online backups when there is the least user activity on the database, because backing up the database is very I/O intensive and users can see slow response during the backup period. Additionally, if the user activity is very high, the archive destination might fill up very fast.

Database Crashes During Hot Backup

There can be many reasons for the database to crash during a hot backup—a power outage or rebooting of the server, for example. If these were to happen during a hot backup, chances are that tablespace would be left in backup mode. In that case you must manually recover the files involved, and the recovery operation would end the backup of tablespace. It's important to check the status of the files as soon as you restart the instance and end the backup for the tablespace if it's in backup mode.

select a.name,b.status from v$datafile a, v$backup b

where a.file#=b.file# and b.status='ACTIVE';

or

select a.tablespace_name,a.file_name,b.status from dba_data_files a,

v$backup b

where a.file_id=b.file# and b.status='ACTIVE';

This statement lists files with ACTIVE status. If the file is in ACTIVE state, the corresponding tablespace is in backup mode. The second statement gives the tablespace name also, but this can't be used unless the database is open. You need to end the backup mode of the tablespace with the following command:

alter tablespace tablespace_name end backup;

Logical Export

Export is the single most versatile utility available to perform a backup of the database, de-fragment the database, and port the database or individual objects from one operating system to another operating system.

Export backup detects block corruption

Though you perform other types of backup regularly, it is good to perform full export of database at regular intervals, because export detects any data or block corruptions in the database. By using export file, it is also possible to recover individual objects, whereas other backup methods do not support individual object recovery.

Export can be used to export the database at different levels of functionality:

Full export (full database export) (FULL=Y)

User-level export (exports objects of specified users) (OWNER=userlist)

Table-level export (exports specified tables and partitions) (TABLES=tablelist)

Transportable tablespaces (TABLESPACES=tools, TRANSPORT_TABLESPACE=y)

There are two methods of Export:

Conventional Path (default)—Uses SQL layer to create the export file. The fact is that the SQL layer introduces CPU overhead due to character set, converting numbers, dates and so on. This is time consuming.

ü Direct path (DIRECT=YES)—Skips the SQL layer and reads directly from database buffers or private buffers. Therefore it is much faster than conventional path.

We will discuss scripts to perform the full, user-level, and table-level export of database. The scripts also show you how to compress and split the export file while performing the export. This is especially useful if the underlying operating system has a limitation of 2GB maximum file limit.

Understand scripting

This chapter requires understanding of basic Unix shell and DOS batch programming techniques that are described in Chapter 2 "Building Blocks." That chapter explained some of the common routines that will be used across most of the scripts presented here.

This book could have provided much more simple scripts. But, considering standardization across all scripts and the reusability of individual sections for your own writing of scripts, I am focusing on providing a comprehensive script, rather than a temporary fix. After you understand one script, it is easy to follow the flow for the rest of the scripts.