Oracle Apps KT

Thursday, October 27, 2022

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

New Attribute

XXRECOVERABLE_TAX

Open Process

Under Message add your new attribute value

Recoverable Tax : &XXRECOVERABLE_TAX

New Function (XX_PONOTIF_TO_SUPPLIER)

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

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)

L_RECOVER_TAX NUMBER;

L_RECOVER_TAX_CHAR VARCHAR2 (200);

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.

Thursday, October 13, 2022

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 | Redwood Shores, California, USA |

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;

Thursday, September 22, 2022

Buffer error in Toad for dbms

Add below in the script under Begin

DBMS_OUTPUT.ENABLE(1000000);

Monday, September 19, 2022

Create Customer as Organization API in apps R12

CREATE TABLE APPS.XXFIA_CUSTOMERS_INT

(

CUSTOMER_NAME VARCHAR2(240 BYTE),

ACCOUNT_NUMBER VARCHAR2(240 BYTE),

ADDRESS1 VARCHAR2(240 BYTE),

ADDRESS2 VARCHAR2(240 BYTE),

CITY VARCHAR2(240 BYTE),

COUNTRY VARCHAR2(240 BYTE),

SOURCE VARCHAR2(240 BYTE),

PHONE1 VARCHAR2(240 BYTE),

PHONE2 VARCHAR2(240 BYTE),

EMAIL VARCHAR2(240 BYTE),

CUSTOMER_ID NUMBER,

STATUS VARCHAR2(240 BYTE),

MESSAGE VARCHAR2(2400 BYTE),

COLUMN1 VARCHAR2(240 BYTE),

COLUMN2 VARCHAR2(240 BYTE),

P_LOCATION_ID VARCHAR2(240 BYTE),

P_PARTY_SITE_ID VARCHAR2(240 BYTE),

P_CUST_ACCT_SITE_ID VARCHAR2(240 BYTE),

P_SITE_USE_ID_BILLTO VARCHAR2(240 BYTE),

P_SITE_USE_ID_SHIPTO VARCHAR2(240 BYTE),

PHONE1_STATUS VARCHAR2(240 BYTE),

PHONE2_STATUS VARCHAR2(240 BYTE),

EMAIL_STATUS VARCHAR2(240 BYTE)

)

CREATE OR REPLACE PROCEDURE APPS.create_customer_FIA (

-- errbuff OUT VARCHAR2,

-- retcode OUT VARCHAR2,

P_CUSTOMER_NAME VARCHAR2,

P_ACCOUNT_NUMBER VARCHAR2,

P_ADDRESS1 VARCHAR2 DEFAULT NULL,

P_ADDRESS2 VARCHAR2 DEFAULT NULL,

P_CITY VARCHAR2 DEFAULT NULL,

P_COUNTRY VARCHAR2 DEFAULT NULL,

P_SOURCE VARCHAR2 DEFAULT NULL, --attribute6

P_EID_LICENSE_NO VARCHAR2 DEFAULT NULL, --attribute8

P_CUSTOMER_EID VARCHAR2 DEFAULT NULL, --attribute9

P_VAT_REGISTRATION_NO VARCHAR2 DEFAULT NULL, --attribute10

P_PHONE_AREA_CODE1 VARCHAR2 DEFAULT NULL,

P_PHONE1 VARCHAR2 DEFAULT NULL,

P_PHONE_AREA_CODE2 VARCHAR2 DEFAULT NULL,

P_PHONE2 VARCHAR2 DEFAULT NULL,

P_EMAIL VARCHAR2 DEFAULT NULL,

P_CUSTOMER_ID OUT VARCHAR2,

P_STATUS OUT VARCHAR2,

P_MESSAGE OUT VARCHAR2,

P_location_id OUT VARCHAR2,

P_party_site_id OUT VARCHAR2,

P_cust_acct_site_id OUT VARCHAR2,

P_site_use_id_BILLTO OUT VARCHAR2,

P_site_use_id_SHIPTO OUT VARCHAR2,

PHONE1_STATUS OUT VARCHAR2,

PHONE2_STATUS OUT VARCHAR2,

EMAIL_STATUS OUT VARCHAR2

)

p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE; --tax_header_level_flag p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE; p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;

L_CCID NUMBER;

L_PHONE_STATUS1 VARCHAR2(200);

L_PHONE_STATUS2 VARCHAR2(200);

L_EMAIL_STATUS VARCHAR2(200);

x_cust_account_id NUMBER;

x_account_number VARCHAR2 (2000); x_party_id NUMBER;

x_party_number VARCHAR2 (2000); x_profile_id NUMBER;

x_return_status VARCHAR2 (2000); x_msg_count NUMBER;

x_msg_data VARCHAR2 (2000); --2 location physical p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE; x_location_id NUMBER; --3 cust_site p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE; x_party_site_id NUMBER; x_party_site_number VARCHAR2 (2000); --4 cust_site_use p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type; x_cust_acct_site_id NUMBER; --5 Bill_to, Ship_to p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;

p_cust_site_use_rec2 HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE; --p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE; x_site_use_id NUMBER; --Local variables L_CUSTOMER_ID NUMBER;

L_VS_OUT VARCHAR2(2000);

BEGIN

-------------Initalize------------------- mo_global.init ('AR'); fnd_global.apps_initialize (user_id => 0,

resp_id => 52470,

resp_appl_id => 222); mo_global.set_policy_context ('S', 2352); fnd_global.set_nls_context ('AMERICAN'); -------------------------------- ---CHECKING IF CUSTOMER EXISTS------------------ BEGIN

SELECT CUSTOMER_ID

INTO L_CUSTOMER_ID

FROM FEA_CUSTOMER_DETAILS

WHERE ( CUSTOMER_NAME = P_CUSTOMER_NAME

OR ACCOUNT_NUMBER = P_ACCOUNT_NUMBER);

EXCEPTION

WHEN OTHERS

THEN

L_CUSTOMER_ID := 0;

END; DBMS_OUTPUT.PUT_LINE ('New Customer--------------------'||L_CUSTOMER_ID); IF L_CUSTOMER_ID = 0

THEN

------------------------------- DBMS_OUTPUT.PUT_LINE ('Creating New Customer -----------'); p_cust_account_rec.account_name := P_CUSTOMER_NAME;

p_cust_account_rec.ACCOUNT_NUMBER := P_ACCOUNT_NUMBER;

--p_cust_account_rec.

p_cust_account_rec.created_by_module := 'HZ_CPUI'; p_cust_account_rec.attribute6 := P_SOURCE; --SOURCE 10 CHAR p_cust_account_rec.attribute8 := P_EID_LICENSE_NO; --EID_LICENSE_NO 50 CHAR p_cust_account_rec.attribute9 := P_CUSTOMER_EID; --CUSTOMER_EID NUMBER p_cust_account_rec.attribute10 := P_VAT_REGISTRATION_NO; --VAT_REGISTRATION_NO 50 CHAR -- p_cust_account_rec.orig_system_reference := '001_001'; -- is not mandatory p_organization_rec.organization_name := P_CUSTOMER_NAME; p_organization_rec.created_by_module := 'HZ_CPUI'; --x_account_number := '99999'; hz_cust_account_v2pub.create_cust_account ('T',

p_cust_account_rec,

p_organization_rec,

p_customer_profile_rec,

'F',

x_cust_account_id,

x_account_number,

x_party_id,

x_party_number,

x_profile_id,

x_return_status,

x_msg_count,

x_msg_data);-- DBMS_OUTPUT.put_line (

-- '--> 1 x_cust_account_id: '

-- || x_cust_account_id

-- || ' x_account_number: '

-- || x_account_number

-- || ' x_party_id: '

-- || x_party_id

-- || ' x_profile_id: '

-- || x_profile_id

-- || ' x_msg_data: '

-- || x_msg_data); --2 location physical p_location_rec.country := NVL(P_COUNTRY,'AE');

p_location_rec.address1 := NVL (P_ADDRESS1, 'Fujairah');

p_location_rec.address2 := NVL (P_ADDRESS2, ' ');

p_location_rec.city := NVL (P_CITY, 'Fujairah'); --p_location_rec.postal_code := '94401';

IF P_COUNTRY = 'US' THEN

p_location_rec.state := '00';

p_location_rec.county := '00';

END IF;

p_location_rec.created_by_module := 'HZ_CPUI';

hz_location_v2pub.create_location ('T',

p_location_rec,

x_location_id,

x_return_status,

x_msg_count,

x_msg_data);-- DBMS_OUTPUT.put_line (

-- '--> 2 x_location_id: '

-- || x_location_id

-- || ' x_return_status: '

-- || x_return_status

-- || ' x_msg_data: '

-- || x_msg_data);

P_location_id := x_location_id;

--3 cust_site p_party_site_rec.party_id := x_party_id; --<<value for party_id from step 2> p_party_site_rec.location_id := x_location_id; --<<value for location_id from step 3> p_party_site_rec.identifying_address_flag := 'Y'; p_party_site_rec.created_by_module := 'HZ_CPUI'; hz_party_site_v2pub.create_party_site ('T',

p_party_site_rec,

x_party_site_id,

x_party_site_number,

x_return_status,

x_msg_count,

x_msg_data);-- DBMS_OUTPUT.put_line (

-- '-->3 x_party_site_id: '

-- || x_party_site_id

-- || ' x_party_site_number: '

-- || x_party_site_number

-- || ' x_msg_data: '

-- || x_msg_data);

P_party_site_id := x_party_site_id;

--4 cust_site_use p_cust_acct_site_rec.cust_account_id := x_cust_account_id; --<<value for cust_account_id you get from step 2> p_cust_acct_site_rec.party_site_id := x_party_site_id; --<<value for party_site_id from step 4> --p_cust_acct_site_rec.language := 'US'; p_cust_acct_site_rec.created_by_module := 'HZ_CPUI'; hz_cust_account_site_v2pub.create_cust_acct_site (

'T',

p_cust_acct_site_rec,

x_cust_acct_site_id,

x_return_status,

x_msg_count,

x_msg_data);-- DBMS_OUTPUT.put_line (

-- '--> 4 x_cust_acct_site_id: '

-- || x_cust_acct_site_id

-- || ' x_return_status: '

-- || x_return_status

-- || ' x_msg_data: '

-- || x_msg_data);

P_cust_acct_site_id := x_cust_acct_site_id;

--5 Bill_to p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id; --<<value for cust_acct_site_id from step 5> p_cust_site_use_rec.site_use_code := 'BILL_TO'; p_cust_site_use_rec.created_by_module := 'HZ_CPUI';

-- BEGIN

-- FIA_CREATE_CCID_VS(P_CUSTOMER_NAME,P_ACCOUNT_NUMBER,L_VS_OUT);

-- EXCEPTION WHEN OTHERS THEN NULL;

-- DBMS_OUTPUT.PUT_LINE('EXISTS IN VS');

-- END;

L_CCID := FIA_CREATE_CCID2(P_CUSTOMER_NAME,P_ACCOUNT_NUMBER);

IF L_CCID >0 THEN

DBMS_OUTPUT.PUT_LINE('L_CCID '||L_CCID);

p_cust_site_use_rec.gl_id_rec :=L_CCID;

END IF; hz_cust_account_site_v2pub.create_cust_site_use (

'T',

p_cust_site_use_rec,

p_customer_profile_rec,

'',

x_site_use_id,

x_return_status,

x_msg_count,

x_msg_data);-- DBMS_OUTPUT.put_line (

-- '--> 5BillTO x_site_use_id: '

-- || x_site_use_id

-- || ' x_msg_data: '

-- || x_msg_data);

P_site_use_id_BILLTO := x_site_use_id;

--6 SHIP_TO p_cust_site_use_rec2.cust_acct_site_id := x_cust_acct_site_id; --<<value for cust_acct_site_id from step 9> p_cust_site_use_rec2.site_use_code := 'SHIP_TO'; p_cust_site_use_rec2.created_by_module := 'HZ_CPUI'; hz_cust_account_site_v2pub.create_cust_site_use (

'T',

p_cust_site_use_rec2,

p_customer_profile_rec,

'',

x_site_use_id,

x_return_status,

x_msg_count,

x_msg_data);-- DBMS_OUTPUT.put_line (

-- '--> 6 ShipTO x_site_use_id: '

-- || x_site_use_id

-- || ' x_msg_data: '

-- || x_msg_data);

P_site_use_id_SHIPTO := x_site_use_id;

-----------CREATING PHONE FOR CUSTOMER

IF P_PHONE1 IS NOT NULL THEN

BEGIN

FIA_CREATE_PHONE_P(x_party_id,P_PHONE1 ,NVL(P_PHONE_AREA_CODE1,''),L_PHONE_STATUS1);

DBMS_OUTPUT.PUT_LINE('--> 7 Phone1 '||L_PHONE_STATUS1);

PHONE1_STATUS := L_PHONE_STATUS1;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR PHONE1 '||SQLERRM);

END;

END IF;

IF P_PHONE2 IS NOT NULL THEN

BEGIN

FIA_CREATE_PHONE_P(x_party_id,P_PHONE2 ,NVL(P_PHONE_AREA_CODE2,''),L_PHONE_STATUS2);

DBMS_OUTPUT.PUT_LINE('--> 8 Phone2 '||L_PHONE_STATUS1);

PHONE2_STATUS := L_PHONE_STATUS2;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR PHONE2 '||SQLERRM);

END;

END IF;

IF P_EMAIL IS NOT NULL THEN

BEGIN

FIA_CREATE_EMAIL_P(x_party_id,P_EMAIL,L_EMAIL_STATUS);

DBMS_OUTPUT.PUT_LINE('--> 8 Email '||L_EMAIL_STATUS);

EMAIL_STATUS := L_EMAIL_STATUS;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR Email '||SQLERRM);

END;

END IF;

COMMIT; P_CUSTOMER_ID := x_cust_account_id; P_STATUS := 'S'; P_MESSAGE := x_msg_data;

ELSE

P_CUSTOMER_ID := NULL; P_STATUS := 'E'; P_MESSAGE := 'Customer Name Already Exists';

END IF;

EXCEPTION

WHEN OTHERS

THEN

P_CUSTOMER_ID := NULL; P_STATUS := 'E'; P_MESSAGE := SQLERRM || ' ' || x_msg_data; DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

---------------------------------------------------------

Block to Run

DECLARE

L_CUSTOMER_ID VARCHAR2(240);

L_STATUS VARCHAR2(240);

L_MSG VARCHAR2(240);

location_id VARCHAR2(240);party_site_id VARCHAR2(240);cust_acct_site_id VARCHAR2(240);

site_use_id_BILLTO VARCHAR2(240);site_use_id_SHIPTO VARCHAR2(240);PHONE1_STATUS VARCHAR2(240);

PHONE2_STATUS VARCHAR2(240);EMAIL_STATUS VARCHAR2(240);

CURSOR C1 IS

SELECT * FROM XXFIA_CUSTOMERS_INT

WHERE ACCOUNT_NUMBER NOT IN ('A1173', 'A1120','A0092','A0091','RD042')

--WHERE STATUS = 'E'

AND CUSTOMER_ID IS NULL

;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

FOR I IN C1 LOOP

BEGIN

DBMS_OUTPUT.PUT_LINE('ANOY BLOCK-----------------'||I.ACCOUNT_NUMBER);

create_customer_FIA(

TRIM(I.CUSTOMER_NAME)

,TRIM(I.ACCOUNT_NUMBER)

,NVL(TRIM(I.ADDRESS1),'FUJAIRAH')

,NULL

,TRIM(I.CITY)

,NVL(TRIM(I.COUNTRY),'AE')

,TRIM(I.SOURCE),NULL,NULL,NULL,NULL

,TRIM(I.PHONE1),NULL

,TRIM(I.PHONE2)

,TRIM(I.EMAIL)

,L_CUSTOMER_ID,L_STATUS,L_MSG,

location_id ,party_site_id ,cust_acct_site_id ,

site_use_id_BILLTO ,site_use_id_SHIPTO ,PHONE1_STATUS ,

PHONE2_STATUS ,EMAIL_STATUS

);

UPDATE XXFIA_CUSTOMERS_INT

SET CUSTOMER_ID = L_CUSTOMER_ID,

STATUS =L_STATUS,

MESSAGE=L_MSG,

P_location_id =location_id ,P_party_site_id =party_site_id ,P_cust_acct_site_id =cust_acct_site_id,

P_site_use_id_BILLTO =site_use_id_BILLTO ,P_site_use_id_SHIPTO =site_use_id_SHIPTO,PHONE1_STATUS =PHONE1_STATUS,

PHONE2_STATUS=PHONE2_STATUS ,EMAIL_STATUS=EMAIL_STATUS

WHERE ACCOUNT_NUMBER = I.ACCOUNT_NUMBER;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('EXP---'||SQLERRM||'-'||I.ACCOUNT_NUMBER);

L_MSG := SQLERRM;

UPDATE XXFIA_CUSTOMERS_INT

SET STATUS ='E'

, MESSAGE = L_MSG

WHERE ACCOUNT_NUMBER = I.ACCOUNT_NUMBER

;

END;

END LOOP;

END;

---------------------Tax Profile Update-----------------

/***IMPORTANT*****/

--when we create customer with all details, a entry will be entered into table ZX_PARTY_TAX_PROFILE, we need toupdate 'Allow Tax Applicability' using below api

DECLARE

CURSOR C1 IS SELECT PARTY_TAX_PROFILE_ID, CREATED_BY FROM ZX_PARTY_TAX_PROFILE

WHERE CREATION_DATE LIKE SYSDATE

and party_type_code = 'THIRD_PARTY'

AND ALLOW_OFFSET_TAX_FLAG IS NULL

AND CREATED_BY IN (2605,0)

ORDER BY CREATION_DATE DESC

;

X_RETURN_STATUS VARCHAR2 (1);

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

FOR I IN C1 LOOP

ZX_PARTY_TAX_PROFILE_PKG.update_row (

P_PARTY_TAX_PROFILE_ID => I.PARTY_TAX_PROFILE_ID,

P_COLLECTING_AUTHORITY_FLAG => NULL,

P_PROVIDER_TYPE_CODE => NULL,

P_CREATE_AWT_DISTS_TYPE_CODE => NULL,

P_CREATE_AWT_INVOICES_TYPE_COD => NULL,

P_TAX_CLASSIFICATION_CODE => NULL, --Tax Classification

P_SELF_ASSESS_FLAG => 'N',

P_ALLOW_OFFSET_TAX_FLAG => 'N', --Allow Offset Taxes

P_REP_REGISTRATION_NUMBER => NULL,

P_EFFECTIVE_FROM_USE_LE => NULL,

P_RECORD_TYPE_CODE => NULL,

P_REQUEST_ID => NULL,

P_ATTRIBUTE1 => NULL,

P_ATTRIBUTE2 => NULL,

P_ATTRIBUTE3 => NULL,

P_ATTRIBUTE4 => NULL,

P_ATTRIBUTE5 => NULL,

P_ATTRIBUTE6 => NULL,

P_ATTRIBUTE7 => NULL,

P_ATTRIBUTE8 => NULL,

P_ATTRIBUTE9 => NULL,

P_ATTRIBUTE10 => NULL,

P_ATTRIBUTE11 => NULL,

P_ATTRIBUTE12 => NULL,

P_ATTRIBUTE13 => NULL,

P_ATTRIBUTE14 => NULL,

P_ATTRIBUTE15 => NULL,

P_ATTRIBUTE_CATEGORY => NULL,

P_PARTY_ID => NULL,

P_PROGRAM_LOGIN_ID => NULL,

P_PARTY_TYPE_CODE => NULL,

P_SUPPLIER_FLAG => NULL,

P_CUSTOMER_FLAG => NULL,

P_SITE_FLAG => NULL,

P_PROCESS_FOR_APPLICABILITY_FL => 'Y', --Allow Tax Applicability

P_ROUNDING_LEVEL_CODE => 'HEADER', -- Rounding Level

P_ROUNDING_RULE_CODE => 'UP', --Rounding Rule

P_WITHHOLDING_START_DATE => NULL,

P_INCLUSIVE_TAX_FLAG => 'N',

P_ALLOW_AWT_FLAG => NULL,

P_USE_LE_AS_SUBSCRIBER_FLAG => NULL,

P_LEGAL_ESTABLISHMENT_FLAG => NULL,

P_FIRST_PARTY_LE_FLAG => NULL,

P_REPORTING_AUTHORITY_FLAG => NULL,

X_RETURN_STATUS => X_RETURN_STATUS,

P_REGISTRATION_TYPE_CODE => NULL,

P_COUNTRY_CODE => NULL

);

COMMIT;

DBMS_OUTPUT.PUT_LINE ('RETURN STATUS :' || X_RETURN_STATUS);

END LOOP;

EXCEPTION WHEN OTHERS THEN NULL;

END;

-----------------------block to add missing value sets .optional as client wants the customer as Segment4 dependent value for Accounts-------------------

--ADDING MISSING VALUESETS IN SEPARATE BLOCK

DECLARE

X VARCHAR2(240);

L_OUT VARCHAR2(240);

CURSOR C1 IS

SELECT * FROM XXFIA_CUSTOMERS_INT

WHERE ACCOUNT_NUMBER NOT IN ('A0092','RD052');

BEGIN

FOR I IN C1 LOOP

BEGIN

FIA_CREATE_CCID_VS(I.CUSTOMER_NAME, I.ACCOUNT_NUMBER,X);

COMMIT;

EXCEPTION WHEN OTHERS THEN NULL;

ROLLBACK;

END;

END LOOP;

--L_OUT := FIA_CREATE_CCID2('AEROTRANS CARGO','A1120');

--DBMS_OUTPUT.PUT_LINE(L_OUT);

END;

CREATE OR REPLACE PROCEDURE FIA_CREATE_CCID_VS

( P_CUSTOMER_NAME VARCHAR2,

P_ACCOUNT_NUMBER VARCHAR2,

P_OUT OUT VARCHAR2)

BEGIN

fnd_global.apps_initialize (-1, 52470, 222);

FND_FLEX_VAL_API.create_dependent_vset_value

('FIA SUB ACCOUNT','111000',P_ACCOUNT_NUMBER, P_CUSTOMER_NAME,'Y',NULL,NULL,NULL,P_OUT);

-- DBMS_OUTPUT.PUT_LINE(P_OUT);

EXCEPTION WHEN OTHERS THEN NULL;

DBMS_OUTPUT.PUT_LINE(SQLERRM);

ROLLBACK;

-- ROLLBACK;

END;

------------------------------view---------------------

CREATE OR REPLACE VIEW FEA_CUSTOMER_DETAILS AS

SELECT DISTINCT hp.party_name "CUSTOMER_NAME",

hca.account_number,

hca.status,

hca.cust_account_id customer_id,

HOU.NAME "OPERATING_UNIT"

FROM apps.hz_parties hp,

apps.hz_party_sites hps,

apps.hz_locations hl,

apps.hz_cust_accounts hca,

apps.hz_cust_acct_sites_all hcas,

apps.hz_cust_site_uses_all hcsu,

--apps.hz_customer_profiles hcp,

--apps.hz_cust_profile_classes hcpc,

--apps.ar_payment_schedules_all ps,

apps.hr_operating_units hou

WHERE hp.party_id = hca.party_id(+)

--AND hp.party_id = hcp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcas.party_site_id

AND hps.location_id = hl.location_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

--AND hca.cust_account_id = hcp.cust_account_id

--AND hca.cust_account_id = ps.customer_id

--AND hcp.profile_class_id = hcpc.profile_class_id

--AND ps.customer_site_use_id =hcsu.site_use_id

and hcsu.org_id =hou.organization_id

and hcsu.ORG_ID = 215900

ORDER BY 4

;

Errors And Solutioins:

Error: customer account failed:Column account_number must have a value.

Solutioin: As per the setup you need to pass p_cust_account_rec.account_number parameter also.

Tuesday, September 6, 2022

decrypt the Oracle Package code using url

https://www.codecrete.net/UnwrapIt/

Thursday, September 1, 2022

Solution for ORA-20001: FLEX-VALUE DOES NOT EXIST: N, VALUE, 481, N, SEGMENT, Employer, N, VALUESET, HR_AE_LEGAL_EMPLOYER

ORA-20001: FLEX-VALUE DOES NOT EXIST: N, VALUE, 481, N, SEGMENT, Employer, N, VALUESET, HR_AE_LEGAL_EMPLOYER

try below 2 steps:

insert into FND_SESSIONS

values (USERENV('SESSIONID'), trunc(sysdate));

commit;

2. apps_initialize

fnd_global.apps_initialize

--(l_user_id,l_responsibility_id,l_resp_appl_id);

Tuesday, May 17, 2022

Delete fnd_user in oracle apps r12

This is not recommended nor supported.

Use as last option, after testing.

DECLARE
BEGIN
FOR X IN (select * from fnd_user where user_name in ('FIA_10179'))
LOOP
DELETE FROM fnd_user where user_name=X.user_name;
fnd_function_security_cache.delete_user(X.user_id);
END LOOP;

END;

Search This Blog

Thursday, October 27, 2022

Thursday, October 13, 2022

Thursday, September 22, 2022

Monday, September 19, 2022

Tuesday, September 6, 2022

Thursday, September 1, 2022

Tuesday, May 17, 2022