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 RFQ
5. 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 PO

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_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;