Error – trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : Attribute DEDUCTOR_TYPE is not defined

While validating Invoice on which TDS is applied via DFF - additional information for India , system is giving this error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : Attribute DEDUCTOR_TYPE is not defined

As mentioned in the error message Deductor type is not defined, hence system is not allowing to validate the Invoice’s. Do the following setup in your instance.

Navigation:

India Local Payables > India TCS/VAT/Service Tax/FBT > Setup > Regime Registration

Query the TDS tax regime

In the Regime Registration Details section, add the value Registration Type = 'Others' and Registration Name = 'Deductor Type'. Select the appropriate value for the field 'Registration Value' and Save.

After doing this setup system will allow to valide the Invoice.

---X---

How to bring Excise/VAT Invoice number in ‘Reference’ field on Receivables Transaction Workbench in Oracle R12 ?

For displaying Excise and VAT Invoice number in the Reference field the profile option ‘JAI: Include Excise and VAT Invoice Number in AR transactions – Reference’ should be set to ‘Yes’

A new procedure has been introduced in Release 12.1.3 for updation of Excise/VAT invoice Number on Receivable Transaction Workbench Reference. JAI_AR_MATCH_TAX_PKG.display_vat_invoice_number. 

If the VAT/Excise Invoice number has already been generated before the Auto Invoice import process then run ‘India - Local Concurrent for Processing Order Lines to AR’ which fires the necessary procedure and brings the VAT/Excise Invoice number in Reference field.

Run concurrent ‘India - Importing Excise/VAT Invoice Numbers in AR’, if the VAT/Excise Invoice number is generated after the auto invoice import process.

In case of Manual Receivable transactions, concurrent Program 'India - Update Excise/VAT Invoice Number in Reference of Receivables Invoice' shall be invoked to populate Excise/VAT invoice Number on Receivable transaction workbench reference. This concurrent will be invoked on its own as soon as the Manual AR transaction is being completed.

Note:

The Invoice source used in Manual Invoice should be a bonded one; else Excise Invoice number won’t be generated.

---X---

ORA-06503: PL/SQL Function returned without value in Package INV_Validate_Trolin Procedure Attributes

When trying to create a new batch, following error is occurring:

ORA-06503: PL/SQL Function returned without value in Package INV_Validate_Trolin Procedure Attributes 

Cause:

The issue is occurring when, an item is used in the formula with an UOM that is neither primary or secondary item UOM and there is no conversion defined between the formula UOM and primary or secondary UOM.

Solution:

Check whether wrong UOM is entered mistakenly in Formula. If UOM’s were entered as required then define correct UOM conversions for items used in formula.

---X---

How to change the order in which the Periods appear in Control Purchasing Periods page ?

By default the sorting in the Control Purchasing Periods page happens in the following order - Closed, Never Opened and then Open. This does not make any business sense and it should be sorted by the Period name like the latest period first, then the previous periods and so on. Right now to view the open periods it is necessary to move to the nth page (depending upon the number of years Oracle Apps is in use) to obtain the data. 

Even-though currently there is no functionality to sort based on the Period status, through the following workaround we can achieve the above mentioned purpose.

Click on “Personalize Page”

Expand Advanced Table: Control Purchasing Period > Column: (NumberColumn) > Column Header > Sortable Header: Period Number.

Click Update button

At all the levels “Inherit” will be defaulting.

 

Against “Sort Allowed” change it to “Yes” at the desired level.

Log out of the application. Sign back on.

Open Control Purchasing Periods page, type a value into the “Fiscal year” field & Click Go

Now by clicking on the “Period Number” it should sort ascending and descending.

Note:

This is applicable only for versions 12.0.0 to 12.1

Customers have already raised an enhancement request to display Purchasing periods in chronological order like in all other modules. Hope Oracle will rectify this bug in 12.2 releases.

---X---

How Shipping Dates are calculated in Oracle R12

Delivery's 'Initial Pickup' and 'Ultimate Ship to Date' are calculated based on ‘Delivery Date Calculation Method’ provided in Shipping Global Parameter's setup. It can be either of following

• Earliest Ship Date, Latest Delivery Date
• Scheduled Date, Requested Date

Navigation:

Oracle Order Management > Shipping > Setup > Global Parameters

Initial Pickup Date:

‘Initial Pickup Date’ is the date on which the items will be shipped out of the warehouse. While creating delivery or assigning delivery line to a delivery, Initial Pick up date is calculated.

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Earliest Ship Date, Latest Delivery Date' then Initial Pickup Date will be Max of (Earliest Pickup Date)

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Scheduled Date, Requested Date' then Initial Pickup Date will be Max of (Date Scheduled)

While closing the pick-up stop manually or during Ship Confirm process, 'Initial Pickup Date' will always be updated with 'Actual Departure Date' (Initial Pickup Date = Actual Departure Date).

Ultimate Ship to Date:

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Earliest Ship Date, Latest Delivery Date' then Ultimate Ship to date will be NVL(Min(Latest Dropoff date), Greatest[Min(Date Requested), Min(Date Scheduled)])

(i.e) Ultimate Ship to date will be Min of (Latest Dropoff date). If Latest Dropoff date field value is null then whichever date is greater in between Min (Date Requested) and Min (Date Scheduled) will be taken as Ultimate Ship to date.

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Scheduled Date, Requested Date' then Ultimate Ship to date will be Min (Date Requested) if it less than Min (Date Scheduled) else Ultimate Ship to date will be Min (Date Scheduled).

If derived ‘Ultimate Ship to Date’ happens to be less than 'Initial Pickup Date' derived then Ultimate Ship to Date = Initial Pickup Date.

While closing the pick-up stop manually or during Ship Confirm process, if 'Ultimate Ship to Date' is less than the 'Actual Departure Date' then Ultimate Ship to Date = Actual Departure Date.

Note:

'Delivery Date Calculation Method' in Global Parameter field can be left null also. If left null, the Initial Pickup Date and Ultimate Ship to Date will be Max of (System Date)

---X---

Interface Trip Stop – SRS Program

The Interface Trip Stop (ITS) will be triggered at the time of Ship Confirm if the check box ‘Defer Interface’ is not checked. ITS can also be executed from concurrent request.

Interface Trip Stop does the following things,

• Updating the Order Management Data (OE_ORDER_LINES_ALL)
• Triggering the Inventory Interface (to update the Inventory tables)

ITS updates the following fields on OE_ORDER_LINES_ALL table:

• Shipped_Quantity
• Shipping_Quantity
• Actual_Shipment_Date

Inventory Interface will be triggered only if the first part, that means related to OM has successfully completed and flag OE_INTERFACED_FLAG = ‘Y’ on WSH_DELIVERY_DETAILS.  

If value of this flag is N/P then Inventory Interface will never be triggered. And even if you try to submit the Inventory Interface - SRS, delivery detail with OE_INTERFACED_FLAG =’N’ will never be picked up.

OE_INTERFACED_FLAG = Y -- signifies ITS has interfaced shipping data to OM

INV_INTERFACED_FLAG = Y -- signifies ITS has interfaced shipping data to INV

As mentioned earlier if the ‘Defer Interface’ checkbox is checked then Interface trip stop concurrent will not be triggered automatically, we should run it manually.

Navigation:

Oracle Order Management > Shipping > Interfaces > Run

Select Mode as ‘All’

If you want to process a particular transaction, then select the same from the list of values of ‘Trip Stop’ and ‘Delivery’.

If wanted to process all the pending transactions leave the fields blank and submit the concurrent.

As scheduling other concurrent’s, we can also schedule ITS – SRS process to run at regular intervals to process different trip stops.

But keep in mind that Deadlock errors may occur when multiple Interface trip stop processes are running at the same time.

To avoid this we have to make ITS and ITS-SRS processes incompatible with themselves as follows,

Navigation: Application Developer > Concurrent > Program

Query ‘Interface Trip Stop – SRS’ program, Click ‘Incompatibilities’ box.

In the Name field select ‘Interface Trip Stop’. For ‘Scope’ select ‘Program’ and for ‘Type’ select ‘Global’. If you select 'domain' for type, then the system will allow two ITS processes to run simultaneously if they are run in different domains. 

Now Query ‘Interface Trip Stop’ and make it incompatible with ‘Interface Trip Stop – SRS’ in the same way as above.

This will prevent more than one ITS or ITS-SRS process from running at the same time.  All other ITS or ITS-SRS processes will be added to a queue and will be processed when the current ITS or ITS-SRS process completes.  Therefore, multiple ITS processes will not become deadlocked with each other.

---X---

Please run and save all Account and Inventory Valuation report outputs before changing any valuation accounts.

Sub-inventory form allows changing valuation accounts only if there is no on-hand in sub-inventory, no un-costed transactions in MTL_MATERIAL_TRANSACTIONS and no pending transactions (MTL_MATERIAL_TRANSACTIONS_TEMP).

When attempted to change the valuation accounts, system will display a warning message “Please run and save all Account and Inventory Valuation report outputs before changing any valuation accounts.”

This is a warning message. We can choose ‘OK’, then proceed with changing the accounts. If ‘Cancel’ is clicked then the existing old account value is put back for the account.

Note:

To avoid reconcillation issues it is recommended to create manual jounal entries to transfer the balance from the discontinued accounts and the accounts be changed at the time of period close.

---X---

Tables involved in Order to Cash (O2C) Cycle - Oracle R12

Order Entry:

OE_ORDER_HEADERS_ALL

At the time of Order entry, the Flow_Status_Code is ‘Entered’

OE_ORDER_LINES_ALL

OE_PRICE_ADJUSTMENTS – when discount gets applied

OE_ORDER_PRICE_ATTRIBS – if line has price attributes

OE_ORDER_HOLDS_ALL – if any hold applied for order like Credit check etc…

Header_id is the link between OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL tables.

Order Booking:

WSH_DELIVERY_DETAILS

WSH_DELIVERY_ASSIGNMENTS

When the Order is Booked, the Flow_Status_Code in OE_ORDER_HEADERS_ALL will be ‘Booked’ and the Flow_Status_Code in OE_ORDER_LINES_ALL will be ‘Awaiting Shipping’.

New records will be created in WSH_DELIVERY_DETAILS with Released_Status=’R’ (Ready to Release). Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.

Launch Pick Release:

Flow_status_code in OE_ORDER_HEADERS_ALL will be ‘Picked’ or ‘Awaiting Shipping’ depending on Auto Pick Confirm (set to No or Yes)

Released_Status in WSH_DELIVERY_DETAILS will be ‘S’ (Submitted for Release) or ‘Y’ (Pick Confirmed). These values again depend on the parameters given at Auto Pick Confirm (set to No or Yes)

WSH_DELIVERY_ASSIGNMENTS - Delivery_ID is populated here (from Delivery_Detail_ID with reference to WSH_DELIVERY_DETAILS table)

MTL_TRANSACTIONS_INTERFACE is updated with Sales order Pick transaction.

Ship Confirm:

Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction.

Flow_status_code in OE_ORDER_LINES_ALL will be ‘Shipped’

Released_Status in WSH_DELIVERY_DETAILS will be ‘C’ (means shipped)

Creating Invoices in Receivables:

Workflow Background Process inserts new records in RA_INTERFACE_LINES_ALL table.

Auto Invoice Master Program transfers the data from RA_INTERFACE_LINES_ALL to the following tables,

RA_CUSTOMER_TRX_ALL: Stores Invoice header information.

RA_CUSTOMER_TRX_LINES_ALL: Stores Invoice lines information.

Creating Receipt:

AR_CASH_RECEIPTS_ALL

Flow_status_code in OE_ORDER_LINES_ALL should be ‘CLOSED’

---X---

Tables involved in Procure to Pay (P2P) Cycle - Oracle R12

Procure to Pay Lifecycle

Algorithmic representation of P2P Cycle

Purchase Requisition:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Requisition_header_id is the link between PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL tables

Requisition_line_id is the link between PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL tables

Purchase Order:

PO_RELEASES_ALL

PO_HEADERS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_DISTRIBUTIONS_ALL

PO_header_id is the link between all these tables

Receipt:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

RCV_TRANSACTIONS

Shipment_header_id is the link between RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINE.

PO_header_id is the link between RCV_SHIPMENT_HEADERS and RCV_TRANSACTIONS

Quality results are stored in QA_RESULTS table

Once the Receipt process is completed, MTL_ONHAND_QUANTITIES table gets updated.

Invoices:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Invoice_id is the link between all these tables

Payments:

AP_INVOICE_PAYMENTS_ALL

AP_PAYMENT_SCHEDULES_ALL

Check’s information will be stored in AP_CHECKS_ALL table.

Check format information will be stored in AP_CHECK_FORMATS table.

Note:

RFQ & Quotation records also get stored in the same PO_HEADERS_ALL table, but with type_lookup_code as ‘RFQ’ and ‘QUOTATION’ respectively.

---X---