Oracle Apps KT

Monday, June 12, 2017

Create PO requisition in R12 from backend and apps initialization for OU's.

1. Package to insert data into PO_REQUISITIONS_INTERFACE_ALL

CREATE OR REPLACE PACKAGE FUJ_EMP_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION(P_EMPLOYEE_NUMBER VARCHAR2) ;
END;

CREATE OR REPLACE PACKAGE BODY FUJ_EMP_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION( P_EMPLOYEE_NUMBER VARCHAR2)
IS
CURSOR C1 IS

SELECT EMPLOYEE_NUMBER,
DECODE(INFO1,1,QUOT_AMOUNT1,
DECODE(INFO2,2,QUOT_AMOUNT2,
DECODE(INFO3,3,QUOT_AMOUNT3
)))AMOUNT
FROM XXFUJ_TICKET_DETAILS
WHERE EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER

;

        BEGIN

FOR I IN C1 LOOP

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           --PREPARER_NAME,
                                           CHARGE_ACCOUNT_ID,
                                           SOURCE_TYPE_CODE,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           CATEGORY_ID,
                                           UNIT_PRICE,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           HEADER_DESCRIPTION,
                                           ITEM_DESCRIPTION,
                                           SUGGESTED_VENDOR_ID,
                                           SUGGESTED_VENDOR_SITE_ID,
                                           --ACCRUAL_ACCOUNT_ID ,
                                           --VARIANCE_ACCOUNT_ID,
                                           --BUDGET_ACCOUNT_ID,
                                           HEADER_ATTRIBUTE1,
                                           HEADER_ATTRIBUTE2,
                                           HEADER_ATTRIBUTE3,
                                           HEADER_ATTRIBUTE4,
                                           GL_DATE,
                                           REQUISITION_HEADER_ID
                                           )
     VALUES ('IMPORT_EXP',                                  --Interface Source
             102,                                             --Operating Unit
             'EXPENSE',                                     --Destination Type
            'APPROVED',--'INCOMPLETE',       -- 'APPROVED',--                                     --Status
             26876,--6439,                      --This comes from per_people_f.person_id
            -- '???? ??? ????? ????? ?????????',
             106847,                                      --Code Combination ID
             'VENDOR',                                           --Source Type
             'Quantity',                                                     --UOM ok??yes
             1,                                           --Line Type of Goods
             1123,                                           --MISC.MISC Category
             I.AMOUNT,                                                       --Price
             1,                                                    --quantity
             108,--102                      --Represents Vision Operations Inv Org.
             142,--162,                                --Represents V1-New York City
             26876,--6439 ,                            --This is the Deliver to Requestor
             I.EMPLOYEE_NUMBER,                        --One Time Header Description
             I.EMPLOYEE_NUMBER,                        --One Time Item Description
             222,
             261,
             --17979,--Accrual
             --106847,--Varianlce
             --106847, --Budget
             'Y',--Header. ATT1
             'India',--Header 2
            'New Delhi',--Header 3
             I.EMPLOYEE_NUMBER,--Header 4
             TO_DATE('01-JAN-2017'),
              PO_REQUISITION_HEADERS_S.NEXTVAL
                           );
                           COMMIT;
/*
UPDATE XXFUJ_TICKET_DETAILS
SET INFO4 = 'PR Created Success'
WHERE EMPLOYEE_NUMBER = I.EMPLOYEE_NUMBER;
COMMIT;
*/
END LOOP;

EXCEPTION WHEN OTHERS THEN NULL;

        END;
END;

2.to run concurrent program 'Requisition Import' from backend:

DECLARE
V_OUTPUT NUMBER;
BEGIN
         mo_global.init ('PO');
MO_GLOBAL.set_policy_context('S',102);
fnd_global.apps_initialize (2605,50597,201,0,0);
FND_REQUEST.SET_ORG_ID(102); --this is passed as operating unit
V_OUTPUT := fnd_request.submit_request
                        (
                         application      => 'PO',
                         program          => 'REQIMPORT',
                         description      => '',
                         start_time       => TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'),
                         sub_request      => FALSE,
                         argument1        => 'IMPORT_EXP',--Interface Source code,
                         argument2        => '', ----APPS.SONA_BATCH_ID_S.CURRVAL,--Batch ID,
                         argument3        => 'BUYER',--Group By,
                         argument4        => '',--Last Req Number,
                         argument5        => 'N',--Multi Distributions,
                         argument6        => 'Y'
                          ) ;
DBMS_OUTPUT.PUT_LINE(   V_OUTPUT);
commit;
EXCEPTION WHEN OTHERS THEN
                      DBMS_OUTPUT.PUT_LINE( SQLERRM);
END;

3. To view exceptions after the above concurrent program.

Run the concurrent program 'Requisition Import Exceptions Report '.

Thursday, May 25, 2017

Oracle Apps R12 Creating customers as Organization

Check the http://mogalafzal.blogspot.ae/2017/05/oracle-apps-r12-creating-customers-as.html for custom table and conc program and issues related.

For the API use the below script, its different from above link.

DECLARE
CURSOR C1 IS
SELECT * FROM XXFUJ_CUST_UPLOAD
WHERE CUST_NUMBER <> 'CUST1003';
BEGIN
   FOR I IN C1 LOOP


   INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL
   ( ADDRESS1
   --, ADDRESS2
   , ORIG_SYSTEM_CUSTOMER_REF
   , ORIG_SYSTEM_ADDRESS_REF
   , SITE_USE_CODE
   , INSERT_UPDATE_FLAG
   , CUSTOMER_NAME
   , CUSTOMER_NUMBER
   , PERSON_FLAG
   , CUSTOMER_STATUS
   , PRIMARY_SITE_USE_FLAG
-- , CITY
-- , STATE
-- , POSTAL_CODE
   , COUNTRY
   --, COUNTY
   --, JGZZ_FISCAL_CODE
    ,LOCATION
   , ORG_ID
   , LAST_UPDATE_DATE
   , LAST_UPDATED_BY
   , CREATION_DATE
   , CREATED_BY
   --, PERSON_FIRST_NAME
-- , PERSON_LAST_NAME
   )
   VALUES
   ( I.CUST_ADDR1 -- ADDRESS1
   --, I.CUST_ADDR2 -- ADDRESS2
   , 'FUJ_CUST'||'-'||FUJ_CUST_S.NEXTVAL        -- ORIG_SYSTEM_CUSTOMER_REF
   , 'FUJ_CUST'||'-'||FUJ_CUST_ADD_S.NEXTVAL       -- ORIG_SYSTEM_ADDRESS_REF
   , 'BILL_TO'          -- SITE_USE_CODE
   , 'I'                -- INSERT_UPDATE_FLAG
   , I.CUST_NAME_ENG      -- CUSTOMER_NAME
   , I.CUST_NUMBER           -- CUSTOMER_NUMBER
   , 'N'                -- PERSON_FLAG
   , 'A'                -- CUSTOMER_STATUS
   , 'Y'                -- PRIMARY_SITE_USE_FLAG
-- , I.CUST_CITY           -- CITY
-- , 'MN'               -- STATE
-- , 55400              -- POSTAL_CODE
   , 'AE'               -- COUNTRY
   --, 'SCOTT'            -- COUNTY
   --, '21-3456789'       -- JGZZ_FISCAL_CODE
   , 'Fujairah'
   , 1730                  -- ORG_ID
   , SYSDATE            -- LAST_UPDATE_DATE
   , 2605               -- LAST_UPDATED_BY
   , SYSDATE            -- CREATION_DATE
   , 2605               -- CREATED_BY
-- , I.CUST_FIRST_NAME_AR
   --, I.CUST_LAST_NAME_AR
   )

;

   INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL
   ( ADDRESS1
   --, ADDRESS2
   , ORIG_SYSTEM_CUSTOMER_REF
   , ORIG_SYSTEM_ADDRESS_REF
   , SITE_USE_CODE
   , INSERT_UPDATE_FLAG
   , CUSTOMER_NAME
   , CUSTOMER_NUMBER
   , PERSON_FLAG
   , CUSTOMER_STATUS
   , PRIMARY_SITE_USE_FLAG
-- , CITY
-- , STATE
-- , POSTAL_CODE
   , COUNTRY
   --, COUNTY
   --, JGZZ_FISCAL_CODE
    ,LOCATION
   , ORG_ID
   , LAST_UPDATE_DATE
   , LAST_UPDATED_BY
   , CREATION_DATE
   , CREATED_BY
   --, PERSON_FIRST_NAME
-- , PERSON_LAST_NAME
   )
   VALUES
   ( I.CUST_ADDR1 -- ADDRESS1
   --, I.CUST_ADDR2 -- ADDRESS2
   , 'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL      -- ORIG_SYSTEM_CUSTOMER_REF
   , 'FUJ_CUST'||'-'||FUJ_CUST_ADD_S.CURRVAL   -- ORIG_SYSTEM_ADDRESS_REF
   , 'SHIP_TO'          -- SITE_USE_CODE
   , 'I'                -- INSERT_UPDATE_FLAG
   , I.CUST_NAME_ENG      -- CUSTOMER_NAME
   , I.CUST_NUMBER           -- CUSTOMER_NUMBER
   , 'N'                -- PERSON_FLAG
   , 'A'                -- CUSTOMER_STATUS
   , 'N'                -- PRIMARY_SITE_USE_FLAG
-- , I.CUST_CITY           -- CITY
-- , 'MN'               -- STATE
-- , 55400              -- POSTAL_CODE
   , 'AE'               -- COUNTRY
   --, 'SCOTT'            -- COUNTY
   --, '21-3456789'       -- JGZZ_FISCAL_CODE
   , 'Fujairah'
   , 1730                  -- ORG_ID
   , SYSDATE            -- LAST_UPDATE_DATE
   , 2605               -- LAST_UPDATED_BY
   , SYSDATE            -- CREATION_DATE
   , 2605               -- CREATED_BY
-- , I.CUST_FIRST_NAME_AR
   --, I.CUST_LAST_NAME_AR
   );

   --
   -- Insert records inot profile interface table
   --
   INSERT into ar.ra_customer_profiles_int_all
   ( ORIG_SYSTEM_CUSTOMER_REF
   , INSERT_UPDATE_FLAG
   , ORG_ID
   , CREDIT_HOLD
   , CUSTOMER_PROFILE_CLASS_NAME
   , LAST_UPDATE_DATE
   , LAST_UPDATED_BY
   , CREATION_DATE
   , CREATED_BY
   )
   VALUES
   ( 'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL -- ORIG_SYSTEM_CUSTOMER_REF
   , 'I'            -- INSERT_UPDATE_FLAG
   , 1730              -- ORG_ID
   , 'N'            -- CREDIT_HOLD
   , 'DEFAULT'      -- CUSTOMER_PROFILE_CLASS_NAME
   , SYSDATE        -- LAST_UPDATE_DATE
   , 2605           -- LAST_UPDATED_BY
   , SYSDATE        -- CREATION_DATE
   , 2605           -- CREATED_BY
   )
;
/*
INSERT
INTO
ra_contact_phones_int_all
(
orig_system_contact_ref,
orig_system_telephone_ref,
orig_system_customer_ref,
orig_system_address_ref,
insert_update_flag,
contact_first_name,
contact_last_name,
contact_title,
contact_job_title,
telephone,
--telephone_extension,
telephone_type,
telephone_area_code,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
sex_code,
salutation,
email_address,
mail_stop,
contact_key,
org_id
--contact_job_title_code,
,contact_point_type
)
VALUES
(
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_contact_ref,
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_telephone_ref,
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_customer_ref,
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_address_ref,
'I',
'CUSTOMERFirstNAME0', --->:contact_first_name,
'CUSTOMERlastNAME0', --->:contact_last_name,
NULL, --->:contact_title,
NULL, --->:contact_job_title,
40986341, --->:telephone,
--6341, --->:telephone_extension,
'MOBILE', --->:telephone_type,
971, --->:telephone_area_code,
2605,
SYSDATE,
2605,
SYSDATE,
2605,
'Male', --->:sex_code,
NULL, --->:salutation,
'vphilip@fexico.ae',--->:email_address,
NULL, --->:mail_stop,
NULL, --->:contact_key,
1708
--Customer, --->:contact_job_title_code,
,'PHONE' --->:contact_point_type
)

;
*/
--commit;

--->:====================================================================
END LOOP;
COMMIT;
END;
/

Oracle Apps R12 Creating customers as Person

Creating customers as Person:

Custom table.
Upload using plsql tool, so Arabic will be uploaded correctly.
Insert data into interface tables.
Run the concurrent program.’ Customer Interface’

CREATE TABLE APPS.XXFUJ_CUST_UPLOAD

(

SNO NUMBER,

CUST_NUMBER VARCHAR2(18 BYTE),

OU_ID NUMBER,

OU_NAME VARCHAR2(200 BYTE), --OPTIONAL

CUST_NAME_ENG VARCHAR2(2000 BYTE), --OPTIONAL

CUST_FIRST_NAME_AR VARCHAR2(200 BYTE),

CUST_SECOND_NAME_AR VARCHAR2(200 BYTE), --OPTIONAL

CUST_LAST_NAME_AR VARCHAR2(200 BYTE),

CUST_ADDR1 VARCHAR2(240 BYTE),

CUST_ADDR2 VARCHAR2(240 BYTE),

CUST_CITY VARCHAR2(240 BYTE),

CUST_GENDER VARCHAR2(240 BYTE),

FUTURE1 VARCHAR2(240 BYTE), --OPTIONAL

FUTURE2 VARCHAR2(240 BYTE), --OPTIONAL

FUTURE3 VARCHAR2(240 BYTE), --OPTIONAL

FUTURE4 VARCHAR2(240 BYTE), --OPTIONAL

FUTURE5 VARCHAR2(240 BYTE --OPTIONAL

)

Chk the screen shots below.

Plsql developer>tools>ODBC Importer

Data from ODBC Tab>

Connection:

Excel files –if you don’t see Excel Files then you need to chk other version of plsql dev.

Apps

>connect

>select the excel file

>Table/Query : select the sheet

Data to Oracle >Tab

General:

Apps

XXFUJ_CUST_UPLOAD –table

Please be sure that excel column names are similar to the table column names, else you can map here.

Click Import.

Pls chk below for screenshots.

3. Interface tables:

ar.RA_CUSTOMERS_INTERFACE_ALL
ar.ra_customer_profiles_int_all

I am uploading only customer info and address, so using only above 2 interfaces, for other info to upload, chk the other interface tables.

Creating sequences:

CREATE SEQUENCE APPS.FUJ_CUST_S

START WITH 29595

MAXVALUE 9999999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER;

CREATE SEQUENCE APPS.FUJ_CUST_ADD_S

START WITH 29595

MAXVALUE 99999999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER;

Insert custom table data into interface tables:

--TRUNCATE TABLE ar.ra_customer_profiles_int_all

-- Insert record into customer interface table

DECLARE

CURSOR C1 IS

SELECT * FROM XXFUJ_CUST_UPLOAD

WHERE SNO BETWEEN 29594 AND 300000;

BEGIN

FOR I IN C1 LOOP

INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL

( ADDRESS1

, ADDRESS2

, ORIG_SYSTEM_CUSTOMER_REF

, ORIG_SYSTEM_ADDRESS_REF

, SITE_USE_CODE

, INSERT_UPDATE_FLAG

, CUSTOMER_NAME

, CUSTOMER_NUMBER

, PERSON_FLAG

, CUSTOMER_STATUS

, PRIMARY_SITE_USE_FLAG

, CITY

-- , STATE

-- , POSTAL_CODE

, COUNTRY

--, COUNTY

--, JGZZ_FISCAL_CODE

, ORG_ID

, LAST_UPDATE_DATE

, LAST_UPDATED_BY

, CREATION_DATE

, CREATED_BY

, PERSON_FIRST_NAME

, PERSON_LAST_NAME

)

VALUES

( I.CUST_ADDR1 -- ADDRESS1

, I.CUST_ADDR2 -- ADDRESS2

, 'FUJ_CUST'||'-'||FUJ_CUST_S.NEXTVAL -- ORIG_SYSTEM_CUSTOMER_REF

, 'FUJ_CUST'||'-'||FUJ_CUST_ADD_S.NEXTVAL -- ORIG_SYSTEM_ADDRESS_REF

, 'BILL_TO' -- SITE_USE_CODE

, 'I' -- INSERT_UPDATE_FLAG

, I.CUST_FIRST_NAME_AR -- CUSTOMER_NAME

, I.CUST_NUMBER -- CUSTOMER_NUMBER

, 'Y' -- PERSON_FLAG

, 'A' -- CUSTOMER_STATUS

, 'Y' -- PRIMARY_SITE_USE_FLAG

, I.CUST_CITY -- CITY

-- , 'MN' -- STATE

-- , 55400 -- POSTAL_CODE

, 'AE' -- COUNTRY

--, 'SCOTT' -- COUNTY

--, '21-3456789' -- JGZZ_FISCAL_CODE

, 145 -- ORG_ID

, SYSDATE -- LAST_UPDATE_DATE

, 2605 -- LAST_UPDATED_BY

, SYSDATE -- CREATION_DATE

, 2605 -- CREATED_BY

, I.CUST_FIRST_NAME_AR

, I.CUST_LAST_NAME_AR

)

;

/*if u need ship_to also, then copy the above insert code twice and change the BILL_TO to SHIP_TO and change the sequence NEXTVAL to CURRVAL for SHIP_TO */

-- Insert records inot profile interface table

INSERT into ar.ra_customer_profiles_int_all

( ORIG_SYSTEM_CUSTOMER_REF

, INSERT_UPDATE_FLAG

, ORG_ID

, CREDIT_HOLD

, CUSTOMER_PROFILE_CLASS_NAME

, LAST_UPDATE_DATE

, LAST_UPDATED_BY

, CREATION_DATE

, CREATED_BY

)

VALUES

( 'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL -- ORIG_SYSTEM_CUSTOMER_REF

, 'I' -- INSERT_UPDATE_FLAG

, 145 -- ORG_ID

, 'N' -- CREDIT_HOLD

, 'DEFAULT' -- CUSTOMER_PROFILE_CLASS_NAME

, SYSDATE -- LAST_UPDATE_DATE

, 2605 -- LAST_UPDATED_BY

, SYSDATE -- CREATION_DATE

, 2605 -- CREATED_BY

)

;

END LOOP;

COMMIT;

END;

4.. Running the concurrent program:” Customer Interface”

$C:\Users\egov\Desktop\customer7.png$

$C:\Users\egov\Desktop\customer8.png$

$C:\Users\egov\Desktop\customer1.png$

$C:\Users\egov\Desktop\customer2.png$

$C:\Users\egov\Desktop\customer3.png$

$C:\Users\egov\Desktop\customer4.png$

$C:\Users\egov\Desktop\customer5.png$

$C:\Users\egov\Desktop\customer6.png$

Errors and solutions:

Check the log file of the Concurrent program for details of errors and update the interface tables accordingly.

Once uploaded data from the interface tables, will be deleted automatically from the interface tables.

So the records in the interface tables are not-uploaded data - data with error for sure.

Search This Blog