PL/SQL SYNTAXES

                       *********************** Package Syntax ***********************

 

*********************** PACKAGE SPECIFICATION ***********************

 

Create Or Replace  PACKAGE XX_TEST_PKG 

AS

/**********************************************************************************

 Object Name : APPS.XX_TEST_PKG

 Purpose :

 Created By : Company Name 

 Created On : June 25,2023

 Package Change History :

 

 Ver        Date                Author

 1.0        June 25,2023             developer name 

 

***********************************************************************************/

 

 

 

            PROCEDURE log_message(P_PROCEDURE VARCHAR2,P_MESSAGE VARCHAR2);  

 

END XX_TEST_PKG;

 

 

 

*********************** PACKAGE BODY ***********************

 

Create Or Replace  PACKAGE BODY XX_TEST_PKG

AS

/**********************************************************************************

 Object Name : APPS.XX_TEST_PKG

 Purpose :

 Created By : Company Name 

 Created On : June 25,2023

 Package Change History :

 

 Ver        Date                Author

 1.0        June 25,2023             developer name 

 

***********************************************************************************/

--

 

  -- Global Variable --

    g_user_id       NUMBER := apps.fnd_profile.VALUE ('USER_ID');

    g_resp_id       NUMBER := apps.fnd_profile.VALUE ('RESP_ID');

    g_resp_appl_id  NUMBER := apps.fnd_profile.VALUE ('RESP_APPL_ID');

    g_org_id        NUMBER := apps.fnd_profile.VALUE ('ORG_ID');

 

 

             PROCEDURE log_message (

        p_procedure   VARCHAR2,

        p_message     VARCHAR2

    ) IS

 

            l_err_msg  varchar2(2000);

 

    BEGIN

           

   FND_GLOBAL.APPS_INITIALIZE(USER_ID=>0,RESP_ID=>50757,RESP_APPL_ID=>401);

 

            l_err_msg:='<<  '|| p_procedure

                             || '  -  '

                             || p_message

                             || '  >>'

                                                                                     ;

 

        dbms_output.put_line(l_err_msg);

 

                        fnd_file.put_line (fnd_file.log, l_err_msg);

 

    END log_message;

 

END XX_TEST_PKG;

 

*********************** PROCEDURE ***********************

 

CREATE [OR REPLACE] PROCEDURE procedure_name 

[(parameter_name [IN | OUT | IN OUT] type [, ...])] 

{IS | AS} 

BEGIN 

  < procedure_body > 

END procedure_name;

 

 

*********************** FUNCTION ***********************

 

CREATE [OR REPLACE] FUNCTION function_name 

[(parameter_name [IN | OUT | IN OUT] type [, ...])] 

RETURN return_datatype 

{IS | AS} 

BEGIN 

   < function_body > 

END [function_name];

 

*********************** TRIGGER ***********************

 

CREATE [OR REPLACE ] TRIGGER trigger_name  

{BEFORE | AFTER | INSTEAD OF }  

{INSERT [OR] | UPDATE [OR] | DELETE}  

[OF col_name]  

ON table_name  

[REFERENCING OLD AS o NEW AS n]  

[FOR EACH ROW]  

WHEN (condition)   

DECLARE 

   Declaration-statements 

BEGIN  

   Executable-statements 

EXCEPTION 

   Exception-handling-statements 

END;