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;