White Paper Subledger Accounting for Intercompany Transaction Flows V5.6

White Paper Sub-Ledger Accounting Setups for Intercompany Transaction Flows

Author:

Wayne Greene

Creation Date:

Feb 5, 2011

Last Update:

July 13, 2012

Contributors And Reviewers: Shixin (Jason) Zhang Rixin Zhu

Overview File Ref: Document1 Company Confidential - For internal use only

1

Contents Define

Overview .................................................................................................................................. 4 Internal Drop Ship – without modified SLA....................................................................... 5 Internal Drop Ship – with modified SLA ............................................................................ 6 Application Accounting Definition ...................................................................................... 7 PL/SQL Functions .................................................................................................................. 8 Sub-Ledger Accounting Setups ............................................................................................ 9 Custom Sources ..................................................................................................................... 10 Assign Custom Sources to Event Class .............................................................................. 12 XLA Lookups......................................................................................................................... 14 Accounting Definition Rules ............................................................................................... 15 Logical Intercompany Event Class ..................................................................................... 16 Logical Intercompany Event Class – Journal Line Definitions ....................................... 17 Sales Order Issue Event Class ............................................................................................. 19 Sales Order Issue – Deferred COGS Journal Line Types ................................................. 20 Sales Order Issue – COGS Journal Line Types ................................................................. 23 Sales Order Issue – Inventory Valuation Journal Line Types ......................................... 25 PL/SQL Function - XYZ_SALE_INTERCO_FLAG .......................................................... 27 PL/SQL Function - XYZ_COGS_RECOG_INTERCO_FLAG......................................... 30 PL/SQL Function - XYZ_INTERNAL_DROP_RECEIPT ................................................ 33 Overview File Ref: Document1 Company Confidential - For internal use only

2

PL/SQL Function - XYZ_INTRNL_DROP_RCPT_MRKP .............................................. 37 PL/SQL Function - XYZ_INTERCO_SEGMENT ............................................................. 40 PL/SQL Function - XYZ_INTRNL_DROP_COGS_AMT ............................................... 43 PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT ................................................... 47


3

Overview This white paper describes how to modify sub-ledger accounting (SLA) for intercompany transactions when tracking of a separate intercompany markup dollar amount is required for internal drop ships across international boundaries. This is often a requirement for tax purposes. The current costing system does not provide for a separation of this markup. Having the markup as a separate accounting entry, instead of added into inventory, allows for accurate end of month intercompany eliminations. An internal drop ship is where an internal legal entity in one country takes a sales order from a customer, but then sources the shipment from an internal warehouse in a different country. This internal warehouse belongs to a different internal legal entity. The warehouse then ships directly to the customer across international boundaries. The country where the shipment comes from must collect taxes on that transaction based on the profit margin. The profit margin must be an unbiased arms-length amount. The markup percent is stored in advanced pricing. The markup amount will appear in the receiving legal entity as part of the Logical Intercompany Shipment Receipt transaction type. This paper does not address the advanced pricing setup or intercompany transactions flows. These setups are also required. It only addresses the SLA setup. Each setup below has a description column to further explain the usage and outcome of each setting. These settings have been successfully used in a standard costing production environment. They are currently being tested in an average costing environment with production go live scheduled for next year. The SLA settings do not address internal intercompany drop ship RMA. That is an ongoing development. Sub-ledger Accounting allows customers to modify the accounting entries just before they are transferred to the general ledger.


4

Internal Drop Ship – without modified SLA The accounting depicted below is the seeded accounting output for an internal drop ship without any SLA changes. Transaction Type Logical Intercompany Sales Issue Logical Intercompany Sales Issue

Description Shipper – at shipper standard cost Shipper – at shipper standard cost

Logical Intercompany Shipment Receipt


Receiver – at shipper standard cost plus 973.58 X .27% markup = 262.86 X currency conversion rate of 1.0079 = 1246.21 Receiver

Logical Sales Order Issue Logical Sales Order Issue

Receiver – when sell to 3 party customer rd Receiver – when sell to 3 party customer

Inventory - Finished Goods Inventory - Deferred COGS

1246.21

COGS Recognition COGS Recognition

COGS recognized when revenue is earned COGS recognized when revenue is earned

Inventory - Deferred COGS Cost of Goods Sold – third party

1246.21

rd

Account Inventory - Finished Goods Cost of sales - Intercompany

DR

CR 973.58

973.58 1246.21

Inventory - Finished Goods Inventory Accrual - Intercompany

1246.21 1246.21

1246.21

Internal Drop Ship – without modified SLA File Ref: Document1 Company Confidential - For internal use only

5

Internal Drop Ship – with modified SLA The accounting depicted below is the accounting output for an internal drop ship after these SLA changes have been made.

Transaction Type Logical Intercompany Sales Issue Logical Intercompany Sales Issue

Description Shipper – at shipper standard cost Shipper – at shipper standard cost


Receiver – 973.58 X currency onversion rate of 1.0079 = 981.27 Receiver – at shipper standard cost plus 973.58 X .27% markup = 262.86 X currency conversion rate of 1.0079 = 264.94


Logical Intercompany Shipment Receipt rd

Account Inventory - Finished Goods Cost of sales - Intercompany

DR

CR 973.58

973.58 981.27

Inventory - Finished Goods 264.94 Intercompany Markup - capitalized Inventory Accrual - Interco

Logical Sales Order Issue Logical Sales Order Issue

Receiver – when sell to 3 party customer rd Receiver – when sell to 3 party customer

Inventory - Finished Goods Inventory - Deferred COGS

COGS Recognition COGS Recognition

COGS recognized when revenue is earned COGS recognized when revenue is earned

Inventory - Deferred COGS Cost of Goods Sold – third party

1246.21 981.27 981.27 981.27 981.27

As can be seen from the example above, the shipping cost is multiplied by a markup of 27%. 973.58 X .27 = 262.86 Then a currency conversion rate of 1.0079 from shipping to receiving company is used to calculate the receiving markup entry. 262.86 X 1.0079 = 264.94 The inventory entry uses the shipping cost multiplied by the currency conversion rate. 973.58 X 1.0079 = 981.27 The receiving standard cost is not used because inventory does not physically enter the receiving warehouse. The receipt is virtual and used only to calculate third party COGS to be used to calculate profit from revenue on the customer facing transaction.

Internal Drop Ship – with modified SLA File Ref: Document1 Company Confidential - For internal use only

6

Application Accounting Definition Use this next form to create a new Application Accounting Definition. Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Methods and Definitions -> Application Accounting Definitions First find the old “COST MANAGEMENT” definition. Then use the copy button and modify as listed below.

Definition Definition

Name

Description

Chart of Accounts Transaction

Chart of Accounts Accounting

XYZ Corp Accounting Flexfield

XYZ Corp Accounting Flexfield

Code XYZ Cost Manager

XYZ Cost Manager

XYZ custom Cost Management

This step may have already been done by another track up until the last step: Use this next form to tie the newly created Application Accounting Definition to Cost Management. Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Methods and Definitions -> Subledger Accounting Methods First find the old Oracle “STANDARD_ACCRUAL” definition. Then use the copy button and modify as listed below. Method Code = XYZ_STANDARD_ACCRUAL Method Name = XYZ STANDARD ACCRUAL Description = XYZ STANDARD ACCRUAL Chart of Accounts Transaction = XYZ_CORP_ACCOUNTING_FLEXFIELD Chart of Accounts Accounting = XYZ_CORP_ACCOUNTING_FLEXFIELD Now modify a single row: Application = Cost Management Name = XYZ Cost Management Application Accounting Definition File Ref: Document1 Company Confidential - For internal use only

7

PL/SQL Functions PL/SQl functions defined for internal drop ship transactions. Compile the following PL/SQL functions: XYZ_SALE_INTERCO_FLAG_V1.txt XYZ_COGS_RECOG_INTERCO_FLAG_V1.txt XYZ_INTERNAL_DROP_RECEIPT_V1.txt XYZ_INTRNL_DROP_RCPT_MRKP_V1.txt XYZ_INTERCO_SEGMENT_V1.txt XYZ_INTRNL_DROP_COGS_AMT XYZ_INTRNL_DROP_INV_AMT

Also, be sure to run the grant and synonym scripts. Also, be sure to run the create index script for queries.

The actual code for these functions is listed at the end of the paper.

PL/SQL Functions File Ref: Document1 Company Confidential - For internal use only

8

Sub-Ledger Accounting Setups The SLA seeded definitions may need to be loaded using the concurrent program “Import Application Accounting Definition”. Context=default Source File=/pbtlyi/applmgr/1200/bom/12.0.0/patch/115/import/US/cstxlaaad.ldt Merge Analysis=No Import Option = Merge Validate=Yes To verify the load: 1. Event Model Form 2. Entity = “Material Account Events” 3. Event Class Code = “LOG_INTERCOMPANY” 4. Event Type Codes should be listed

Sub-Ledger Accounting Setups File Ref: Document1 Company Confidential - For internal use only

9

Custom Sources

Now create custom sources using the following form: Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Sources -> Custom Sources Description

Returns “Y” if transaction is internal drop ship

For internal drop ship returns the intercompany segment

Only called by COGS recognition. Returns “Y” if transaction is a COGS recognition on an internal drop ship

Internal drop COGS amt downstream after markup removed

Internal drop INV amt downstream after markup removed

Custom

Custom

PL/SQL

Data

Segment

Parameter

Parameter

Source

Source

Function

Type

Flexfield

Code

Name

Name

USED IN SALES ISSUE EVENT XYZ_SALE _INTERCO _FLAG XYZ INTERCO SEGMENT XYZ_COGS _RECOG_I NTERCO_F LAG XYZ INTRNL DROP COGS AMT XYZ INTRNL DROP INV AMT

XYZ_SALE _INTERCO _FLAG XYZ INTERCO SEGMENT XYZ_COGS _RECOG_I NTERCO_F LAG XYZ INTRNL DROP COGS AMT XYZ INTRNL DROP INV AMT

XYZ_SALE _INTERCO _FLAG XYZ_INTE RCO_SEG MENT XYZ_COGS _RECOG_I NTERCO_F LAG XYZ_INTR NL_DROP_ COGS_AM T

Alpha numer ic Alpha numer ic Alpha numer ic

No

Inventory Item in Costing

TRANSACTION_ID in Costing

Yes



No



Nume ric

No



XYZ_INTR NL_DROP_ INV_AMT

Nume ric

No



Intercompany Segment

Accounting

Custom Sources File Ref: Document1 Company Confidential - For internal use only

10

Description

Internal drop inventory receipt amount

Internal drop receipt markup

Custom

Custom

PL/SQL

Data

Segment

Accounting

Parameter

Parameter

Source

Source

Function

Type

Flexfield

Code

Name

Name

USED IN INTERCO MPANY EVENT XYZ_INTE RNAL_DR OP_RECEI PT XYZ_INTR NL_DROP_ RCPT_MR KP

XYZ_INTE RNAL_DR OP_RECEI PT XYZ_INTR NL_DROP_ RCPT_MR KP

XYZ_INTE RNAL_DR OP_RECEI PT XYZ_INTR NL_DROP_ RCPT_MR KP

Nume ric

No



Nume ric

No



Custom Sources File Ref: Document1 Company Confidential - For internal use only

11

Assign Custom Sources to Event Class Use this next form to assign the custom sources to the “Logical Intercompany” event class. Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Sources -> Accounting Attribute Assignments Query the event class “Logical Intercompany”. Create a new row for each entry in the table below. This will allow the source to be chosen while inside the line type form. Accounting

Source

Attribute Accounted Amount Entered Amount Accounted Amount

XYZ_INTERNAL_DROP_RECEIPT XYZ_INTRNL_DROP_RCPT_MRKP XYZ_INTRNL_DROP_RCPT_MRKP

Use this next form to assign the custom sources to the “Sales Order Issue” event class. Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Sources -> Accounting Attribute Assignments Query the event class “Sales Order Issue”. Create a new row for each entry in the table below. This will allow the source to be chosen while inside the line type form. Accounting

Source

Attribute Accounted Amount Accounted Amount

XYZ INTRNL DROP COGS AMT XYZ INTRNL DROP INV AMT

Assign Custom Sources to Event Class File Ref: Document1 Company Confidential - For internal use only

12

Assign Custom Sources to Event Class File Ref: Document1 Company Confidential - For internal use only

13

XLA Lookups Use this next form to create a new XLA Lookup. Application Developer -> Application -> Lookups -> XLA Lookups Query type of “XLA_ACCOUNTING_CLASS” This lookup will appear on the SLA accounting form for each IC markup accounting line.. Code

Meaning

Description

IC_Markup

Inter-company Markup

Inter-company Markup

XLA Lookups File Ref: Document1 Company Confidential - For internal use only

14

Accounting Definition Rules Use this next form to create an accounting derivation rule for markup account. Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Journal Entry Setups -> Account Derivation Rules Choose to create new rules as listed below: Description

Rule

Rule

Chart of

Chart of

Output

Segment

Code

Name

Accounts

Accounts

Type

Name

Transaction

Accounting XYZ_CORP _ACCOUN TING_FLE XFIELD XYZ_CORP _ACCOUN TING_FLE XFIELD

Segment

Segment

Inter-company Markup Account.

XYZ INTERCO MARKUP

XYZ INTERCO MARKUP

XYZ_CORP_ACCO UNTING_FLEXFIEL D

XYZ INTERCO SEGMENT used for intercompany eliminations.

XYZ INTERCO SEGMENT

XYZ INTERCO SEGMENT

XYZ_CORP_ACCO UNTING_FLEXFIEL D

Prior ity

Value

Value

Account

10

Constant

122500

Intercompany

10

Source

XYZ INTERCO SEGMENT

Condition

Type

Accounting Definition Rules File Ref: Document1 Company Confidential - For internal use only

15

Logical Intercompany Event Class Go back to the Application Accounting Definition form. Cost Manager – SLA -> SLA -> Accounting Setup > Accounting Methods Builder -> Methods and Definitions -> Application Accounting Definitions Query the event class of “Logical Intercompany”. Then, click on the “Journal Line Definition” button. Click the “Copy Definition” button. Definition Code = XYZ_LOGIC_INTERCO Definition Name = XYZ Logical Intercompany Description = XYZ Logical Intercompany Chart of Accounts Transaction = XYZ_CORP_ACCOUNTING_FLEXFIELD Chart of Accounts Accounting = XYZ_CORP_ACCOUNTING_FLEXFIELD After the new Journal Line Definition has been created, go back to the Application Accounting Definition form. Query the event class of “Logical Intercompany”. Delete the old journal line definition that is attached at the bottom of the form and add the new one that was just created.

Logical Intercompany Event Class File Ref: Document1 Company Confidential - For internal use only

16

Logical Intercompany Event Class – Journal Line Definitions Once you have defined the new Journal Line Definition, then execute the following steps: Select the journal line type of “Inventory Valuation”. Disable this type. Click the line type button. Click the “Copy” button. Create new line types as described above by using the data below. Repeat once for each row in the table below:

Description

Line Event Class

Name

Type Code

Transaction

Accounting

Rounding

Chart of

Class

Class

Condition

Accounting Attribute

Accounts

Assignments: Source for Accounted Amount

Sales Issue at std cost.

Logical Intercompany

XYZ INTRNL DROP ISSUE

XYZ INTRNL DROP ISSUE

XYZ_CORP_A CCOUNTING_ FLEXFIELD

Inventory Valuation

Inventory Valuation

Accounting Line Type =1 AND Accounting Event Type Code = 'LOG_IC_SALES_ISSUE'

Accounted Amount = Accounted Amount


XYZ_INTRNL_DROP _MRKP

XYZ_INTRNL_ DROP_MRKP


Inventory Valuation

Inventory Valuation

Accounting Line Type =1 AND Accounting Event Type Name = LOG_IC_SHIPMENT_RC PT

Accounted Amount = XYZ_INTR NL_DROP_ RCPT_MRK P

( WHEN YOU COPY THE LINE TYPE, MAKE SURE IT DOES NOT CHANGE THE EVENT CLASS. ) Inv receipt markup – executes with the XYZ INTRNL

DROP RCPT INV

Logical Intercompany Event Class – Journal Line Definitions File Ref: Document1 Company Confidential - For internal use only

17

Description

Line Event Class

Name

Type Code

Transaction

Accounting

Rounding

Chart of

Class

Class

Condition

Accounting Attribute

Accounts

Assignments: Source for Accounted Amount

Be sure to enter ADR of XYZ INTERCO SEGMENT for the above type on the intercompany segment And XYZ INTERCO MARKUP on the account segment Inv receipt without markup This includes the PPV amount Executes with XYZ_INTRNL_DROP_MRKP


XYZ INTRNL DROP RCPT INV

XYZ INTRNL DROP RCPT INV


Inventory Valuation

Inventory Valuation

Accounting Line Type =1 AND Accounting Event Type Name = LOG_IC_SHIPMENT_RC PT

Accounted Amount = XYZ INTERNAL DROP RECEIPT

On new line types, always turn on debit and switch debit/credit. This means positive will be debit and negative will be credit. In the Logical Intercompany event class, on the journal line types of XYZ_INTERCO_MARKUP and Intercompany Accrual enter the following: 1.

Segment = Intercompany, Rule Name = XYZ INTERCO SEGMENT

Logical Intercompany Event Class – Journal Line Definitions File Ref: Document1 Company Confidential - For internal use only

18

Sales Order Issue Event Class Go back to the Application Accounting Definitions form. Query the event class of “Sales Order Issue”. Then, click on the “Journal Line Definition” button. Click the “Copy Definition” button. Definition Code = XYZ SALES ORDER ISSUE Definition Name = XYZ Sales Order Issue Description = XYZ Sales Order Issue Chart of Accounts Transaction = XYZ_CORP_ACCOUNTING_FLEXFIELD Chart of Accounts Accounting = XYZ_CORP_ACCOUNTING_FLEXFIELD After the new Journal Line Definition has been created, go back to the Application Accounting Definition form. Query the event class of “Sales Order Issue”. Delete the old journal line definition that is attached at the bottom of the form and add the new one that was just created.

Sales Order Issue Event Class File Ref: Document1 Company Confidential - For internal use only

19

Sales Order Issue – Deferred COGS Journal Line Types Since third party sales and logical sales issue share the same journal line types, if you modify one, you must redefine them all.

Select the journal line type of “Deferred COGS”. Disable this type. Click the line type button. Click the “Copy” button. Create new line types as described above by using the data below. Repeat once for each row in the table below:

Description

Line Event Class

Type

Name

Code

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount THIRD PARTY AND EXTERNAL DROP COGS RECOG FOR DEFERRED

Sales Order Issue

XYZ COGS RECOG 3RD DEFERRED

XYZ COGS RECOG 3RD DEFERRED


Deferred Cost of Goods Sold


Debit

Accounting Line Type =36 AND Accounting Event Type Name = 'COGS Recognition' AND XYZ COGS RECOG INTERCO FLAG = 'N'


Sales Order Issue – Deferred COGS Journal Line Types File Ref: Document1 Company Confidential - For internal use only

20

Description

Line Event Class

Type

Name

Code

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount CUSTOMER FACING INTERNAL DROP COGS RECOG FOR DEFERRED COGS

Sales Order Issue

XYZ COGS RECOG DEFERRED

XYZ COGS RECOG DEFERRED




XYZ COGS RMA DEFERRED

Sales Order Issue






Debit

THIRD PARTY SHIP AND EXTERNAL DROP SHIP DCOGS

Sales Order Issue

XYZ DEFERRED COGS

XYZ DEFERRED COGS




Debit

customer facing logical sales issue deferred COGS

Sales Order Issue

XYZ LOGICAL DEFERRED COGS

XYZ LOGICAL DEFERRED COGS




CREDIT – THIS IS BECAUSE THE CUSTOM SOURCE ALWAYS RETURNS A POSITIVE AMOUNT AND IS ALSO USED FOR THE COGS ENTRY, BUT THIS ENTRY NEEDS TO BE CREDITED

DEBIT

Accounting Line Type =36 AND Accounting Event Type Name = 'COGS Recognition' AND XYZ COGS RECOG INTERCO FLAG = 'Y'

XYZ INTRNL DROP COGS AMT

Accounting Line Type =36 AND Accounting Event Type Name = 'RMA Receipt' AND XYZ COGS RECOG INTERCO FLAG = 'N' Accounting Line Type =36 AND Accounting Event Type Name = 'Sales Order Issue' AND XYZ_SALE_INTERCO _FLAG = 'N'


Accounting Line Type =36 AND Accounting Event Type Name = 'Logical Sales Order Issue' AND XYZ SALE INTERCO FLAG = 'Y'

Accounted Amount = XYZ_INTRNL_D ROP_INV_AMT

Accounted Amount


21

Description

Line Event Class

Type Code

Name

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount

On new line types, always turn on debit and switch debit/credit. This means positive will be debit and negative will be credit.


22

Sales Order Issue – COGS Journal Line Types Select the journal line type of “Cost of Goods Sold”. Disable this type. Click the line type button. Click the “Copy” button. Create new line types as described above by using the data below. Repeat once for each row in the table below:

Description

Line Event Class

Type

Name

Code

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount CUSTOMER FACING INTERNAL DROP COGS RECOG FOR DEFERRED COGS

Sales Order Issue

XYZ COGS RECOG INTERCO

XYZ COGS RECOG INTERC O

XYZ_CORP_AC COUNTING_FL EXFIELD

Cost of Goods Sold

Cost of Goods Sold

Debit

Accounting Line Type =35 AND Accounting Event Type Name = 'COGS Recognition' AND XYZ COGS RECOG INTERCO FLAG = 'Y'

XYZ INTRNL DROP COGS AMT

THIRD PARTY AND EXTERNAL DROP COG RECOG COGS AMT

Sales Order Issue

XYZ COGS RECOG SO

XYZ COGS RECOG SO


Cost of Goods Sold

Cost of Goods Sold

Debit

Accounting Line Type =35 AND Accounting Event Type Name = 'COGS Recognition' AND XYZ COGS RECOG INTERCO FLAG = 'N'


Sales Order Issue – COGS Journal Line Types File Ref: Document1 Company Confidential - For internal use only

23

Description

Line Event Class

Type

Name

Code

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount

XYZ COGS RMA

Sales Order Issue

XYZ COGS RMA

XYZ COGS RMA


Cost of Goods Sold

Cost of Goods Sold

Debit

Accounting Line Type =35 AND Accounting Event Type Name = 'RMA Receipt' AND XYZ COGS RECOG INTERCO FLAG = 'N'



Sales Order Issue – COGS Journal Line Types File Ref: Document1 Company Confidential - For internal use only

24

Sales Order Issue – Inventory Valuation Journal Line Types Once you have defined the new Journal Line Definition, then execute the following steps: Select the journal line type of “Inventory Valuation”. Disable this type. Click the line type button. Click the “Copy” button. Create new line types as described above by using the data below. Repeat once for each row in the table below:

Description

Line Event Class

Type

Name

Code

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount internal drop ship customer facing logical sales issue inv valuation

RMA

Sales Order Issue

XYZ INT DROP INV VALUATI ON

XYZ INT DROP INV VALUATI ON


Inventory Valuation

Inventory Valuation

CREDIT – BECAUSE THE CUSTOM SOURCE RETURNS A POSITIVE AND IS ALSO USED FOR THE DEFERRED COGS SIDE OF THE LOGICAL SALES ISSUE BUT HERE WE NEED A CREDIT SINCE SHIPPING OUT INVENTORY

Accounting Line Type =1 AND Accounting Event Type Name = 'Logical Sales Order Issue' AND XYZ SALE INTERCO FLAG = 'Y'

Accounted Amount = XYZ_INTR NL_DROP_I NV_AMT

Sales Order Issue

XYZ RMA INV VALUATI ON

XYZ RMA INV VALUATI ON


Inventory Valuation

Inventory Valuation

Debit

Accounting Line Type =1 AND Accounting Event Type Name = 'RMA Receipt' AND XYZ SALE INTERCO FLAG = 'N'


Sales Order Issue – Inventory Valuation Journal Line Types File Ref: Document1 Company Confidential - For internal use only

25

Description

Line Event Class

Type

Name

Code

Transaction

Accounting

Rounding

Debit

Chart of

Class

Class

/

Attribute

Credit

Assignments:

Accounts

Condition

Accounting

Source for Accounted Amount 3rd party and external drop so inv valuation

Sales Order Issue

XYZ SO INV VALUATI ON

XYZ SO INV VALUATI ON


Inventory Valuation

Inventory Valuation

Debit

Accounting Line Type =1 AND Accounting Event Type Name = 'Sales Order Issue' AND XYZ SALE INTERCO FLAG = 'N'



Sales Order Issue – Inventory Valuation Journal Line Types File Ref: Document1 Company Confidential - For internal use only

26

PL/SQL Function - XYZ_SALE_INTERCO_FLAG CREATE OR REPLACE FUNCTION XYZ_SALE_INTERCO_FLAG ( p_Inventory_Item IN NUMBER , p_transaction_id IN NUMBER ) RETURN VARCHAR2 IS -- **** RETURN Y ON SALES ISSUE TRANSACTIONS IF IT IS INTERCOMPANY xyz_sale_interco_flag VARCHAR2(1); BEGIN xyz_sale_interco_flag:='N'; SELECT /*+ ORDERED */ 'Y' INTO xyz_sale_interco_flag FROM mtl_material_transactions mmt , mtl_material_transactions mmt2 , mtl_transaction_types ttype2 WHERE /*+ ORDERED_PREDICATES */ mmt.transaction_id = p_transaction_id -- **** FROM THE PARENT TRANSACTION OF SALES ISSUE AND mmt2.parent_transaction_id = mmt.parent_transaction_id AND ttype2.transaction_type_id = mmt2.transaction_type_id AND -- **** THIS INSURES WE ARE DEALING WITH ONE OF THE LOGICAL INTERCOMPANY TRANSACTIONS ttype2.transaction_type_name = 'Logical Intercompany Shipment Receipt' ; PL/SQL Function - XYZ_SALE_INTERCO_FLAG File Ref: Document1 Company Confidential - For internal use only

27

-- ************************************ FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' SALES ISSUE INTERCO FLAG FUNCTION ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' SALES ISSUE INTERCO FLAG: ' || xyz_sale_interco_flag ); -- ************************************ RETURN xyz_sale_interco_flag; -- ************************************ EXCEPTION WHEN NO_DATA_FOUND THEN xyz_sale_interco_flag:='N'; FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' SALES ISSUE INTERCO FLAG FUNCTION - NO DATA' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' SALES ISSUE INTERCO FLAG: ' || xyz_sale_interco_flag ); PL/SQL Function - XYZ_SALE_INTERCO_FLAG File Ref: Document1 Company Confidential - For internal use only

28

RETURN xyz_sale_interco_flag;

WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' SALES ISSUE INTERCO FLAG FUNCTION ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR ITEM ID: ' || p_inventory_item );

xyz_sale_interco_flag:='N'; RETURN xyz_sale_interco_flag; END;

PL/SQL Function - XYZ_SALE_INTERCO_FLAG File Ref: Document1 Company Confidential - For internal use only

29

PL/SQL Function - XYZ_COGS_RECOG_INTERCO_FLAG CREATE OR REPLACE FUNCTION XYZ_COGS_RECOG_INTERCO_FLAG ( p_Inventory_Item IN NUMBER , p_transaction_id IN NUMBER ) RETURN VARCHAR2 IS -- **** RETURNS Y IF COGS RECOG IS ON INTERCOMPANY xyz_cogs_recog_interco_flag VARCHAR2(1); BEGIN xyz_cogs_recog_interco_flag:='N'; SELECT /*+ ORDERED */ 'Y' INTO xyz_cogs_recog_interco_flag FROM mtl_material_transactions mmt WHERE /*+ ORDERED_PREDICATES */ mmt.transaction_id = p_transaction_id -- **** COGS RECOG TRANSACTION AND -- **** THIS WILL PULL IN THE SOURCE TRANS OF COGS RECOG EXISTS ( SELECT /*+ ORDERED */ mmt2.transaction_id FROM mtl_material_transactions mmt2 PL/SQL Function - XYZ_COGS_RECOG_INTERCO_FLAG File Ref: Document1 Company Confidential - For internal use only

30

, inv.mtl_transaction_types ttype WHERE /*+ ORDERED_PREDICATES */ mmt2.transaction_source_id = mmt.transaction_source_id AND -- **** THERE COULD BE MULTIPLE ITEMS WITH DIFFERENT SOURCES mmt2.inventory_item_id = mmt.inventory_item_id AND ttype.transaction_type_id = mmt2.transaction_type_id AND -- **** THIS INSURES WE ARE DEALING WITH AN INTERNAL DROP SHIP ttype.transaction_type_name = 'Logical Intercompany Shipment Receipt' ) ; -- ************************************ FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' COGS RECOG INTERCO FLAG FUNCTION ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' COGS RECOG INTERCO FLAG: ' || xyz_cogs_recog_interco_flag ); -- ************************************ RETURN xyz_cogs_recog_interco_flag; -- ************************************ EXCEPTION WHEN NO_DATA_FOUND THEN xyz_cogs_recog_interco_flag:='N'; PL/SQL Function - XYZ_COGS_RECOG_INTERCO_FLAG File Ref: Document1 Company Confidential - For internal use only

31

FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' COGS RECOG INTERCO FLAG FUNCTION - NO DATA' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' COGS RECOG INTERCO FLAG: ' || xyz_cogs_recog_interco_flag ); RETURN xyz_cogs_recog_interco_flag;

WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' COGS RECOG INTERCO FLAG FUNCTION ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR ITEM ID: ' || p_inventory_item );

xyz_cogs_recog_interco_flag:='N'; RETURN xyz_cogs_recog_interco_flag; END; PL/SQL Function - XYZ_COGS_RECOG_INTERCO_FLAG File Ref: Document1 Company Confidential - For internal use only

32

PL/SQL Function - XYZ_INTERNAL_DROP_RECEIPT CREATE OR REPLACE FUNCTION XYZ_INTERNAL_DROP_RECEIPT -- **** INTERNAL DROP SHIP LOGICAL RECEIPT ( p_inventory_Item IN NUMBER , p_transaction_id IN NUMBER ) RETURN NUMBER IS xyz_no_markup_inv_cost NUMBER; BEGIN xyz_no_markup_inv_cost := 0; SELECT /*+ ORDERED */ ROUND ( ( ( mmt3.intercompany_cost ( mmt3.intercompany_cost - ( rates.conversion_rate * mmt1.actual_cost ) ) ) * mmt3.transaction_quantity ) ,2 ) extend_inv INTO xyz_no_markup_inv_cost FROM mtl_material_transactions mmt , mtl_material_transactions mmt1 , mtl_transaction_types ttype1 , mtl_material_transactions mmt2 , mtl_transaction_types ttype2 , mtl_material_transactions mmt3 PL/SQL Function - XYZ_INTERNAL_DROP_RECEIPT File Ref: Document1 Company Confidential - For internal use only

33

, mtl_transaction_types ttype3 , gl_daily_rates rates WHERE /*+ ORDERED_PREDICATES */ mmt.transaction_id = p_transaction_id -- ********************************************* AND mmt1.parent_transaction_id = mmt.parent_transaction_id AND ttype1.transaction_type_id = mmt1.transaction_type_id AND ttype1.transaction_type_name = 'Sales order issue' -- ********************************************* AND mmt2.parent_transaction_id = mmt.parent_transaction_id AND ttype2.transaction_type_id = mmt2.transaction_type_id AND ttype2.transaction_type_name = 'Logical Intercompany Sales Issue' -- *************************************************** AND mmt3.parent_transaction_id = mmt.parent_transaction_id AND ttype3.transaction_type_id = mmt3.transaction_type_id AND ttype3.transaction_type_name = 'Logical Intercompany Shipment Receipt' -- ********************************************* -- **** WE NEED TO CONVERT THE SHIP AMT TO THE RECEIPT CURRENCY AND rates.from_currency = mmt2.currency_code AND rates.to_currency = mmt3.currency_code AND rates.conversion_date = TRUNC (mmt.transaction_date) AND --rates.conversion_type = '1001' -- **** get from profile??? rates.conversion_type = 'Corporate' -- **** get from profile??? ; -- ************************************ PL/SQL Function - XYZ_INTERNAL_DROP_RECEIPT File Ref: Document1 Company Confidential - For internal use only

34

FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTERNAL_DROP_RECEIPT ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' xyz_no_markup_inv_cost: ' || xyz_no_markup_inv_cost ); -- ************************************ RETURN xyz_no_markup_inv_cost; -- ************************************ EXCEPTION WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTERNAL_DROP_RECEIPT ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR ITEM ID: ' || p_inventory_item );

xyz_no_markup_inv_cost := 0; RETURN xyz_no_markup_inv_cost; PL/SQL Function - XYZ_INTERNAL_DROP_RECEIPT File Ref: Document1 Company Confidential - For internal use only

35

END;

PL/SQL Function - XYZ_INTERNAL_DROP_RECEIPT File Ref: Document1 Company Confidential - For internal use only

36

PL/SQL Function - XYZ_INTRNL_DROP_RCPT_MRKP CREATE OR REPLACE FUNCTION XYZ_INTRNL_DROP_RCPT_MRKP -- **** INTERNAL DROP SHIP LOGICAL RECEIPT MARKUP ( p_inventory_Item IN NUMBER , p_transaction_id IN NUMBER ) RETURN NUMBER IS xyz_markup_inv_cost NUMBER; BEGIN xyz_markup_inv_cost := 0; SELECT /*+ ORDERED */ ROUND ( ( mmt2.actual_cost * mmt2.transaction_quantity ) ,2 ) ---- **** MUST ROUND SEPERATELY FROM SUBTRACT SO ENTRIES BALANCE WITHOUT ROUNDING ERROR ROUND ( ( ( mmt3.actual_cost * ( mmt2.actual_cost / mmt2.intercompany_cost ) ) * mmt2.transaction_quantity ) ,2 ) markup INTO xyz_markup_inv_cost FROM mtl_material_transactions mmt PL/SQL Function - XYZ_INTRNL_DROP_RCPT_MRKP File Ref: Document1 Company Confidential - For internal use only

37

, mtl_material_transactions mmt2 , inv.mtl_transaction_types ttype , mtl_material_transactions mmt3 , inv.mtl_transaction_types ttype2 WHERE /*+ ORDERED_PREDICATES */ mmt.transaction_id = p_transaction_id AND mmt2.parent_transaction_id = mmt.parent_transaction_id AND ttype.transaction_type_id(+) = mmt2.transaction_type_id AND ttype.transaction_type_name = 'Logical Intercompany Shipment Receipt' AND mmt3.parent_transaction_id = mmt.parent_transaction_id AND ttype2.transaction_type_id(+) = mmt3.transaction_type_id AND ttype2.transaction_type_name = 'Sales order issue' ; -- ************************************ FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTRNL_DROP_RCPT MARKUP ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' xyz_markup_inv_cost: ' || xyz_markup_inv_cost ); -- ************************************ RETURN xyz_markup_inv_cost; -- ************************************ PL/SQL Function - XYZ_INTRNL_DROP_RCPT_MRKP File Ref: Document1 Company Confidential - For internal use only

38

EXCEPTION WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTRNL_DROP_RCPT MARKUP ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR ITEM ID: ' || p_inventory_item );

xyz_markup_inv_cost := 0; RETURN xyz_markup_inv_cost; END;

PL/SQL Function - XYZ_INTRNL_DROP_RCPT_MRKP File Ref: Document1 Company Confidential - For internal use only

39

PL/SQL Function - XYZ_INTERCO_SEGMENT CREATE OR REPLACE FUNCTION XYZ_INTERCO_SEGMENT ( p_inventory_Item IN NUMBER , p_transaction_id IN NUMBER ) RETURN VARCHAR2 IS xyz_interco_segment

VARCHAR2(3);

BEGIN xyz_interco_segment:='000'; SELECT /*+ ORDERED */ comb.segment1 INTO xyz_interco_segment FROM mtl_material_transactions mmt , mtl_transaction_types ttype , mtl_material_transactions mmt2 , mtl_transaction_types ttype2 , gl_code_combinations comb WHERE /*+ ORDERED_PREDICATES */ -- **** INTERNAL DROP SHIP INTERCOMPANY TRANS mmt.transaction_id = p_transaction_id AND ttype.transaction_type_id = mmt.transaction_type_id AND ttype.transaction_type_name = 'Logical Intercompany Shipment Receipt' AND PL/SQL Function - XYZ_INTERCO_SEGMENT File Ref: Document1 Company Confidential - For internal use only

40

mmt2.parent_transaction_id = mmt.parent_transaction_id AND ttype2.transaction_type_id = mmt2.transaction_type_id AND -- **** ttype2.transaction_type_name = 'Logical Intercompany Sales Issue' AND comb.code_combination_id = mmt2.distribution_account_id; -- ************************************ FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' INTERCO SEGMENT FUNCTION ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_Item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' INTERCO SEGMENT: ' || xyz_interco_segment ); -- ************************************ RETURN xyz_interco_segment; -- ************************************ EXCEPTION WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' INTERCO SEGMENT FUNCTION ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, PL/SQL Function - XYZ_INTERCO_SEGMENT File Ref: Document1 Company Confidential - For internal use only

41

' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR ITEM ID: ' || p_inventory_Item );

xyz_interco_segment:='000'; RETURN xyz_interco_segment; END;

PL/SQL Function - XYZ_INTERCO_SEGMENT File Ref: Document1 Company Confidential - For internal use only

42

PL/SQL Function - XYZ_INTRNL_DROP_COGS_AMT CREATE OR REPLACE FUNCTION XYZ_INTRNL_DROP_COGS_AMT -- **** INV RCPT NO MARKUP ON INTERNAL DROP SHIP ( p_inventory_Item IN NUMBER , p_transaction_id IN NUMBER ) RETURN NUMBER IS xyz_no_markup_inv_cost NUMBER; BEGIN -- **** THIS IS USED BY INTERNAL DROP SHIPS TO ADJUST THE -- **** CUSTOMER FACING SIDE OF THE TRANSACTION xyz_no_markup_inv_cost := 0; SELECT /*+ ORDERED */ ROUND ( ( ( mmt3.intercompany_cost ( mmt3.intercompany_cost - ( rates.conversion_rate * mmt1.actual_cost ) ) ) * mmt3.transaction_quantity ) ,2 ) extend_inv INTO xyz_no_markup_inv_cost FROM mtl_material_transactions mmt , mtl_material_transactions mmt1 PL/SQL Function - XYZ_INTRNL_DROP_COGS_AMT File Ref: Document1 Company Confidential - For internal use only

43

, mtl_transaction_types ttype1 , mtl_material_transactions mmt2 , mtl_transaction_types ttype2 , mtl_material_transactions mmt3 , mtl_transaction_types ttype3 , gl_daily_rates rates WHERE /*+ ORDERED_PREDICATES */ mmt.transaction_id = p_transaction_id -- **** COGS RECOG AND -- **** THIS WILL PULL IN THE SOURCE TRANS OF COGS RECOG mmt1.transaction_source_id = mmt.transaction_source_id AND -- **** THERE COULD BE MULTIPLE ITEMS PER ORDER mmt1.inventory_item_id = mmt.inventory_item_id AND ttype1.transaction_type_id = mmt1.transaction_type_id AND ttype1.transaction_type_name = 'Sales order issue' -- ********************************************* AND mmt2.parent_transaction_id = mmt1.parent_transaction_id AND ttype2.transaction_type_id = mmt2.transaction_type_id AND ttype2.transaction_type_name = 'Logical Intercompany Sales Issue' -- *************************************************** AND mmt3.parent_transaction_id = mmt1.parent_transaction_id AND ttype3.transaction_type_id = mmt3.transaction_type_id AND ttype3.transaction_type_name = 'Logical Intercompany Shipment Receipt' -- ********************************************* -- **** WE NEED TO CONVERT THE SHIP AMT TO THE RECEIPT CURRENCY AND rates.from_currency = mmt2.currency_code AND rates.to_currency = mmt3.currency_code AND PL/SQL Function - XYZ_INTRNL_DROP_COGS_AMT File Ref: Document1 Company Confidential - For internal use only

44

rates.conversion_date = TRUNC (mmt.transaction_date) AND --rates.conversion_type = '1001' -- **** get from profile??? rates.conversion_type = 'Corporate' -- **** get from profile??? ; -- ************************************ FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTRNL_DROP_COGS_AMT ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' xyz_no_markup_inv_cost: ' || xyz_no_markup_inv_cost ); -- ************************************ RETURN xyz_no_markup_inv_cost; -- ************************************ EXCEPTION WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTRNL_DROP_COGS_AMT ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, PL/SQL Function - XYZ_INTRNL_DROP_COGS_AMT File Ref: Document1 Company Confidential - For internal use only

45

' ERROR ITEM ID: ' || p_inventory_item );

xyz_no_markup_inv_cost := 0; RETURN xyz_no_markup_inv_cost; END;

PL/SQL Function - XYZ_INTRNL_DROP_COGS_AMT File Ref: Document1 Company Confidential - For internal use only

46

PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT

CREATE OR REPLACE FUNCTION XYZ_INTRNL_DROP_INV_AMT -- **** INV RCPT NO MARKUP ON INTERNAL DROP SHIP ( p_inventory_Item , p_transaction_id

IN NUMBER IN NUMBER

) RETURN NUMBER IS xyz_no_markup_inv_cost NUMBER; BEGIN -- **** THIS IS USED BY INTERNAL DROP SHIPS TO ADJUST THE -- **** CUSTOMER FACING SIDE OF THE TRANSACTION xyz_no_markup_inv_cost := 0; SELECT /*+ ORDERED */ ROUND ( ( ( mmt3.intercompany_cost PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT File Ref: Document1 Company Confidential - For internal use only

47

( mmt3.intercompany_cost - ( rates.conversion_rate * mmt1.actual_cost ) ) ) * mmt3.transaction_quantity ) ,2 )

extend_inv

INTO xyz_no_markup_inv_cost FROM mtl_material_transactions

mmt

, mtl_material_transactions

mmt1

, mtl_transaction_types

ttype1

, mtl_material_transactions , mtl_transaction_types

mmt2 ttype2

, mtl_material_transactions , mtl_transaction_types , gl_daily_rates

mmt3 ttype3

rates

WHERE /*+ ORDERED_PREDICATES */ mmt.transaction_id = p_transaction_id -- ********************************************* AND mmt1.parent_transaction_id = mmt.parent_transaction_id AND ttype1.transaction_type_id = mmt1.transaction_type_id AND PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT File Ref: Document1 Company Confidential - For internal use only

48

ttype1.transaction_type_name = 'Sales order issue' -- ********************************************* AND mmt2.parent_transaction_id = mmt.parent_transaction_id AND ttype2.transaction_type_id = mmt2.transaction_type_id AND ttype2.transaction_type_name = 'Logical Intercompany Sales Issue' -- *************************************************** AND mmt3.parent_transaction_id = mmt.parent_transaction_id AND ttype3.transaction_type_id = mmt3.transaction_type_id AND ttype3.transaction_type_name = 'Logical Intercompany Shipment Receipt' -- ********************************************* -- **** WE NEED TO CONVERT THE SHIP AMT TO THE RECEIPT CURRENCY AND rates.from_currency = mmt2.currency_code AND rates.to_currency = mmt3.currency_code AND rates.conversion_date = TRUNC (mmt.transaction_date) AND --rates.conversion_type = '1001' -- **** get from profile??? rates.conversion_type = 'Corporate' -- **** get from profile??? ; PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT File Ref: Document1 Company Confidential - For internal use only

49

-- ************************************ FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTRNL_DROP_INV_AMT ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ITEM ID: ' || p_inventory_item ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' xyz_no_markup_inv_cost: ' || xyz_no_markup_inv_cost );

-- ************************************ RETURN xyz_no_markup_inv_cost; -- ************************************ EXCEPTION WHEN OTHERS THEN FND_FILE.NEW_LINE( FND_FILE.LOG, 1 ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR: ' || SUBSTR(SQLERRM,1,80) PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT File Ref: Document1 Company Confidential - For internal use only

50

); FND_FILE.PUT_LINE( FND_FILE.LOG, ' XYZ_INTRNL_DROP_INV_AMT ' ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR TRANSACTION ID: ' || p_transaction_id ); FND_FILE.PUT_LINE( FND_FILE.LOG, ' ERROR ITEM ID: ' || p_inventory_item );

xyz_no_markup_inv_cost := 0; RETURN xyz_no_markup_inv_cost;

END;

PL/SQL Function - XYZ_INTRNL_DROP_INV_AMT File Ref: Document1 Company Confidential - For internal use only

51

White Paper Subledger Accounting for Intercompany Transaction Flows V5.6

Recommend Documents