Customize PO Approval workflow to add NonRecoverable Tax in the Approver Notification Message

Requirement: 

User Enter the PO All Details and submit for Approval.

Actions --> Manage --> can view the NonRecoverable Tax, Recoverable Tax.

PO Approval workflow to add NonRecoverable Tax in the Approver Notification Message

1. New Attribute 

XXRECOVERABLE_TAX

2. Open Process

3. Under Message add your new attribute value

Recoverable Tax : &XXRECOVERABLE_TAX

4. New Function (XX_PONOTIF_TO_SUPPLIER)

Function Name; XX_PONOTIF_TO_SUPPLIER 🡪 this is the procedure in the database available at the end.

5. Attach this function to the process.

/* Formatted on 10/27/2022 2:18:31 PM (QP5 v5.336) */

CREATE OR REPLACE PROCEDURE XX_PONOTIF_TO_SUPPLIER (

    p_item_type   IN            VARCHAR2,

    p_item_key    IN            VARCHAR2,

    p_actid       IN            NUMBER,

    p_funmode     IN            VARCHAR2,

    p_result         OUT NOCOPY VARCHAR2)

IS

    L_RECOVER_TAX        NUMBER;

    L_RECOVER_TAX_CHAR   VARCHAR2 (200);

BEGIN

    BEGIN

        SELECT SUM (POD.RECOVERABLE_TAX)

          INTO L_RECOVER_TAX

          /*nvl(sum( round (nvl(POD.RECOVERABLE_TAX,0) *

                                    decode(quantity_ordered,

                                           NULL,

                                           (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / nvl(POD.amount_ordered, 1),

                                           (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / nvl(POD.quantity_ordered, 1)

                                          ) / 5

                                    ) * 5

                           ),0)*/

          FROM po_distributions_all POD, PO_HEADERS_ALL POA, apps.wf_items wi

         WHERE     POD.po_header_id = POA.PO_HEADER_ID --POHdr_rec.po_header_id

               AND NVL (POD.distribution_type, 'STANDARD') NOT IN

                       ('PREPAYMENT')                              -- 11876122

               AND POD.line_location_id IS NOT NULL

               AND wi.item_type = 'POAPPRV'

               AND wi.item_key = p_item_key

               AND wi.user_key = POA.segment1;

        L_RECOVER_TAX_CHAR := TO_CHAR (NVL (L_RECOVER_TAX, 0));

        wf_engine.setitemattrtext (itemtype   => p_item_type,

                                   itemkey    => p_item_key,

                                   aname      => 'XXRECOVERABLE_TAX',

                                   avalue     => L_RECOVER_TAX_CHAR);

    EXCEPTION

        WHEN OTHERS

        THEN

            AP_WEB_UTILITIES_PKG.logProcedure ('ERROR IN XXRECOVERABLE_TAX',

                                               'sqlerrm' || SQLERRM);

            wf_engine.setitemattrtext (itemtype   => p_item_type,

                                       itemkey    => p_item_key,

                                       aname      => 'XXRECOVERABLE_TAX',

                                       avalue     => '0');

    END;

    p_result := 'Y';

EXCEPTION

    WHEN OTHERS

    THEN

        p_result := 'N';

END;

Output:

Submit PO for Approval and check the Approver Notification. 

Item Creation with catalog, templates apps R12

--Stock Items

CREATE TABLE XXFIA_ITEM_INTERFACE

(ITEM_CODE VARCHAR2(30),

DESCRIPTION VARCHAR2(240),

SEGMENT1  VARCHAR2(240),

SEGMENT2  VARCHAR2(240),

CATEGORY_ID NUMBER,

UOM  VARCHAR2(240),

EXPENSE_ACCOUNT_SEG  VARCHAR2(240),

EXPENSE_ACCOUNT NUMBER,

SOURCE_NUMBER  VARCHAR2(240),

PROCESS_FLAG VARCHAR2(2),

ERROR_MSG  VARCHAR2(240));

--Non Stock Items

CREATE TABLE XXFIA_ITEM_INTERFACE_NS

(ITEM_CODE VARCHAR2(30),

DESCRIPTION VARCHAR2(240),

SEGMENT1  VARCHAR2(240),

SEGMENT2  VARCHAR2(240),

CATEGORY_ID NUMBER,

UOM  VARCHAR2(240),

EXPENSE_ACCOUNT_SEG  VARCHAR2(240),

EXPENSE_ACCOUNT NUMBER,

SOURCE_NUMBER  VARCHAR2(240),

PROCESS_FLAG VARCHAR2(2),

ERROR_MSG  VARCHAR2(240))

;

1. Upload using toad or any other tool.

2. Check if all the UOM exists in the masters.

select distinct trim(UOM),1

 from XXFIA_ITEM_INTERFACE

 WHERE TRIM(UOM)NOT IN (SELECT uom_code

FROM 

MTL_UNITS_OF_MEASURE MTL) 

UNION

select distinct trim(UOM),2

 from XXFIA_ITEM_INTERFACE_NS

 WHERE TRIM(UOM) NOT IN (SELECT trim(uom_code)

FROM 

MTL_UNITS_OF_MEASURE MTL)

ORDER BY 1

 ;

--truncate table XXFIA_ITEM_INTERFACE;

3. get the category_id

select category_id,segment1,SEGMENT2, description,category_concat_segs, structure_name

from mtl_categories_v

--where segment1 = '%ELEC%'

order by category_id,structure_id;

SELECT * FROM XXFIA_ITEM_INTERFACE

WHERE CATEGORY_ID IS NULL

;  

4. update category_id

DECLARE

CURSOR C1 IS 

SELECT CATEGORY_ID,SEGMENT1, SEGMENT2 FROM mtl_categories_v;

BEGIN

FOR I IN C1 LOOP

UPDATE XXFIA_ITEM_INTERFACE 

SET CATEGORY_ID = I.CATEGORY_ID

WHERE UPPER(I.SEGMENT1) = TRIM(UPPER(SEGMENT1)) 

AND UPPER(I.SEGMENT2) = TRIM(UPPER(SEGMENT2))

AND CATEGORY_ID IS NULL

;

COMMIT;

END LOOP;

END;

SELECT DISTINCT TRIM(SEGMENT1), TRIM(SEGMENT2) FROM XXFIA_ITEM_INTERFACE

WHERE CATEGORY_ID IS NULL

5. get the Master Organization, Other Oranizations.

--2398 MASTER

--2352 CHILD

6. Check if the accounts combination exists in GL  or not.

SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,EXPENSE_ACCOUNT_SEG

,3083 --TEMPLATE_ID 

--,3391161

, fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT

FROM XXFIA_ITEM_INTERFACE 

where

fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000') = '0';

SELECT * FROM GL_CODE_COMBINATIONS_V

WHERE CODE_COMBINATION_ID = 3391161;

7. Get the template Id and insert into the interface tables.

--STOCK TEMPLATE_ID  3083

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

,EXPENSE_ACCOUNT

)

SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3083 --TEMPLATE_ID 

--,3391161

, fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT

FROM XXFIA_ITEM_INTERFACE 

WHERE ITEM_CODE = ('67000450');

8. Run the concurrent Program from Inventory Resp

Import Items with Organization All, and Proces Flag as 1 and other as yes.

9.

For Categories.

insert into MTL_ITEM_CATEGORIES_INTERFACE

(

INVENTORY_ITEM_ID,

item_number,

category_set_id,  -- SAME for all --select category_set_id,category_set_name,description,structure_name, structure_id from mtl_category_sets_v where category_set_name LIKE 'FIA%';

category_id, --updated using above script

process_flag, organization_id, set_process_id, transaction_type)

--values('TESTCATG2',1100000041,2127,1,2398,1,'CREATE');

SELECT 

(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = TRIM(ITEM_CODE) and organization_id =2398 ),

TRIM(ITEM_CODE)

,1100000041 --static get once for all

,category_id

,1

,2398 --MASTER_ORG_ID

,1,'CREATE' FROM XXFIA_ITEM_INTERFACE 

WHERE TRIM(ITEM_CODE) = ('67000450'); 

10. Again Run the concurrent Program from Inventory Resp

Import Items with Organization All, and Proces Flag as 1 and other as yes.

11. Before adding to other organizations

we need to run the datafix 

---RUN DATAFIX

12.

--FOR CHILD ORG

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

,EXPENSE_ACCOUNT

)

SELECT 1,1,'CREATE',2352 --CHILD_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3083 --TEMPLATE_ID 

--,3391161

, fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT

FROM XXFIA_ITEM_INTERFACE WHERE TRIM(ITEM_CODE) NOT IN ( '67000450')

;

COMMIT;

13.

Again Run the concurrent Program from Inventory Resp

Import Items with Organization All, and Proces Flag as 1 and other as yes.

14. do the same for Non Stock Items also.

--------------Non Stock--------------

SELECT * FROM XXFIA_ITEM_INTERFACE_NS;

TEMPLATE_ID:3084 --NON STOCK

DELETE FROM mtl_system_items_interface;

1.

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

--,EXPENSE_ACCOUNT

,MARKET_PRICE

)

SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3084 --TEMPLATE_ID 

,TRUNC(EXPENSE_ACCOUNT_SEG,2)

FROM XXFIA_ITEM_INTERFACE_NS 

WHERE ITEM_CODE <>'NG000016'-- <> 'NGE00001'

AND CATEGORY_ID IS NOT NULL

AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)

--AND TRIM(ITEM_CODE) = 'NB000143'

;

2. concurrent Program.

3.

insert into MTL_ITEM_CATEGORIES_INTERFACE

(

INVENTORY_ITEM_ID,

item_number,

category_set_id,  -- SAME for all --select category_set_id,category_set_name,description,structure_name, structure_id from mtl_category_sets_v where category_set_name LIKE 'FIA%';

category_id, --updated using above script

process_flag, organization_id, set_process_id, transaction_type)

----values('TESTCATG2',1100000041,2127,1,2398,1,'CREATE');

SELECT 

(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = TRIM(ITEM_CODE) AND ORGANIZATION_ID = 2398),

TRIM(ITEM_CODE)

,1100000041 --static get once for all

,category_id

,1

,2398 --MASTER_ORG_ID

,1,'CREATE' FROM XXFIA_ITEM_INTERFACE_NS 

WHERE ITEM_CODE <> 'NG000016'--<> 'NGE00001'

AND CATEGORY_ID IS NOT NULL

--AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)

;

4. concurrent Program.

5.

--FOR CHILD ORG

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

--,EXPENSE_ACCOUNT

)

SELECT 1,1,'CREATE',2352 --CHILD_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3084 --TEMPLATE_ID 

FROM XXFIA_ITEM_INTERFACE_NS 

WHERE ITEM_CODE <> 'NG000016'

AND CATEGORY_ID IS NOT NULL

--AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)

COMMIT;

6.concurrent Program.

-----------------------------------------------Data Fix to assign items to child org-------------------

Update 2398 to your master org 

REM datafix after uploading master items_before_assign_to_Other_orgs

REM take backup of mtl_systems_items_b before running this script REM $Header: b19630365.sql 120.0.12010000.1 2014/10/28 12:08:24 liswang noship $

REMREM dbdrv sql ~PROD ~PATH ~FILE none none none sqlplus \ 

REM dbdrv phase=upg checkfile:~PROD:~PATH:~FILE 

REM +======================================================================+ 

REM |                Copyright (c) 1997, 2013 Oracle Corporation           | 

REM |                   Redwood Shores, California, USA                    | 

REM |                        All rights reserved.                          | 

REM +======================================================================+ 

REM SQL Script File Name: 

REM        b19630365.sql

REMREM Description: 

REM        This sql script updates item attributes with   

REM        R12 default value for all items. 

REMREM ======================================================================== set verify off

WHENEVER SQLERROR EXIT FAILURE ROLLBACK; 

WHENEVER OSERROR EXIT FAILURE ROLLBACK; Update mtl_system_items_b

set CATALOG_STATUS_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  CATALOG_STATUS_FLAG is null;COMMIT;Update mtl_system_items_b

set COLLATERAL_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  COLLATERAL_FLAG is null;COMMIT;Update mtl_system_items_b

set CONTAINER_ITEM_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  CONTAINER_ITEM_FLAG is null;COMMIT;Update mtl_system_items_b

set DOWNLOADABLE_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  DOWNLOADABLE_FLAG is null;COMMIT;Update mtl_system_items_b

set ELECTRONIC_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  ELECTRONIC_FLAG is null;COMMIT;Update mtl_system_items_b

set EVENT_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  EVENT_FLAG is null;COMMIT;Update mtl_system_items_b

set INDIVISIBLE_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  INDIVISIBLE_FLAG is null;COMMIT;Update mtl_system_items_b

set MRP_CALCULATE_ATP_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  MRP_CALCULATE_ATP_FLAG is null;COMMIT;Update mtl_system_items_b

set VEHICLE_ITEM_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  VEHICLE_ITEM_FLAG is null;COMMIT;Update mtl_system_items_b

set RECIPE_ENABLED_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  RECIPE_ENABLED_FLAG is null;COMMIT;Update mtl_system_items_b

set BOM_ENABLED_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  BOM_ENABLED_FLAG is null;COMMIT;Update mtl_system_items_b

set LOT_STATUS_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_STATUS_ENABLED is null;COMMIT;Update mtl_system_items_b

set SERIAL_STATUS_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  SERIAL_STATUS_ENABLED is null;COMMIT;Update mtl_system_items_b

set LOT_DIVISIBLE_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_DIVISIBLE_FLAG is null;COMMIT;Update mtl_system_items_b

set LOT_SPLIT_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_SPLIT_ENABLED is null;COMMIT;Update mtl_system_items_b

set LOT_MERGE_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_MERGE_ENABLED is null;COMMIT;Update mtl_system_items_b

set LOT_TRANSLATE_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_TRANSLATE_ENABLED is null;COMMIT;

Update mtl_system_items_b

set BULK_PICKED_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  BULK_PICKED_FLAG is null;COMMIT;

COMMIT;Update mtl_system_items_b

set EQUIPMENT_TYPE = 2

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  EQUIPMENT_TYPE is null;COMMIT;EXIT;