Thursday, 19 October 2017

Oracle Fusion HCM Data Loader (HDL) Keys Significance

HCM Data Loader (HDL) Keys

Supported Key Types:
HCM Data Loader (HDL) supports 4 different types of keys as listed below 
  1. GUID – Oracle Fusion Global Unique ID
  2. Oracle Fusion Surrogate ID
  3. Source Keys
  4. User Keys 
Key Resolution Sequence (Key references are resolved) in the following order:

1. Oracle Fusion GUID

2. Source key

3. Oracle Fusion surrogate ID

4. User key

Important Notes 
1. While  Create/First time Loading any object(s) we have to consider only SOURCE KEY / USER KEY no need to think about GUID or Surrogate ID (because those are created only after an object created in fusion).
2.While Modify/Updating/DELETE any object then all four keys comes into picture.

If you supply multiple key values, then they are used in this order with no cross-validation. For example, if you supply both a GUID and a source key, then the GUID is used to identify the record and the source key is ignored. If the source key references a different record from the GUID, then no error is raised.
These key types are explained below:

Oracle Fusion GUID
Integration Key Generated by Fusion
  • Generated in Oracle Fusion when a record is created
  • Hexadecimal value
  • Unique across all objects
  • Held in Integration Key Map
Oracle Fusion Surrogate ID
Fusion Generated Unique ID
  • Generated in Oracle Fusion when the record is created
  • Numeric value
  • Unique only for the object type
  • Held on the object
Source Keys
Source System Key Information
  • Two values combined:
    • SourceSystemOwner
    • SourceSystemID
  • Held in Integration Key Map

User Keys
User Readable and Generated Keys
  • Natural values
  • One or many attributes
  • Sometimes alternatives
  • Sometimes updateable
  • Held on the object definition
     

Key Type
Create
Update
Held on Object
Type
Generated Automatically
GUID
No
Yes
No
Hexadecimal
Yes
Surrogate ID
No
Yes (see note #1)
Yes
Numeric
Yes
Source Key
Yes
Yes
No
Alphanumeric
Conditionally (see note #2)
User Key
Yes
Yes (see note #3)
Yes
Alphanumeric
No
Notes:-
  1. You can use surrogate IDs when updating objects, but the IDs may not be readily available to Oracle HCM Cloud users.
  2. Default source keys are generated only if you don't supply a source key when creating an object.
  3. You can't use user keys alone when updating some objects because their values are subject to change.
  4. Keys that aren't held on the object exist in the Integration Key Map table.
Integration Key Map table
Keys that aren’t held on the object are stored in the HDL integration key map table - HRC_INTEGRATION_KEY_MAP. You should be able to use BIP to fire up a SQL statement and view the contents of this table. e.g. 
/* Formatted on 2017/09/12 14:25 (Formatter Plus v4.8.8) */
SELECT object_name
     ,source_system_id
     ,source_system_owner
     ,surrogate_id
     ,RAWTOHEX (guid) guid
FROM fusion.hrc_integration_key_map
WHERE source_system_owner = 'STUDENT1'


Business Object
Fusion GUID
Source Key
Surrage ID
User Key
Location
88ABCD164738983
STUDENT1_LOC1
 8899933355517
 (Set code and Location Code) COMMON, HQ1
  • Fusion GUID: System generated GUID
  • Source Key:  Source System Owner is the reference to source application like PS or EBS, Source System Key is the actual key\id provided in the Location.dat file. 
  • Surrogate ID: System generated. In this case it is the primary key from locations record. e.g. select * from PER_LOCATION_DETAILS_F_VL  where location code = 'STUDENT1 Location1‘   (Result Location ID = 8899933355517)
  • User Key:- Best way to get this info is the Business Object Documentation from MOS or other option is UI as shown below. Online page should highlight user keys with




Oracle Apps(EBS) - AR Receipt Register Query with Bank statement Header and Line Details


Below query is useful when you required  Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off)

SELECT ACRA.RECEIPT_DATE
,( select distinct CSH.STATEMENT_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  STATEMENT_NUMBER
,( select CSL.LINE_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  LINE_NUMBER
,ACRA.RECEIPT_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER), NVL(SUBSTR(HCA.ACCOUNT_NUMBER, INSTR(HCA.ACCOUNT_NUMBER, '.')+1), HCA.ACCOUNT_NUMBER)) CUSTOMER_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,HP_ONACC.PARTY_NAME,HP.PARTY_NAME ) CUSTOMER_NAME
,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,RCTA.TRX_NUMBER) APPLIED_TO
,ARCAA.APPLY_DATE
,art.name ACTIVITY_NAME
,ARCAA.AMOUNT_APPLIED
,ACRA.AMOUNT RECEIPT_AMOUNT
,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
,FU.USER_NAME APPLIED_USER
,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
apps.ar_cash_receipts_all ACRA,
apps.ar_cash_receipt_history_all ACRHA,
apps.ra_customer_trx_all RCTA,
apps.hz_cust_accounts HCA,
apps.hz_parties HP,
apps.hz_cust_accounts HCA_ONACC,
apps.hz_parties HP_ONACC,
apps.fnd_user FU
,ar_receivables_trx_ALL art                         
                  WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID(+)
AND ACRHA.CASH_RECEIPT_ID(+)=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID=HP.PARTY_ID(+)
AND HCA_ONACC.CUST_ACCOUNT_ID(+)=ARCAA.ON_ACCT_CUST_ID
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND(( HP.PARTY_NAME between NVL(:p_customer_name_low,HP.PARTY_NAME) AND NVL(:p_customer_name_high,HP.PARTY_NAME)) or
(HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME))
   )
                            AND( ( HCA.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA.ACCOUNT_NUMBER) or
   (HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER))
  )
   )
                            AND ARCAA.ORG_ID=:p_org AND
                            ACRA.ORG_ID=:p_org AND
                            ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'
AND ARCAA.REVERSAL_GL_DATE is NULL
and ACRHA.REVERSAL_GL_DATE is NULL
            UNION
                    SELECT ACRA.RECEIPT_DATE
                            ,NULL STATEMENT_NUMBER
                            ,NULL LINE_NUMBER
                            ,ACRA.RECEIPT_NUMBER
                            ,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER) CUSTOMER_NUMBER
                            ,HP_ONACC.PARTY_NAME CUSTOMER_NAME
                            ,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,null) APPLIED_TO
                            ,ARCAA.APPLY_DATE
                            ,art.name ACTIVITY_NAME
                            ,ARCAA.AMOUNT_APPLIED
                            ,ACRA.AMOUNT RECEIPT_AMOUNT
                            ,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
                            ,FU.USER_NAME APPLIED_USER
                            ,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
                            apps.ar_cash_receipts_all ACRA,
                            apps.ar_cash_receipt_history_all ACRHA,
                            apps.hz_cust_accounts HCA_ONACC,
                            apps.hz_parties HP_ONACC,
                            apps.fnd_user FU
                            ,ar_receivables_trx_ALL art
                    WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ACRHA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID IS NULL
AND (HCA_ONACC.CUST_ACCOUNT_ID=ARCAA.ON_ACCT_CUST_ID or HCA_ONACC.CUST_ACCOUNT_ID=ACRA.PAY_FROM_CUSTOMER )
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
                            AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id AND
ARCAA.ORG_ID=:p_org
AND ACRA.ORG_ID=:p_org
AND ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'                         
                            AND ARCAA.APPLIED_PAYMENT_SCHEDULE_ID <0
                            AND ARCAA.AMOUNT_APPLIED >0
                            AND ARCAA.REVERSAL_GL_DATE IS NULL
                            AND ACRHA.REVERSAL_GL_DATE IS NULL
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME)
                            AND HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER)
                            order by RECEIPT_NUMBER,CREATION_DATE ASC  ;

Tuesday, 17 October 2017

Query to get the Project Expenditures Defined for an Employee for a Specific Period

SELECT
  EXPT.EXPENDITURE_TYPE ,
  EXPI.Quantity ,
  CASE
    WHEN (EXPT.unit_of_measure='HOURS')
    THEN NVL(CDL.PROJECT_BURDENED_COST,cdl.project_raw_cost)
  END Raw_cost ,
  --EXPI.DENOM_RAW_COST
  EXPI.ACCRUED_REVENUE ,
  EXPI.expenditure_item_id ,
  EXPI.expenditure_id ,
  EXPI.EXPENDITURE_ITEM_DATE,
  PAP.GL_PERIOD_NAME GL_PERIOD,
  PAP.PERIOD_NAME PA_PERIOD,
  PAP.START_DATE Period_Start_Date,
  PAP.END_DATE Period_End_Date,
  --  (SELECT
  --DECODE(EXPT.Expenditure_type,'Straight Time',EXPI.Quantity,'Overtime 0.0X',EXPI.Quantity, 'Overtime 1.0X', EXPI.Quantity,'Overtime 1.5X',EXPI.Quantity,'Overtime 2.0X',EXPI.Quantity, 'Overtime 2.5X',EXPI.Quantity,'Premium',EXPI.quantity,0)Total_Hours,
  CASE
    WHEN EXPT.Expenditure_category='Labor'
    AND EXPT.unit_of_measure      ='HOURS'
    THEN EXPI.Quantity
    ELSE 0
  END Total_Hours,
  --  (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN EXPI.QUANTITY
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN EXPI.QUANTITY
        ELSE 0
      END
    ELSE 0
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --      PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --  AND EXPI.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  --  AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  OT_DT_Hours,
  -- (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE NOT IN('Overtime 0.0','Overtime 1.5X','Overtime 2.0X','Overtime 1.0X','Overtime 2.5X','Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT','Union Vac/Supp Dues')
        THEN EXPI.QUANTITY
      END
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --     PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --    AND EXP.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  -- AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  Regular_Hours,
  CDL.acct_raw_cost,
  EXP.INCURRED_BY_PERSON_ID
FROM apps.PA_PERIODS_ALL pap,
  apps.PA_COST_DISTRIBUTION_LINES_ALL CDL,
  apps.PA_EXPENDITURE_ITEMS_ALL EXPI,
  apps.PA_EXPENDITURE_TYPES EXPT,
  apps.PA_EXPENDITURES_ALL EXP
WHERE CDL.PA_DATE BETWEEN PAP.START_DATE(+) AND PAP.END_DATE(+)
AND CDL.ORG_ID                 = PAP.ORG_ID(+)
AND PAP.GL_PERIOD_NAME         ='SEP-17'
AND CDL.LINE_NUM(+)            = 1
AND CDL.EXPENDITURE_ITEM_ID(+) = EXPI.EXPENDITURE_ITEM_ID
AND EXPI.EXPENDITURE_TYPE      = EXPT.EXPENDITURE_TYPE
AND EXP.EXPENDITURE_ID         = EXPI.EXPENDITURE_ID
AND EXP.INCURRED_BY_PERSON_ID IS NOT NULL

Monday, 16 October 2017

JDeveloper Installation and Setting Environment

Prerequisites 

Desktop with 1.5 GB RAM 

1.Telnet and FTP access to apps and db server 
2.Database connectivity details:
3.Apps username and password,
4.SID,
5.Host Name and port. 
6.Exact version of OA Framework on server

Get the JDeveloper software ( Ex:ZIP File  : p4141787_11i_GENERIC.zip )

Copy  into required drive ( folder ) / ( C:\)
Eg: C:\ JDEV  ( create JDEV ( name can be any one ) folder in C-drive

Extract the ZIP file name p4141787_11i_GENERIC.zip )
Right click à WinZip à Extract To Here
After extracting it generates following
JDEV---- ( user created folder )
|____ jdevbin  
|____ Jdevdoc
|____   jdevhome

Take the shortcut of  C:\JDEV\jdevbin\jdev\bin\ jdevW.exe     to desktop

Copy the  TEST.dbc file form Oracle Apps Server to JDeveloper
Source Oracle Apps path:   
\oracle\inst_TOP\apps\test2\appl\fnd\12.0.0\secure\TEST.dbc
JDeveloprer Path: C:\JDEV\jdevhome\jdev\dbc_files\secure
 
Set the Environment variables of O/S
My Computer à Advanced à Environment Variables à New à 
Variable Name : JDEV_USER_HOME
Variable Value : C:\JDEV\jdevhome\jdev
OK à OKàOK

Testing Functionality of Jdeveloper
Go to connection  à Right Click à New database connection à Next à 
Connection Name : test ( as desired )
Connection Type : Oracle ( JDBC )
Next :
User Name : apps
Password : apps
Next
Driver  : thin
Host Name : localhost ( if database is on the local system, else URL of DB server )
JDBC Port: 1521
SID       : VIS

For details see the vis.dbc located in the folder :

\\oracle\inst_TOP\apps\fnd\12.0.3\secure\apps
APPS_JDBC_URL=jdbc\:oracle\:thin\:@(DESCRIPTION\=(LOAD_BALANCE\=YES)(FAILOVER\=YES)(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=tcp)(HOST\=APPS.ora.com)(PORT\=1521)))(CONNECT_DATA\=(SID\=VIS)))
Next
Test Connection
Result: success
Next
Finish


ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters

Symptoms:-

Concurrent program completed warning with   “ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters” this reason.

Solution:-

SQL> alter session set "_fix_control"='17376322:OFF'; 

or at system level : 
SQL> alter system set "_fix_control"='17376322:OFF'; 

Enabling Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility

How to Enable Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility
Goal:-
What are the steps to enable Create/View Accounting for a user-defined responsibility?
Solution:-
Please add Subfunction "SLA: View Accounting - Lines Inquiries" and "XLA: View Accounting Lines" into your own menu as following:

Responsibility: System Administrator
Navigation : Application > Menu

   Query for the Menu value: FUN_AR_RECEIPT_PROCESSING
   Scroll down and add following lines :


         Sequence: Next Number
         Prompt: Leave blank
         Submenu: Leave blank
         Function: 'SLA: View Accounting - Lines Inquiries' from the LOV
         Description: Leave blank
       Sequence: Next Number
         Prompt: Leave blank
         Submenu: Leave blank
         Function: 'XLA: View Accounting Lines' from the LOV
         Description: Anything say "View Accounting Lines"

then save the change and retest the issue.

Workflow download and upload commands in Oracle apps

Workflow upload
WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]
Example:
WFLOAD apps/pwd@<connect_string> 0 Y UPLOAD $XXDOY_TOP/install/XXDOYAP.wft

Different “Upload Modes” applicable to WFLOAD:

UPGRADE
Honors both protection and customization levels of data
UPLOAD
Honors only protection level of data [No respect of Customization Level]
FORCE
Force upload regardless of protection or customization level

Workflow Download
WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD file_name.wft <Item_Type>

Example:
WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD XXDOYAP.wft