Entries by Oracle ERP Apps Guide

, ,

EXECUTE IMMEDIATE

Note:- This type of Dynamic SQL would not work in the 10.7 version Database.For the Select statementNote:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.Example:-———-DECLAREL_DEPTNO NUMBER DEFAULT 10;L_SAL NUMBER; BEGIN EXECUTE IMMEDIATE ‘select max(sal) from emp where deptno = :l_deptno’INTO L_SALUSING L_DEPTNO;DBMS_OUTPUT.PUT_LINE(L_SAL);END;For the Insert statementNote:- I have created […]

/by
, ,

Introduction of DYNAMIC SQL

If we have to create some PROCEDURE, FUNCTION or PACKAGE and run them, then we have to do two steps.1) Compile the code.2) Execute the code which you have compiled.Errors can occur at any above steps.At the compile Time (Step 1), system will check for the syntax of the code and also if all the […]

/by
, ,

DBMS_SQL

For the Select statementNote:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.Example:-DECLAREL_DEPTNO NUMBER DEFAULT 10;L_SAL NUMBER;L_SQL VARCHAR2( 3000 );L_CUR NUMBER;L_RC NUMBER;BEGIN— Code converted in dynamic SQL start(phani).L_SQL := ‘select max(sal) from emp where deptno = :l_deptno’;L_CUR := dbms_sql.OPEN_CURSOR;dbms_sql.PARSE( L_CUR, L_SQL, dbms_sql.NATIVE );dbms_sql.BIND_VARIABLE( L_CUR, ‘:l_deptno’, L_DEPTNO […]

/by
,

API – Customer Additional Information (Price List , Payment Terms ,Sales Type )

CREATE OR REPLACE procedure APPS.XX_CUSTOMER_ADDITIONAL_LOADISp_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;x_return_status VARCHAR2(2000);x_msg_count NUMBER;xio_p_object_version NUMBER;x_msg_data VARCHAR2(2000);v_site_use_id NUMBER;V_OBJECT_VERSION NUMBER;BEGIN– FND_GLOBAL.APPS_INITIALIZE(<user_id>,<resp_id>,<resp_applicarion_id>);– MO_GLOBAL.INIT(‘AR’);– MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, <org_id>); FND_GLOBAL.APPS_INITIALIZE(1535,50930,222); MO_GLOBAL.INIT(‘AR’); MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, 1210); BEGIN SELECT HCSU.SITE_USE_ID, HCSU.OBJECT_VERSION_NUMBER INTO V_SITE_USE_ID, V_OBJECT_VERSION FROM HZ_PARTIES HP, HZ_PARTY_SITES HPS, HZ_CUST_ACCT_SITES_ALL HCAS, HZ_CUST_SITE_USES_ALL HCSU WHERE HP.PARTY_ID = HPS.PARTY_ID AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID AND HCAS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID AND HCSU.SITE_USE_CODE = ‘BILL_TO’ AND ltrim(rtrim(upper(HP.PARTY_NAME)))= ltrim(rtrim(upper(‘SSE Test […]

/by