Discoverer Parameters

Creating parameter in Discoverer report


After Creating the workbook,Click on tools->Parameters

Hit on new add the column name in which u need the parameters to pick.

To add between condition add new condition and select the parameter as the condition item.

Party Merge Alert CRM

                                                                     
                                                                     
                                                                     
                                             
ALERTS


1.Party Merge

Table from :HZ_MERGE_PARTIES 
Table to Update:XXPN_TRANSACTION_LINES
Alert_Name:XX_CUST_ALERT
Application:Receivables

Event type:

SELECT FROM_PARTY_ID,TO_PARTY_ID 
INTO &FROM_PARTY,&TO_PARTY
FROM  APPS.HZ_MERGE_PARTIES  
WHERE FROM_PARTY_ID!=TO_PARTY_ID
AND MERGE_TYPE='PARTY_MERGE'
AND MERGE_REASON_CODE='MERGER'
AND ROWID=:ROWID

Action Type:

begin
UPDATE XXPN_TRANSACTION_LINES
SET PARTY_ID=&TO_PARTY
WHERE PARTY_ID=&FROM_PARTY;
end;
/


************************************************

2.Account Merge

Table from RA_CUSTOMER_MERGE_HEADERS 
Table to XXPN_TRANSACTION_LINES
Alert name:XX_ACCOUNT_MERGE


Event type: select DUPLICATE_ID,CUSTOMER_ID 
      INTO &FROM_CUST,&TO_CUST
      from RA_CUSTOMER_MERGE_HEADERS 
      where MERGE_REASON_CODE='MERGER'
      and PROCESS_FLAG='Y'
      and DUPLICATE_ID!=CUSTOMER_ID
      AND ROWID=:ROWID
      
      
      Action_type:
      
      BEGIN
      UPDATE APPS.XXPN_TRANSACTION_LINES 
      SET CUSTOMER_ACCOUNT_ID=&TO_CUST WHERE CUSTOMER_ACCOUNT_ID=&FROM_CUST;
      END;
      /

Default date in report

When we transfer the custom report to the application,we get error on input parameter date while running because of mismatch of date format type:

the default type of the date in application is YYYY/MM/DD HH24:MI:SS

Change in the format of input parameter date and transfer to application.

Set who Columns

Set who columns in pre-insert trigger in block level trigger

FND_STANDARD.SET_WHO;


The WHO columns are

CREATED BY,
CREATION_DATE,
LAST_UPDATE_BY,
LAST_UPDATED_DATE

Check Points

After executing a fndload check the ldt files,if it has extracted the current objects details
e.g:

sample ldt file for a report where it has extracted the report name,valuset etc:--


# $Header$

# dbdrv: exec fnd bin FNDLOAD bin &phase=daa+56 checkfile:~PROD:~PATH:~FILE &ui_apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct @~PROD:~PATH/~FILE
LANGUAGE = "US"
LDRCONFIG = "afcpprog.lct 120.2.12010000.2"

#Source Database UAYSUF

#RELEASE_NAME 12.1.3

# -- Begin Entity Definitions --

DEFINE PROGRAM
  KEY   CONCURRENT_PROGRAM_NAME         VARCHAR2(30)
  KEY   APPLICATION_SHORT_NAME          VARCHAR2(50)
  CTX   OWNER                           VARCHAR2(4000)
  BASE  LAST_UPDATE_DATE                VARCHAR2(75)
  TRANS USER_CONCURRENT_PROGRAM_NAME    VARCHAR2(240)
  BASE  EXEC                            REFERENCES EXECUTABLE
  BASE  EXECUTION_METHOD_CODE           VARCHAR2(1)
  BASE  ARGUMENT_METHOD_CODE            VARCHAR2(1)
  BASE  QUEUE_CONTROL_FLAG              VARCHAR2(1)
  BASE  QUEUE_METHOD_CODE               VARCHAR2(1)
  BASE  REQUEST_SET_FLAG                VARCHAR2(1)
  BASE  ENABLED_FLAG                    VARCHAR2(1)
  BASE  PRINT_FLAG                      VARCHAR2(1)
  BASE  RUN_ALONE_FLAG                  VARCHAR2(1)
  BASE  SRS_FLAG                        VARCHAR2(1)
  TRANS DESCRIPTION                     VARCHAR2(240)
  BASE  CLASS_APPLICATION               VARCHAR2(50)
  BASE  CONCURRENT_CLASS_NAME           VARCHAR2(30)
  BASE  EXECUTION_OPTIONS               VARCHAR2(250)
  BASE  SAVE_OUTPUT_FLAG                VARCHAR2(1)
  BASE  REQUIRED_STYLE                  VARCHAR2(1)
  BASE  OUTPUT_PRINT_STYLE              VARCHAR2(30)
  BASE  PRINTER_NAME                    VARCHAR2(30)
  BASE  MINIMUM_WIDTH                   VARCHAR2(50)
  BASE  MINIMUM_LENGTH                  VARCHAR2(50)
  BASE  REQUEST_PRIORITY                VARCHAR2(50)
  BASE  ATTRIBUTE_CATEGORY              VARCHAR2(30)
  BASE  ATTRIBUTE1                      VARCHAR2(150)
  BASE  ATTRIBUTE2                      VARCHAR2(150)
  BASE  ATTRIBUTE3                      VARCHAR2(150)
  BASE  ATTRIBUTE4                      VARCHAR2(150)
  BASE  ATTRIBUTE5                      VARCHAR2(150)
  BASE  ATTRIBUTE6                      VARCHAR2(150)
  BASE  ATTRIBUTE7                      VARCHAR2(150)
  BASE  ATTRIBUTE8                      VARCHAR2(150)
  BASE  ATTRIBUTE9                      VARCHAR2(150)
  BASE  ATTRIBUTE10                     VARCHAR2(150)
  BASE  ATTRIBUTE11                     VARCHAR2(150)
  BASE  ATTRIBUTE12                     VARCHAR2(150)
  BASE  ATTRIBUTE13                     VARCHAR2(150)
  BASE  ATTRIBUTE14                     VARCHAR2(150)
  BASE  ATTRIBUTE15                     VARCHAR2(150)
  BASE  OUTPUT_FILE_TYPE                VARCHAR2(4)
  BASE  RESTART                         VARCHAR2(1)
  BASE  NLS_COMPLIANT                   VARCHAR2(1)
  BASE  CD_PARAMETER                    VARCHAR2(240)
  BASE  INCREMENT_PROC                  VARCHAR2(61)
  BASE  MLS_EXEC                        REFERENCES EXECUTABLE
  BASE  ENABLE_TIME_STATISTICS          VARCHAR2(1)
  BASE  SECURITY_GROUP_NAME             VARCHAR2(30)
  BASE  RESOURCE_CONSUMER_GROUP         VARCHAR2(30)
  BASE  ROLLBACK_SEGMENT                VARCHAR2(30)
  BASE  OPTIMIZER_MODE                  VARCHAR2(30)
  BASE  REFRESH_PORTLET                 VARCHAR2(1)
  BASE  ACTIVITY_SUMMARIZER             VARCHAR2(51)
  BASE  PROGRAM_TYPE                    VARCHAR2(1)
  BASE  ALLOW_MULTIPLE_PENDING_REQUEST  VARCHAR2(1)
  BASE  SRS_FLEX                        REFERENCES DESC_FLEX
  BASE  AUTO_ANNOTATION                 VARCHAR2(32000)
  BASE  TEMPLATE_APPL_SHORT_NAME        VARCHAR2(50)
  BASE  TEMPLATE_CODE                   VARCHAR2(80)
  BASE  MULTI_ORG_CATEGORY              VARCHAR2(1)

  DEFINE ANNOTATION
    KEY   CONCURRENT_PROGRAM_NAME3        VARCHAR2(30)
    KEY   APPLICATION_SHORT_NAME3         VARCHAR2(50)
    CTX   OWNER                           VARCHAR2(4000)
    BASE  LAST_UPDATE_DATE                VARCHAR2(75)
    BASE  PROGRAM_ANNOTATION              VARCHAR2(32000)
  END ANNOTATION

    DEFINE INCOMPATABILITY
      KEY   CONCURRENT_PROGRAM_NAME2        VARCHAR2(30)
      KEY   APPLICATION_SHORT_NAME2         VARCHAR2(50)
      CTX   OWNER                           VARCHAR2(4000)
      BASE  LAST_UPDATE_DATE                VARCHAR2(75)
      BASE  RUNNING_TYPE                    VARCHAR2(1)
      BASE  TO_RUN_TYPE                     VARCHAR2(1)
      BASE  INCOMPATIBILITY_TYPE            VARCHAR2(1)
    END INCOMPATABILITY
END PROGRAM

DEFINE EXECUTABLE
  KEY   EXECUTABLE_NAME                 VARCHAR2(30)
  KEY   APPLICATION_SHORT_NAME          VARCHAR2(50)
  CTX   OWNER                           VARCHAR2(4000)
  BASE  LAST_UPDATE_DATE                VARCHAR2(75)
  BASE  EXECUTION_METHOD_CODE           VARCHAR2(1)
  BASE  EXECUTION_FILE_NAME             VARCHAR2(61)
  BASE  SUBROUTINE_NAME                 VARCHAR2(30)
  BASE  EXECUTION_FILE_PATH             VARCHAR2(510)
  TRANS USER_EXECUTABLE_NAME            VARCHAR2(240)
  TRANS DESCRIPTION                     VARCHAR2(240)
END EXECUTABLE

DEFINE DESC_FLEX
  KEY   APPLICATION_SHORT_NAME          VARCHAR2(50)
  KEY   DESCRIPTIVE_FLEXFIELD_NAME      VARCHAR2(40)
  CTX   OWNER                           VARCHAR2(4000)
  CTX   LAST_UPDATE_DATE                VARCHAR2(50)
  BASE  TABLE_APPLICATION_SHORT_NAME    VARCHAR2(50)
  BASE  APPLICATION_TABLE_NAME          VARCHAR2(30)
  BASE  CONTEXT_COLUMN_NAME             VARCHAR2(30)
  BASE  CONTEXT_REQUIRED_FLAG           VARCHAR2(1)
  BASE  CONTEXT_USER_OVERRIDE_FLAG      VARCHAR2(1)
  BASE  CONCATENATED_SEGMENT_DELIMITER  VARCHAR2(1)
  BASE  FREEZE_FLEX_DEFINITION_FLAG     VARCHAR2(1)
  BASE  PROTECTED_FLAG                  VARCHAR2(1)
  BASE  DEFAULT_CONTEXT_FIELD_NAME      VARCHAR2(200)
  BASE  DEFAULT_CONTEXT_VALUE           VARCHAR2(30)
  BASE  CONTEXT_DEFAULT_TYPE            VARCHAR2(1)
  BASE  CONTEXT_DEFAULT_VALUE           VARCHAR2(2000)
  BASE  CONTEXT_OVERRIDE_VALUE_SET_NAM  REFERENCES VALUE_SET
  BASE  CONTEXT_RUNTIME_PROPERTY_FUNCT  VARCHAR2(2000)
  TRANS TITLE                           VARCHAR2(60)
  TRANS FORM_CONTEXT_PROMPT             VARCHAR2(45)
  TRANS DESCRIPTION                     VARCHAR2(240)

  DEFINE DFF_CONTEXT
    KEY   DESCRIPTIVE_FLEX_CONTEXT_CODE   VARCHAR2(30)
    CTX   OWNER                           VARCHAR2(4000)
    CTX   LAST_UPDATE_DATE                VARCHAR2(50)
    BASE  ENABLED_FLAG                    VARCHAR2(1)
    BASE  GLOBAL_FLAG                     VARCHAR2(1)
    TRANS DESCRIPTIVE_FLEX_CONTEXT_NAME   VARCHAR2(80)
    TRANS DESCRIPTION                     VARCHAR2(240)

    DEFINE DFF_SEGMENT
      KEY   END_USER_COLUMN_NAME            VARCHAR2(30)
      KEY   APPLICATION_COLUMN_NAME         VARCHAR2(30)
      CTX   OWNER                           VARCHAR2(4000)
      CTX   LAST_UPDATE_DATE                VARCHAR2(50)
      BASE  COLUMN_SEQ_NUM                  VARCHAR2(50)
      BASE  ENABLED_FLAG                    VARCHAR2(1)
      BASE  DISPLAY_FLAG                    VARCHAR2(1)
      BASE  REQUIRED_FLAG                   VARCHAR2(1)
      BASE  SECURITY_ENABLED_FLAG           VARCHAR2(1)
      BASE  FLEX_VALUE_SET_NAME             REFERENCES VALUE_SET
      BASE  DISPLAY_SIZE                    VARCHAR2(50)
      BASE  MAXIMUM_DESCRIPTION_LEN         VARCHAR2(50)
      BASE  CONCATENATION_DESCRIPTION_LEN   VARCHAR2(50)
      BASE  RANGE_CODE                      VARCHAR2(1)
      BASE  DEFAULT_TYPE                    VARCHAR2(1)
      BASE  DEFAULT_VALUE                   VARCHAR2(2000)
      BASE  RUNTIME_PROPERTY_FUNCTION       VARCHAR2(2000)
      BASE  SRW_PARAM                       VARCHAR2(30)
      TRANS FORM_LEFT_PROMPT                VARCHAR2(80)
      TRANS FORM_ABOVE_PROMPT               VARCHAR2(80)
      TRANS DESCRIPTION                     VARCHAR2(240)
    END DFF_SEGMENT
  END DFF_CONTEXT

    DEFINE DFF_REF_FIELD
      KEY   DEFAULT_CONTEXT_FIELD_NAME      VARCHAR2(200)
      CTX   OWNER                           VARCHAR2(4000)
      CTX   LAST_UPDATE_DATE                VARCHAR2(50)
      TRANS DESCRIPTION                     VARCHAR2(240)
    END DFF_REF_FIELD

      DEFINE DFF_COLUMN
        KEY   COLUMN_NAME                     VARCHAR2(30)
        CTX   OWNER                           VARCHAR2(4000)
        CTX   LAST_UPDATE_DATE                VARCHAR2(50)
        BASE  FLEXFIELD_USAGE_CODE            VARCHAR2(1)
      END DFF_COLUMN
END DESC_FLEX

DEFINE VALUE_SET
  KEY   FLEX_VALUE_SET_NAME             VARCHAR2(60)
  CTX   OWNER                           VARCHAR2(4000)
  CTX   LAST_UPDATE_DATE                VARCHAR2(50)
  BASE  VALIDATION_TYPE                 VARCHAR2(1)
  BASE  PROTECTED_FLAG                  VARCHAR2(1)
  BASE  SECURITY_ENABLED_FLAG           VARCHAR2(1)
  BASE  LONGLIST_FLAG                   VARCHAR2(1)
  BASE  FORMAT_TYPE                     VARCHAR2(1)
  BASE  MAXIMUM_SIZE                    VARCHAR2(50)
  BASE  NUMBER_PRECISION                VARCHAR2(50)
  BASE  ALPHANUMERIC_ALLOWED_FLAG       VARCHAR2(1)
  BASE  UPPERCASE_ONLY_FLAG             VARCHAR2(1)
  BASE  NUMERIC_MODE_ENABLED_FLAG       VARCHAR2(1)
  BASE  MINIMUM_VALUE                   VARCHAR2(150)
  BASE  MAXIMUM_VALUE                   VARCHAR2(150)
  BASE  PARENT_FLEX_VALUE_SET_NAME      VARCHAR2(60)
  BASE  DEPENDANT_DEFAULT_VALUE         VARCHAR2(60)
  BASE  DEPENDANT_DEFAULT_MEANING       VARCHAR2(240)
  TRANS DESCRIPTION                     VARCHAR2(240)

  DEFINE VSET_VALUE
    KEY   PARENT_FLEX_VALUE_LOW           VARCHAR2(60)
    KEY   FLEX_VALUE                      VARCHAR2(150)
    CTX   OWNER                           VARCHAR2(4000)
    CTX   LAST_UPDATE_DATE                VARCHAR2(50)
    BASE  ENABLED_FLAG                    VARCHAR2(1)
    BASE  SUMMARY_FLAG                    VARCHAR2(1)
    BASE  START_DATE_ACTIVE               VARCHAR2(50)
    BASE  END_DATE_ACTIVE                 VARCHAR2(50)
    BASE  PARENT_FLEX_VALUE_HIGH          VARCHAR2(60)
    BASE  ROLLUP_HIERARCHY_CODE           VARCHAR2(30)
    BASE  HIERARCHY_LEVEL                 VARCHAR2(50)
    BASE  COMPILED_VALUE_ATTRIBUTES       VARCHAR2(2000)
    BASE  VALUE_CATEGORY                  VARCHAR2(30)
    BASE  ATTRIBUTE1                      VARCHAR2(240)
    BASE  ATTRIBUTE2                      VARCHAR2(240)
    BASE  ATTRIBUTE3                      VARCHAR2(240)
    BASE  ATTRIBUTE4                      VARCHAR2(240)
    BASE  ATTRIBUTE5                      VARCHAR2(240)
    BASE  ATTRIBUTE6                      VARCHAR2(240)
    BASE  ATTRIBUTE7                      VARCHAR2(240)
    BASE  ATTRIBUTE8                      VARCHAR2(240)
    BASE  ATTRIBUTE9                      VARCHAR2(240)
    BASE  ATTRIBUTE10                     VARCHAR2(240)
    BASE  ATTRIBUTE11                     VARCHAR2(240)
    BASE  ATTRIBUTE12                     VARCHAR2(240)
    BASE  ATTRIBUTE13                     VARCHAR2(240)
    BASE  ATTRIBUTE14                     VARCHAR2(240)
    BASE  ATTRIBUTE15                     VARCHAR2(240)
    BASE  ATTRIBUTE16                     VARCHAR2(240)
    BASE  ATTRIBUTE17                     VARCHAR2(240)
    BASE  ATTRIBUTE18                     VARCHAR2(240)
    BASE  ATTRIBUTE19                     VARCHAR2(240)
    BASE  ATTRIBUTE20                     VARCHAR2(240)
    BASE  ATTRIBUTE21                     VARCHAR2(240)
    BASE  ATTRIBUTE22                     VARCHAR2(240)
    BASE  ATTRIBUTE23                     VARCHAR2(240)
    BASE  ATTRIBUTE24                     VARCHAR2(240)
    BASE  ATTRIBUTE25                     VARCHAR2(240)
    BASE  ATTRIBUTE26                     VARCHAR2(240)
    BASE  ATTRIBUTE27                     VARCHAR2(240)
    BASE  ATTRIBUTE28                     VARCHAR2(240)
    BASE  ATTRIBUTE29                     VARCHAR2(240)
    BASE  ATTRIBUTE30                     VARCHAR2(240)
    BASE  ATTRIBUTE31                     VARCHAR2(240)
    BASE  ATTRIBUTE32                     VARCHAR2(240)
    BASE  ATTRIBUTE33                     VARCHAR2(240)
    BASE  ATTRIBUTE34                     VARCHAR2(240)
    BASE  ATTRIBUTE35                     VARCHAR2(240)
    BASE  ATTRIBUTE36                     VARCHAR2(240)
    BASE  ATTRIBUTE37                     VARCHAR2(240)
    BASE  ATTRIBUTE38                     VARCHAR2(240)
    BASE  ATTRIBUTE39                     VARCHAR2(240)
    BASE  ATTRIBUTE40                     VARCHAR2(240)
    BASE  ATTRIBUTE41                     VARCHAR2(240)
    BASE  ATTRIBUTE42                     VARCHAR2(240)
    BASE  ATTRIBUTE43                     VARCHAR2(240)
    BASE  ATTRIBUTE44                     VARCHAR2(240)
    BASE  ATTRIBUTE45                     VARCHAR2(240)
    BASE  ATTRIBUTE46                     VARCHAR2(240)
    BASE  ATTRIBUTE47                     VARCHAR2(240)
    BASE  ATTRIBUTE48                     VARCHAR2(240)
    BASE  ATTRIBUTE49                     VARCHAR2(240)
    BASE  ATTRIBUTE50                     VARCHAR2(240)
    TRANS FLEX_VALUE_MEANING              VARCHAR2(150)
    TRANS DESCRIPTION                     VARCHAR2(240)

    DEFINE VSET_VALUE_QUAL_VALUE
      KEY   ID_FLEX_APPLICATION_SHORT_NAME  VARCHAR2(50)
      KEY   ID_FLEX_CODE                    VARCHAR2(4)
      KEY   SEGMENT_ATTRIBUTE_TYPE          VARCHAR2(30)
      KEY   VALUE_ATTRIBUTE_TYPE            VARCHAR2(30)
      CTX   OWNER                           VARCHAR2(4000)
      CTX   LAST_UPDATE_DATE                VARCHAR2(50)
      BASE  COMPILED_VALUE_ATTRIBUTE_VALUE  VARCHAR2(2000)
    END VSET_VALUE_QUAL_VALUE

      DEFINE VSET_VALUE_HIERARCHY
        KEY   RANGE_ATTRIBUTE                 VARCHAR2(1)
        KEY   CHILD_FLEX_VALUE_LOW            VARCHAR2(60)
        KEY   CHILD_FLEX_VALUE_HIGH           VARCHAR2(60)
        CTX   OWNER                           VARCHAR2(4000)
        CTX   LAST_UPDATE_DATE                VARCHAR2(50)
        BASE  START_DATE_ACTIVE               VARCHAR2(50)
        BASE  END_DATE_ACTIVE                 VARCHAR2(50)
      END VSET_VALUE_HIERARCHY
  END VSET_VALUE

    DEFINE VSET_QUALIFIER
      KEY   ID_FLEX_APPLICATION_SHORT_NAME  VARCHAR2(50)
      KEY   ID_FLEX_CODE                    VARCHAR2(4)
      KEY   SEGMENT_ATTRIBUTE_TYPE          VARCHAR2(30)
      KEY   VALUE_ATTRIBUTE_TYPE            VARCHAR2(30)
      CTX   OWNER                           VARCHAR2(4000)
      CTX   LAST_UPDATE_DATE                VARCHAR2(50)
      BASE  ASSIGNMENT_ORDER                VARCHAR2(50)
      BASE  ASSIGNMENT_DATE                 VARCHAR2(50)
    END VSET_QUALIFIER

      DEFINE VSET_ROLLUP_GROUP
        KEY   HIERARCHY_CODE                  VARCHAR2(30)
        CTX   OWNER                           VARCHAR2(4000)
        CTX   LAST_UPDATE_DATE                VARCHAR2(50)
        TRANS HIERARCHY_NAME                  VARCHAR2(30)
        TRANS DESCRIPTION                     VARCHAR2(240)
      END VSET_ROLLUP_GROUP

        DEFINE VSET_SECURITY_RULE
          KEY   FLEX_VALUE_RULE_NAME            VARCHAR2(30)
          KEY   PARENT_FLEX_VALUE_LOW           VARCHAR2(60)
          CTX   OWNER                           VARCHAR2(4000)
          CTX   LAST_UPDATE_DATE                VARCHAR2(50)
          BASE  PARENT_FLEX_VALUE_HIGH          VARCHAR2(60)
          TRANS ERROR_MESSAGE                   VARCHAR2(240)
          TRANS DESCRIPTION                     VARCHAR2(240)

          DEFINE VSET_SECURITY_USAGE
            KEY   APPLICATION_SHORT_NAME          VARCHAR2(50)
            KEY   RESPONSIBILITY_KEY              VARCHAR2(30)
            CTX   OWNER                           VARCHAR2(4000)
            CTX   LAST_UPDATE_DATE                VARCHAR2(50)
            BASE  PARENT_FLEX_VALUE_HIGH          VARCHAR2(60)
          END VSET_SECURITY_USAGE

            DEFINE VSET_SECURITY_LINE
              KEY   INCLUDE_EXCLUDE_INDICATOR       VARCHAR2(1)
              KEY   FLEX_VALUE_LOW                  VARCHAR2(60)
              KEY   FLEX_VALUE_HIGH                 VARCHAR2(60)
              CTX   OWNER                           VARCHAR2(4000)
              CTX   LAST_UPDATE_DATE                VARCHAR2(50)
              BASE  PARENT_FLEX_VALUE_HIGH          VARCHAR2(60)
            END VSET_SECURITY_LINE
        END VSET_SECURITY_RULE

          DEFINE VSET_EVENT
            KEY   EVENT_CODE                      VARCHAR2(1)
            CTX   OWNER                           VARCHAR2(4000)
            CTX   LAST_UPDATE_DATE                VARCHAR2(50)
            BASE  USER_EXIT                       VARCHAR2(32000)
          END VSET_EVENT

            DEFINE VSET_TABLE
              CTX   OWNER                           VARCHAR2(4000)
              CTX   LAST_UPDATE_DATE                VARCHAR2(50)
              BASE  TABLE_APPLICATION_SHORT_NAME    VARCHAR2(50)
              BASE  APPLICATION_TABLE_NAME          VARCHAR2(240)
              BASE  SUMMARY_ALLOWED_FLAG            VARCHAR2(1)
              BASE  VALUE_COLUMN_NAME               VARCHAR2(240)
              BASE  VALUE_COLUMN_TYPE               VARCHAR2(1)
              BASE  VALUE_COLUMN_SIZE               VARCHAR2(50)
              BASE  ID_COLUMN_NAME                  VARCHAR2(240)
              BASE  ID_COLUMN_TYPE                  VARCHAR2(1)
              BASE  ID_COLUMN_SIZE                  VARCHAR2(50)
              BASE  MEANING_COLUMN_NAME             VARCHAR2(240)
              BASE  MEANING_COLUMN_TYPE             VARCHAR2(1)
              BASE  MEANING_COLUMN_SIZE             VARCHAR2(50)
              BASE  ENABLED_COLUMN_NAME             VARCHAR2(240)
              BASE  COMPILED_ATTRIBUTE_COLUMN_NAME  VARCHAR2(240)
              BASE  HIERARCHY_LEVEL_COLUMN_NAME     VARCHAR2(240)
              BASE  START_DATE_COLUMN_NAME          VARCHAR2(240)
              BASE  END_DATE_COLUMN_NAME            VARCHAR2(240)
              BASE  SUMMARY_COLUMN_NAME             VARCHAR2(240)
              BASE  ADDITIONAL_WHERE_CLAUSE         VARCHAR2(32000)
              BASE  ADDITIONAL_QUICKPICK_COLUMNS    VARCHAR2(240)
            END VSET_TABLE

              DEFINE VSET_DEPENDS_ON
                KEY   IND_FLEX_VALUE_SET_NAME         VARCHAR2(60)
                CTX   OWNER                           VARCHAR2(4000)
                CTX   LAST_UPDATE_DATE                VARCHAR2(50)
                BASE  IND_VALIDATION_TYPE             VARCHAR2(1)
                BASE  DEP_VALIDATION_TYPE             VARCHAR2(1)
              END VSET_DEPENDS_ON
END VALUE_SET

# -- End Entity Definitions --


BEGIN EXECUTABLE "XXAYE_PO_CONFIRMATION" "XXAYE"
  OWNER = "MEERAM"
  LAST_UPDATE_DATE = "2011/11/21"
  EXECUTION_METHOD_CODE = "P"
  EXECUTION_FILE_NAME = "XXAYE_PO_CONFIRMATION"
  USER_EXECUTABLE_NAME = "XXAYE_PO_CONFIRMATION"
END EXECUTABLE

BEGIN VALUE_SET "XXAYE_PONUM_VS"
  OWNER = "MEERAM"
  LAST_UPDATE_DATE = "2011/11/21 17:39:47"
  VALIDATION_TYPE = "F"
  PROTECTED_FLAG = "N"
  SECURITY_ENABLED_FLAG = "N"
  LONGLIST_FLAG = "N"
  FORMAT_TYPE = "C"
  MAXIMUM_SIZE = "20"
  ALPHANUMERIC_ALLOWED_FLAG = "Y"
  UPPERCASE_ONLY_FLAG = "N"
  NUMERIC_MODE_ENABLED_FLAG = "N"


  BEGIN VSET_TABLE
    OWNER = "ARAJ"
    LAST_UPDATE_DATE = "2011/11/22 09:00:23"
    TABLE_APPLICATION_SHORT_NAME = "PO"
    APPLICATION_TABLE_NAME = "XXAYE_PO_NUM_SET"
    SUMMARY_ALLOWED_FLAG = "N"
    VALUE_COLUMN_NAME = "SEGMENT1"
    VALUE_COLUMN_TYPE = "V"
    VALUE_COLUMN_SIZE = "20"
    ENABLED_COLUMN_NAME = "'Y'"
    COMPILED_ATTRIBUTE_COLUMN_NAME = "NULL"
    HIERARCHY_LEVEL_COLUMN_NAME = "NULL"
    START_DATE_COLUMN_NAME = "to_date(null)"
    END_DATE_COLUMN_NAME = "to_date(null)"
    SUMMARY_COLUMN_NAME = "'N'"
  END VSET_TABLE
 





END VALUE_SET

BEGIN VALUE_SET "XXAYE_PODATE_VS"
  OWNER = "MEERAM"
  LAST_UPDATE_DATE = "2011/11/21 17:42:22"
  VALIDATION_TYPE = "F"
  PROTECTED_FLAG = "N"
  SECURITY_ENABLED_FLAG = "N"
  LONGLIST_FLAG = "N"
  FORMAT_TYPE = "Y"
  MAXIMUM_SIZE = "20"
  ALPHANUMERIC_ALLOWED_FLAG = "Y"
  UPPERCASE_ONLY_FLAG = "Y"
  NUMERIC_MODE_ENABLED_FLAG = "N"


  BEGIN VSET_TABLE
    OWNER = "ARAJ"
    LAST_UPDATE_DATE = "2011/11/22 09:14:47"
    TABLE_APPLICATION_SHORT_NAME = "PO"
    APPLICATION_TABLE_NAME = "XXAYE_PO_NUM_SET"
    SUMMARY_ALLOWED_FLAG = "N"
    VALUE_COLUMN_NAME = "PO_DATE"
    VALUE_COLUMN_TYPE = "D"
    VALUE_COLUMN_SIZE = "20"
    ENABLED_COLUMN_NAME = "'Y'"
    COMPILED_ATTRIBUTE_COLUMN_NAME = "NULL"
    HIERARCHY_LEVEL_COLUMN_NAME = "NULL"
    START_DATE_COLUMN_NAME = "to_date(null)"
    END_DATE_COLUMN_NAME = "to_date(null)"
    SUMMARY_COLUMN_NAME = "'N'"
    ADDITIONAL_WHERE_CLAUSE = "WHERE SEGMENT1=:$FLEX$.XXAYE_PONUM_VS"
    ADDITIONAL_QUICKPICK_COLUMNS = "TO_CHAR(PO_DATE)"
  END VSET_TABLE
 





END VALUE_SET

BEGIN DESC_FLEX "XXAYE" "$SRS$.XXAYE_PO_CONFIRMATION"
  OWNER = "ARAJ"
  LAST_UPDATE_DATE = "2011/11/26 21:12:35"
  TABLE_APPLICATION_SHORT_NAME = "FND"
  APPLICATION_TABLE_NAME = "FND_SRS_MASTER"
  CONTEXT_COLUMN_NAME = "STRUCTURE_COLUMN"
  CONTEXT_REQUIRED_FLAG = "N"
  CONTEXT_USER_OVERRIDE_FLAG = "N"
  CONCATENATED_SEGMENT_DELIMITER = ":"
  FREEZE_FLEX_DEFINITION_FLAG = "Y"
  PROTECTED_FLAG = "S"
  CONTEXT_OVERRIDE_VALUE_SET_NAM = ""
  TITLE = "$SRS$.XXAYE_PO_CONFIRMATION"
  FORM_CONTEXT_PROMPT = "Context Value"
  DESCRIPTION = " Sales Order Confirmation"



  BEGIN DFF_CONTEXT "Global Data Elements"
    OWNER = "MEERAM"
    LAST_UPDATE_DATE = "2011/11/21 17:13:53"
    ENABLED_FLAG = "Y"
    GLOBAL_FLAG = "Y"
    DESCRIPTIVE_FLEX_CONTEXT_NAME = "Global Data Elements"
 
    BEGIN DFF_SEGMENT "P_PO_NUM" "ATTRIBUTE1"
      OWNER = "ARAJ"
      LAST_UPDATE_DATE = "2011/11/22 08:54:44"
      COLUMN_SEQ_NUM = "1"
      ENABLED_FLAG = "Y"
      DISPLAY_FLAG = "Y"
      REQUIRED_FLAG = "Y"
      SECURITY_ENABLED_FLAG = "N"
      FLEX_VALUE_SET_NAME = "XXAYE_PONUM_VS"
      DISPLAY_SIZE = "20"
      MAXIMUM_DESCRIPTION_LEN = "50"
      CONCATENATION_DESCRIPTION_LEN = "25"
      SRW_PARAM = "P_PO_NUM"
      FORM_LEFT_PROMPT = "PO No:"
      FORM_ABOVE_PROMPT = "PO No:"
    END DFF_SEGMENT
   
    BEGIN DFF_SEGMENT "P_PO_DATE" "ATTRIBUTE2"
      OWNER = "ARAJ"
      LAST_UPDATE_DATE = "2011/11/22 08:55:31"
      COLUMN_SEQ_NUM = "2"
      ENABLED_FLAG = "Y"
      DISPLAY_FLAG = "Y"
      REQUIRED_FLAG = "N"
      SECURITY_ENABLED_FLAG = "N"
      FLEX_VALUE_SET_NAME = "XXAYE_PODATE_VS"
      DISPLAY_SIZE = "20"
      MAXIMUM_DESCRIPTION_LEN = "50"
      CONCATENATION_DESCRIPTION_LEN = "25"
      SRW_PARAM = "P_PO_DATE"
      FORM_LEFT_PROMPT = "PO Date:"
      FORM_ABOVE_PROMPT = "PO Date:"
    END DFF_SEGMENT
   
  END DFF_CONTEXT
 
END DESC_FLEX

BEGIN PROGRAM "XXAYE_PO_CONFIRMATION" "XXAYE"
  OWNER = "ARAJ"
  LAST_UPDATE_DATE = "2011/11/26"
  USER_CONCURRENT_PROGRAM_NAME =
 "Purchase Order Confirmation Report (OCR)"
  EXEC = "XXAYE_PO_CONFIRMATION" "XXAYE"
  EXECUTION_METHOD_CODE = "P"
  ARGUMENT_METHOD_CODE = "4"
  QUEUE_CONTROL_FLAG = "N"
  QUEUE_METHOD_CODE = "I"
  REQUEST_SET_FLAG = "N"
  ENABLED_FLAG = "Y"
  PRINT_FLAG = "Y"
  RUN_ALONE_FLAG = "N"
  SRS_FLAG = "Y"
  DESCRIPTION = " Sales Order Confirmation"
  SAVE_OUTPUT_FLAG = "Y"
  REQUIRED_STYLE = "N"
  OUTPUT_PRINT_STYLE = "A4"
  OUTPUT_FILE_TYPE = "XML"
  RESTART = "Y"
  NLS_COMPLIANT = "Y"
  MLS_EXEC = "" ""
  ENABLE_TIME_STATISTICS = "N"
  SRS_FLEX = "XXAYE" "$SRS$.XXAYE_PO_CONFIRMATION"
  AUTO_ANNOTATION =
 "/**\n\
  * @param P_PO_NUM PO No:\n\
  * @rep:paraminfo {@rep:type XXAYE_PONUM_VS} {@rep:displayed Y} {@rep:required}\n\
  * @param P_PO_DATE PO Date:\n\
  * @rep:paraminfo {@rep:type XXAYE_PODATE_VS} {@rep:displayed Y}\n\
  */"


END PROGRAM

1)
        KEY   CHILD_FLEX_VALUE_LOW            VARCHAR2(60)
        KEY   CHILD_FLEX_VALUE_HIGH           VARCHAR2(60)
        CTX   OWNER                           VARCHAR2(4000)
        CTX   LAST_UPDATE_DATE                VARCHAR2(50)
        BASE  START_DATE_ACTIVE               VARCHAR2(50)
        BASE  END_DATE_ACTIVE                 VARCHAR2(50)
      END VSET_VALUE_HIERARCHY
  END VSET_VALUE

    DEFINE VSET_QUALIFIER
   XXAYE_RICE_POC_2/XXAYE_PO_CONFIRMATION.rdf                                                   0000644 0000146 0000144 000

FNDLOAD

To load template

FNDLOAD apps/DI6E24BW O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXAYE_SALES_CONFIRMATION.ldt XDO_DS_DEFINITIONS

To load value sets

FNDLOAD apps/DI6E24BW O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXAYE_PO_CONFIRMATION_VALUESET2.ldt VALUE_SET_VALUE


1 - Printer Styles

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLEPRINTER_STYLE_NAME="printer style name"



2 - Lookups

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPEAPPLICATION_SHORT_NAME="FND"

LOOKUP_TYPE="lookup name"



3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lctfile_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALLAPPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="descflex name" P_CONTEXT_CODE="context name"



4 - Key Flexfield Structures

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEXP_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALLAPPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code"P_STRUCTURE_CODE="structure name"



5 - Concurrent Programs

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAMAPPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrentname"



6 - Value Sets

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SETFLEX_VALUE_SET_NAME='value set name'



7 - Value Sets with values

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUEFLEX_VALUE_SET_NAME='value set name'



8 - Profile Options

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILEPROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"



9 - Request Groups

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUPREQUEST_GROUP_NAME="request group"APPLICATION_SHORT_NAME="FND"



10 - Request Sets

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET

APPLICATION_SHORT_NAME="FND"REQUEST_SET_NAME="request set"



11 - Responsibilities

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITYRESP_KEY="responsibility"



12 - Menus

FNDLOAD apps/apps O Y DOWNLOAD$FND_TOP/patch/115/import/afsload.lct file_name.ldt MENUMENU_NAME="menu_name"



13 - Alerts

FNDLOAD apps/apps 0 Y DOWNLOAD$ALR_TOP/patch/115/import/alr.lct <'File Name'>.ldt ALR_ALERTSAPPLICATION_SHORT_NAME=<'APP SHORT NAME'> ALERT_NAME=<'Alert name todownload'>



14 - WEB ADI

FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct<'YOUR LDT FILE NAME'>.ldt BNE_INTEGRATORSINTEGRATOR_ASN="XXALY" INTEGRATOR_CODE="YOUR INTEGRATORNAME"

R12 Forms cmd

Command to compile the form  in R12

frmcmp_batch module=$XXAYE_TOP/forms/US/XXAYE_DMS_DO.fmb userid=username/password@instance output_file=$XXAYE_TOP/forms/US/XXAYE_DMS_DO.fmx module_type=form batch=yes compile_all=special

set path from custom top

> FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$XXAYE_TOP/forms/US

>export FORMS_PATH

Form Commands

For compiling a form in the application,we need to set the forms path

Eg. in 11i

Login in host(unix)

pwd ->to find the path
cd $folder_name->to move to that folder
ls->to list the folders in the current path


set path

$cd $AU_TOP/forms/US

Compile Command

$f60gen module= XXFORM.fmb module_type=form output_file=$XXCUST_TOP/forms/US/XXFORM.fmx userid=apps/apps11emrmd module_type=form batch=yes compile_all=special

Important Tables in PM

PN_LEASES_ALL

PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY

PN_LOCATIONS_ALL

PN_PROPERTIES_ALL

PN_ADDRESSES_ALL

Update HZ_Organization_units Table

CREATE OR REPLACE PROCEDURE XX_UPDATE_BRAND_NAME
AS
   L_PERSON_REC              HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   L_ORGANIZATION_REC        HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
   L_VERSION_NUMBER          NUMBER;
   L_PARTY_ID                NUMBER;
   L_PARTY_TYPE              VARCHAR2(100);
   L_DUNS_NAME               VARCHAR2 (100);
   L_ORIG_SYSTEM_REFERENCE   VARCHAR2 (100);
   X_PARTY_NUMBER            VARCHAR2 (100);
   X_PARTY_ID                NUMBER;
   X_PROFILE_ID              NUMBER;
   X_RETURN_STATUS           VARCHAR2 (1000);
   X_MSG_COUNT               NUMBER;
   X_MSG_DATA                VARCHAR2 (1000);
   L_ERROR_MESSAGE           VARCHAR2 (1000);

   CURSOR P_RECORDS
   IS
      SELECT *
        FROM XX_BRAND_NAME_STAG where comments is null;
       
BEGIN

   FOR P1 IN P_RECORDS
    LOOP
        IF P1.COMMENTS IS NULL THEN
 
        SELECT PARTY_ID, OBJECT_VERSION_NUMBER,PARTY_TYPE
            INTO L_PARTY_ID, L_VERSION_NUMBER,L_PARTY_TYPE
            FROM HZ_PARTIES
            WHERE PARTY_ID = P1.PARTY_ID;

      IF L_PARTY_TYPE = 'ORGANIZATION'
      THEN
         L_ORGANIZATION_REC.ORGANIZATION_NAME := P1.PARTY_NAME;
        -- L_ORGANIZATION_REC.CREATED_BY_MODULE :=  P1.CREATED_BY_MODULE;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_ID :=  P1.PARTY_ID;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_NUMBER := P1.PARTY_NUMBER;
       --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM_REFERENCE :=  P1.ORIG_SYSTEM_REFERENCE;
         --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM           := P_ORIG_SYSTEM;
         L_ORGANIZATION_REC.DUNS_NUMBER_C := P1.BRAND_NAME;

       
            UPDATE APPS.HZ_ORGANIZATION_PROFILES G
               SET DUNS_NUMBER_C =  P1.BRAND_NAME
             WHERE ATTRIBUTE_CATEGORY(+) = 'Organization Information'
               AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE
                   )
               AND PARTY_ID = L_PARTY_ID;
       

         HZ_PARTY_V2PUB.UPDATE_ORGANIZATION
                           (P_INIT_MSG_LIST                    => FND_API.G_TRUE,
                            P_ORGANIZATION_REC                 => L_ORGANIZATION_REC,
                            P_PARTY_OBJECT_VERSION_NUMBER      => L_VERSION_NUMBER,
                            X_PROFILE_ID                       => X_PROFILE_ID,
                            X_RETURN_STATUS                    => X_RETURN_STATUS,
                            X_MSG_COUNT                        => X_MSG_COUNT,
                            X_MSG_DATA                         => X_MSG_DATA
                           );
      -- P_DUNS_NAME =>'URBANOO' );
     
     
     
     END IF;
   UPDATE XX_BRAND_NAME_STAG SET COMMENTS='Y' WHERE PARTY_ID =P1.PARTY_ID;
    COMMIT;
     ELSE
UPDATE XX_BRAND_NAME_STAG SET COMMENTS='N' WHERE PARTY_ID =P1.PARTY_ID;
COMMIT;
   END IF;
   END LOOP;

   FOR I IN 1 .. X_MSG_COUNT
   LOOP
      L_ERROR_MESSAGE :=
            I
         || '. '
         || SUBSTR (FND_MSG_PUB.GET (P_ENCODED => FND_API.G_FALSE), 1, 255);
   --SINA_ERROR('UPDATE_PARTY', P_PARTY_TYPE || P_PARTY_NAME || ' ' || L_ERROR_MESSAGE);
   END LOOP;
 
END XX_UPDATE_BRAND_NAME;

current GL Period

select max(gl_date) from
ra_cust_trx_line_gl_dist_all

To find Responsibilities

-- Query to find the Responsibilities based on Concurrent Request set Name

SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              frsv.user_request_set_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_request_sets_vl frsv
        WHERE fr.request_group_id = frg.request_group_id
          AND fr.responsibility_id = frt.responsibility_id
          AND frg.request_group_id = frgu.request_group_id
          AND frgu.request_unit_id = frsv.request_set_id
          AND frsv.user_request_set_name LIKE 'give user request set name'
          AND frsv.request_set_name LIKE 'request set name';
       

-- Query to find the Responsibilities based on Concurrent Program Name        
     
SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              fcpt.user_concurrent_program_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_concurrent_programs_tl fcpt
        WHERE fr.responsibility_id = frt.responsibility_id
          AND fr.request_group_id = frg.request_group_id
          AND frgu.request_group_id = frg.request_group_id
          AND frgu.request_unit_id = fcpt.concurrent_program_id
          AND fcpt.LANGUAGE = 'US'
          AND frt.LANGUAGE = 'US'
          AND fcpt.user_concurrent_program_name LIKE
                                                'Your Concurrent Program Name'        

AR Tables 11i

Query for Subledger Transfer to GL

SELECT    gjjlv.period_name             "Period Name"
        , gjb.name                      "Batch Name"
        , gjjlv.header_name             "Journal Entry For"
        , gjjlv.je_source               "Source"
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit"
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit"
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
        , gjjlv.currency_code            "Currency"
        , rctype.name                    "Trx type"
        , rcta.trx_number                "Trx Number"
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , TRUNC(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
FROM    apps.GL_JE_JOURNAL_LINES_V gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ra_customer_trx_all rcta
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ra_cust_trx_types_all rctype
WHERE     gjh.period_name IN ('OCT-2008','NOV-2008')
AND       glcc.code_combination_id = gje.code_combination_id
AND       gjh.je_batch_id = gjb.je_batch_id
AND       gjh.je_header_id = gje.je_header_id
AND       gjh.period_name = gjb.default_period_name
AND       gjh.period_name = gje.period_name
AND       gjjlv.period_name = gjh.period_name
AND       gjjlv.je_batch_id = gjh.je_batch_id
AND       gjjlv.je_header_id = gjh.je_header_id
AND       gjjlv.line_je_line_num  = gje.je_line_num
AND       gjjlv.line_code_combination_id = glcc.code_combination_id
AND       gjjlv.line_reference_4 = rcta.trx_number
AND       rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND       rcta.org_id = rctype.org_id
AND       ra.customer_id = rcta.bill_to_customer_id

AR Useful Query

Query to fetch summary data*/

SELECT hc.cust_account_id          
      ,min(hc.account_number)         customer_number
      ,sum(amount_due_remaining)      amount_due_remaining
      ,sum(aps.amount_due_original)   amount_due_original
      ,count(aps.payment_schedule_id) open_invoices
  FROM ra_customer_trx_all       ra,
       ra_customer_trx_lines_all rl,
       ar_payment_schedules_all  aps,
       ra_cust_trx_types_all     rt,
       hz_cust_accounts          hc,
       hz_parties                hp,
       hz_cust_acct_sites_all    hcasa_bill,
       hz_cust_site_uses_all     hcsua_bill,
       hz_party_sites            hps_bill,
       ra_cust_trx_line_gl_dist_all rct
 WHERE ra.customer_trx_id           = rl.customer_trx_id
   AND ra.customer_trx_id           = aps.customer_trx_id
   AND ra.org_id                    = aps.org_id
   AND rct.customer_trx_id          = aps.customer_trx_id
   AND rct.customer_trx_id          = ra.customer_trx_id
   AND rct.customer_trx_id          = rl.customer_trx_id
   AND rct.customer_trx_line_id     = rl.customer_trx_line_id
   AND ra.complete_flag             = 'Y'
   AND rl.line_type                 IN ('FREIGHT', 'LINE')
   AND ra.cust_trx_type_id          = rt.cust_trx_type_id
   AND ra.bill_to_customer_id       = hc.cust_account_id
   AND hc.status                    = 'A'
   AND hp.party_id                  = hc.party_id
   AND hcasa_bill.cust_account_id   = ra.bill_to_customer_id
   AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
   AND hcsua_bill.site_use_code     = 'BILL_TO'
   AND hcsua_bill.site_use_id       = ra.bill_to_site_use_id
   AND hps_bill.party_site_id       = hcasa_bill.party_site_id
   AND hcasa_bill.status            = 'A'
   AND hcsua_bill.status            = 'A'
   AND aps.amount_due_remaining     <> 0
   AND aps.status                   = 'OP'
   GROUP by hc.cust_account_id;

AR Useful Query

Sharing few queries Which i have came across while browsing :-


TRACE WHICH AR INVOICE IS LINKED WITH WHICH GL ENTRY.

Linking Table between AR to GL :

In R12, try using the query below:

SELECT b.NAME batch_name,
b.description batch_description,
h.je_category,
h.je_source,
h.period_name je_period_name,
h.NAME journal_name,
h.status journal_status,
h.description je_description,
l.je_line_num line_number
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
ra_customer_trx_all rct,
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =''





11i

select gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, trunc(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
from apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
where /*gjh.period_name IN ('OCT-2008','NOV-2008')
and */glcc.code_combination_id = gje.code_combination_id
and gjh.je_batch_id = gjb.je_batch_id
and gjh.je_header_id = gje.je_header_id
and gjh.period_name = gjb.default_period_name
and gjh.period_name = gje.period_name
and gjjlv.period_name = gjh.period_name
and gjjlv.je_batch_id = gjh.je_batch_id
and gjjlv.je_header_id = gjh.je_header_id
and gjjlv.line_je_line_num = gje.je_line_num
and gjjlv.line_code_combination_id = glcc.code_combination_id
and gjjlv.line_reference_4 = rcta.trx_number
and rcta.cust_trx_type_id = rctype.cust_trx_type_id
and rcta.org_id = rctype.org_id
and ra.customer_id = rcta.bill_to_customer_id

Happy Reading.....................

AR Tables 11i

RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLES_TRX_ALL
AR_RECEIVABLE_APPLICATIONS_ALL


transaction types stored in RA_CUST_TRX_TYPES_ALL

RA_CUSTOMER_TRX_ALL
--------------------------


The primary key for this table is CUSTOMER_TRX_ID.

-If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID
 stores the customer transaction identifier of the invoice you credited,otherwise it is null.

-invoice against a commitment  INITIAL_CUSTOMER_TRX_ID,otherwise it is null.

-COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No




RA_CUSTOMER_TRX_LINES_ALL
---------------------------------


-The primary key for this table is CUSTOMER_TRX_LINE_ID
-QUANTITY_ORDERED stores the amount of product ordered
-QUANTITY_INVOICED stores the amount of product invoiced

**For invoices entered through the window, QUANTITY_ORDERED and QUANTITY_INVOICED must be the same.
  For invoices imported through AutoInvoice, QUANTITY_ORDERED and QUANTITY_INVOICED can be different**

-If you enter a credit memo, QUANTITY_CREDITED stores the amount of product credited
-UOM_CODE stores the unit of measure code as defined in MTL_UNITS_OF_MEASURE
-UNIT_STANDARD_PRICE stores the list price per unit for this transaction line.
 UNIT_SELLING_PRICE stores the selling price per unit for this transaction line.
 For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE can be different.
-**LINE_TYPE differentiates between the different types of lines that are stored in this table.
LINE points to regular invoice lines that normally refer to an item. **

--For every row in this table that belongs to a complete transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y),
  there must be at least one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting information),
  even for non–postable transactions.


RA_CUST_TRX_LINE_GL_DIST_ALL
----------------------------------------------

The primary key for this table is CUST_TRX_LINE_GL_DIST_ID.

ACCOUNT_SET_FLAG is Y if this row is part of an account set

AR_PAYMENT_SCHEDULES_ALL
-----------------------------------------------

Oracle Receivables groups different transactions bythe column CLASS.
These classes include invoice (INV),
debit memos(DM), guarantees (GUAR),
credit memos (CM), deposits (DEP),
chargebacks (CB), and receipts (PMT).

The primary key for this table is PAYMENT_SCHEDULE_ID

--AR_PAYMENT_SCHEDULES_ALL joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries
such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits.

--AR_PAYMENT_SCHEDULES_ALL uses the foreign key CUSTOMER_TRX_ID to join to the RA_CUSTOMER_TRX_ALL
table for these transactions.

--AR_PAYMENT_SCHEDULES_ALL joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions using the
foreign key CASH_RECEIPT_ID.

--When a receiptis applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS and AMOUNT_DUE_REMAINING.

--STATUS changes from ’OP’ to ’CL’for any transaction that has an AMOUNT_DUE_REMAINING value of 0(Zero)

--The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. If this amount is negative, then this column indicates the credit balance amount currently available for this customer.

--For invoices with split terms, one record is created in RA_CUSTOMER_TRX_ALL and one record is stored in AR_PAYMENT_SCHEDULES_ALL for each installment.

--In AR_PAYMENT_SCHEDULES_ALL, DUE_DATE and AMOUNT_DUE_REMAINING can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column.



NOTE:
If you create a debit memo reversal when you reverse a receipt, Oracle Receivables creates a new payment schedule record for the
debit memo and fills in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was reversed.
Oracle Receivables creates a new payment schedule record when you create a chargeback in the Receipts window.
ASSOCIATED_CASH_RECEIPT_ID is the cash receipt of the payment you entered when you created the chargeback in this window.
GL_DATE_CLOSED indicates the general ledger date on which your transaction was closed.

AR_RECEIVABLES_TRX_ALL
------------------------------

Adjustment, Miscellaneous Cash, and Finance Charge

The primary key for this table is RECEIVABLES_TRX_ID.

AR_RECEIVABLE_APPLICATIONS_ALL
-----------------------------------


Possible statuses of your applications include APP, UNAPP, ACC, and UNID

There are two kinds of applications: CASH and CM (for credit memo applications). This is stored in the column APPLICATION_TYPE.

Salary Sum Query

Sum of all earnings in payroll:

/* Formatted on 2011/10/27 13:36 (Formatter Plus v4.8.8) */

SELECT pee.assignment_id, pee.element_entry_id, pee.element_link_id,
pel.element_type_id, pet.element_name, pettl.reporting_name,
DECODE (pet.element_name,
'Basic_New', 'Basic Salary',
'HRA', 'House Rent Allowance',
pet.element_name
) display_name,
(SELECT MAX (screen_entry_value)
FROM pay_element_entry_values_f
WHERE element_entry_id = pee.element_entry_id) screen_entry_value
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_element_classifications pec
WHERE pee.assignment_id = :assignment_id
AND TRUNC (SYSDATE) BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND TRUNC (SYSDATE) BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pel.element_type_id = pet.element_type_id
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.processing_type = 'R'
AND pet.element_type_id = pettl.element_type_id
AND TRUNC (SYSDATE) BETWEEN peev.effective_start_date
AND peev.effective_end_date*/
AND pet.classification_id = pec.classification_id
AND pec.classification_name = 'Earnings'
ORDER BY DECODE (pet.element_name, 'Basic_New', 1, 'HRA', 2, 3)

FOrmsPersonalization:To restrict a receipt method LOV for a responsibility

In Order Management Responsibility go to quick sales order menu

• create a sales order
• click on actions
• Choose payments
• form main menu go to help-diagnostics-custom code-personalize

1.Add a Sequence with description,

 

 

Under Condition ;
trigger event ; when_new_item_instance
Trigger_object ; blockname.filed-name(In which u need the personalization)
Condition:optional,can give any default values e.g:
Block_name.field_name=’Value’

 

e.g: WHEN-NEW-ITEM-INSTANCE;
OE_PAYMENTS.RECEIPT_METHOD:OE_PAYMENTS.PAYMENT_TYPE_CODE='CASH'
Under Level Choose the responsibility that you want to assign.

 

 

 

Now go to actions tab

 

2.The first sequence is built-in and the second sequence is property for a LOV
e.g:Seq 10
type;Builtin
In Builtin Type Choose;Create record group for query
Argument;Enter your query
NOTE:
1. If u r changing the existing record group the you need to check the exiting record group query and develop your query by choosing same columns and with your own condition appropriately.
2. U need to refer the the LOV that u r Personalizing.
Group Name;Give a custom name
E.g. query
**-For type Cash**
SELECT rm.NAME,rm.receipt_method_id
/*Have choosen the same columns as per the default LOV Record group query and changed the where clause as per my requirement*/
FROM
ar_receipt_methods rm,
oe_payments b,
ar_receipt_method_accounts_all rma,
fnd_lookup_values flv,
ce_bank_acct_uses_all cba
where
rm.receipt_method_id=b.receipt_method_id
AND rm.receipt_method_id = rma.receipt_method_id
AND flv.lookup_type like '%PAYMENT_METHODS%' --Existing LOV Name--
AND rma.remit_bank_acct_use_id = cba.bank_acct_use_id and
rma.org_id = cba.org_id
AND flv.enabled_flag = 'Y'
AND Flv.LOOKUP_CODE not IN('CCR')--Condition for Where Clause--
AND rm.receipt_method_id IN('2012')
GROUP BY rm.NAME,rm.receipt_method_id

 

3.Create property for the built-in type

 

My desp; Restrict Receipt Method for cash
Object_type;Cash

 

Target_Object;Payment_Methods
Property_name;Group Name
Value;Give the custom name that u have given for the Builtin Group Name

 

And ITS DONE !!!!

 

 

 

 

 

FND_CANONICAL

In HRMS we do use date with timestamp for EIT ans SIT segments.

To change the format od the date we can use fnd_date.canonical_to_date default function:

e.g:

before using fnd_date function

SELECT pei_information2 Visit_date from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

After using fnd_date function:

SELECT fnd_date.canonical_to_date(pei_information2) from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 And you can use to_chat over uit to make it in words as

SELECT TO_CHAR(fnd_date.canonical_to_date(pei_information2),'Month ddTH, YYYY') from per_people_extra_info where pei_attribute_category = '02' and person_id=1101