Oracle Application: May 2020

Wednesday 20 May 2020

How to pass multiple value in parameter with comma separator without using from and to parameter range logic. – Oracle Fusion

Requirement: User want to run the report for specific comma separator list of multiple values, User does not want use from and to range parameter logic.

Example: Parameter Value (Seq_id) : 123,345,456

Solution:

At the time of defining a parameter, Please select checkbox – Text Field Contains Comma- Separated value.

Try to run the report and pass parameter like 123,345,678, Report will run normally as expected.

Oracle API - How to Retire a Fixed Asset

/**-------Call Asset Retirement API-------**/

CREATE OR REPLACE PROCEDURE API_CALL_ASSET_RETIRE

API_ERROR EXCEPTION;

/*TEST ASSET INFO */

L_ASSET_ID NUMBER;

L_BOOK_TYPE_CODE VARCHAR2 (15) := 'MWANI_CORP_BOOK';

L_USER_ID NUMBER := 0;

L_COST_RETIRED NUMBER;

L_PROCEEDS_OF_SALE NUMBER := 0;

L_COST_OF_REMOVAL NUMBER := 0;

V_REQUEST_ID NUMBER;

-- USER_ID MUST PROPERLY BE SET TO RUN CALC GAIN/LOSS

/* DEFINE LOCAL RECORD TYPES */

L_TRANS_REC FA_API_TYPES.TRANS_REC_TYPE;

L_DIST_TRANS_REC FA_API_TYPES.TRANS_REC_TYPE;

L_ASSET_HDR_REC FA_API_TYPES.ASSET_HDR_REC_TYPE;

L_ASSET_RETIRE_REC FA_API_TYPES.ASSET_RETIRE_REC_TYPE;

L_ASSET_DIST_TBL FA_API_TYPES.ASSET_DIST_TBL_TYPE;

L_SUBCOMP_TBL FA_API_TYPES.SUBCOMP_TBL_TYPE;

L_INV_TBL FA_API_TYPES.INV_TBL_TYPE;

/* MISC INFO */

L_API_VERSION NUMBER := 1;

L_INIT_MSG_LIST VARCHAR2 (1) := FND_API.G_FALSE;

L_COMMIT VARCHAR2 (1) := FND_API.G_TRUE;

L_VALIDATION_LEVEL NUMBER := FND_API.G_VALID_LEVEL_FULL;

L_CALLING_FN VARCHAR2 (80) := 'RETIREMENT ASET WRAPPER';

L_RETURN_STATUS VARCHAR2 (1) := FND_API.G_FALSE;

L_MSG_COUNT NUMBER := 0;

L_MSG_DATA VARCHAR2 (512);

L_COUNT NUMBER;

L_REQUEST_ID NUMBER;

I NUMBER := 0;

TEMP_STR VARCHAR2 (512);

MESG_COUNT NUMBER;

P_SESSION_ID NUMBER;

P_REQUESTER_ID NUMBER;

L_DATE_RETIRED DATE;

CURSOR CUR_FA_ADDITION

SELECT C.SEQ_ID,

A.ASSET_NUMBER,

A.ASSET_ID,

B.COST,

C.DATE_RETIRED,

C.PROCEEDS_OF_SALE,

C.COST_OF_REMOVAL,

C.REQUESTER_ID

FROM FA_ADDITIONS_B A, FA_BOOKS_V B, XX_ASSET_RET_STG_TBL C

WHERE A.ASSET_ID = B.ASSET_ID

AND A.ASSET_NUMBER = TO_CHAR(C.ASSET_NUMBER)

AND NVL (C.X_STATUS, 'N') IN ('N', 'E')

AND C.SEQ_ID IS NOT NULL

ORDER BY C.SEQ_ID;

BEGIN

FND_GLOBAL.APPS_INITIALIZE (USER_ID => fnd_profile.VALUE('USER_ID'),

RESP_ID => fnd_profile.VALUE('RESP_ID'),

RESP_APPL_ID => fnd_profile.VALUE('RESP_APPL_ID'));

-- insert into xx_temp_tbl2 --column(ASSET_NO,BOOK_TYPE,X_STATUS,X_MESSAGE,P_ERRBUF,P_RETCODE,RET_DATE)

--values('API CALL',NULL,NULL,NULL,NULL,NULL,SYSDATE);

--commit;

FOR I IN CUR_FA_ADDITION

LOOP

L_ASSET_ID := I.ASSET_ID;

L_COST_RETIRED := I.COST;

L_DATE_RETIRED := I.DATE_RETIRED;

DBMS_OUTPUT.DISABLE;

DBMS_OUTPUT.ENABLE (1000000);

DBMS_OUTPUT.PUT_LINE ('BEGIN');

FA_SRVR_MSG.INIT_SERVER_MESSAGE;

FA_DEBUG_PKG.SET_DEBUG_FLAG (DEBUG_FLAG => 'YES');

-- GET STANDARD WHO INFO

L_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;

FND_PROFILE.GET ('LOGIN_ID', L_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN);

FND_PROFILE.GET ('USER_ID', L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY);

IF (L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY IS NULL)

THEN

L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY := -1;

END IF;

IF (L_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN IS NULL)

THEN

L_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN := -1;

END IF;

L_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE := SYSDATE;

L_TRANS_REC.WHO_INFO.CREATION_DATE :=

L_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE;

L_TRANS_REC.WHO_INFO.CREATED_BY :=

L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY;

L_TRANS_REC.TRANSACTION_TYPE_CODE := NULL;

-- THIS WILL BE DETERMINED INSIDE API

L_TRANS_REC.TRANSACTION_DATE_ENTERED := NULL;

L_ASSET_HDR_REC.ASSET_ID := L_ASSET_ID;

L_ASSET_HDR_REC.BOOK_TYPE_CODE := L_BOOK_TYPE_CODE;

L_ASSET_HDR_REC.PERIOD_OF_ADDITION := NULL;

L_ASSET_RETIRE_REC.RETIREMENT_PRORATE_CONVENTION := NULL;

L_ASSET_RETIRE_REC.DATE_RETIRED := L_DATE_RETIRED; --NULL;

-- WILL BE CURRENT PERIOD BY DEFAULT

L_ASSET_RETIRE_REC.UNITS_RETIRED := NULL;

L_ASSET_RETIRE_REC.COST_RETIRED := L_COST_RETIRED;

L_ASSET_RETIRE_REC.PROCEEDS_OF_SALE := I.PROCEEDS_OF_SALE; -- L_PROCEEDS_OF_SALE;

L_ASSET_RETIRE_REC.COST_OF_REMOVAL := I.COST_OF_REMOVAL; -- L_COST_OF_REMOVAL;

L_ASSET_RETIRE_REC.RETIREMENT_TYPE_CODE := 'SALE';

L_ASSET_RETIRE_REC.TRADE_IN_ASSET_ID := NULL;

L_ASSET_RETIRE_REC.CALCULATE_GAIN_LOSS := FND_API.G_FALSE;

FND_PROFILE.PUT ('USER_ID', L_USER_ID);

L_ASSET_DIST_TBL.DELETE;

FA_RETIREMENT_PUB.DO_RETIREMENT (

P_API_VERSION => L_API_VERSION,

P_INIT_MSG_LIST => L_INIT_MSG_LIST,

P_COMMIT => L_COMMIT,

P_VALIDATION_LEVEL => L_VALIDATION_LEVEL,

P_CALLING_FN => L_CALLING_FN,

X_RETURN_STATUS => L_RETURN_STATUS,

X_MSG_COUNT => L_MSG_COUNT,

X_MSG_DATA => L_MSG_DATA,

PX_TRANS_REC => L_TRANS_REC,

PX_DIST_TRANS_REC => L_DIST_TRANS_REC,

PX_ASSET_HDR_REC => L_ASSET_HDR_REC,

PX_ASSET_RETIRE_REC => L_ASSET_RETIRE_REC,

P_ASSET_DIST_TBL => L_ASSET_DIST_TBL,

P_SUBCOMP_TBL => L_SUBCOMP_TBL,

P_INV_TBL => L_INV_TBL);

IF L_RETURN_STATUS = FND_API.G_FALSE

THEN

UPDATE XX_ASSET_RET_STG_TBL

SET X_STATUS = 'E', X_MESSAGE = L_MSG_DATA

WHERE ASSET_NUMBER = I.ASSET_NUMBER;

COMMIT;

RAISE API_ERROR;

END IF;

DBMS_OUTPUT.PUT_LINE (

'ASSET RETIREMENT DONE: RETIREMENT_ID: '

|| L_ASSET_RETIRE_REC.RETIREMENT_ID);

UPDATE XX_ASSET_RET_STG_TBL

SET X_STATUS = 'R', X_MESSAGE = 'ASSET RETIRED SUCCESSFULLY'

WHERE ASSET_NUMBER = I.ASSET_NUMBER;

COMMIT;

-- DUMP DEBUG MESSAGES WHEN RUN IN DEBUG MODE TO LOG FILE

IF (FA_DEBUG_PKG.PRINT_DEBUG)

THEN

FA_DEBUG_PKG.WRITE_DEBUG_LOG;

END IF;

FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => L_CALLING_FN,

NAME => 'FA_SHARED_END_SUCCESS',

TOKEN1 => 'PROGRAM',

VALUE1 => 'RETIREMENT_API');

MESG_COUNT := FND_MSG_PUB.COUNT_MSG;

IF (MESG_COUNT > 0)

THEN

TEMP_STR := FND_MSG_PUB.GET (FND_MSG_PUB.G_FIRST, FND_API.G_FALSE);

DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);

FOR I IN 1 .. (MESG_COUNT - 1)

LOOP

TEMP_STR :=

FND_MSG_PUB.GET (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);

DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);

END LOOP;

ELSE

DBMS_OUTPUT.PUT_LINE ('DUMP: NO MESSAGE !');

END IF;

END LOOP;

EXCEPTION

WHEN API_ERROR

THEN

ROLLBACK WORK;

FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => L_CALLING_FN,

NAME => 'FA_SHARED_PROGRAM_FAILED',

TOKEN1 => 'PROGRAM',

VALUE1 => L_CALLING_FN);

MESG_COUNT := FND_MSG_PUB.COUNT_MSG;

-- P_ERRBUF := SQLCODE || '-' || SQLERRM;

-- P_RETCODE := 2;

IF (MESG_COUNT > 0)

THEN

TEMP_STR := FND_MSG_PUB.GET (FND_MSG_PUB.G_FIRST, FND_API.G_FALSE);

DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);

FOR I IN 1 .. (MESG_COUNT - 1)

LOOP

TEMP_STR :=

FND_MSG_PUB.GET (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);

DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);

END LOOP;

ELSE

DBMS_OUTPUT.PUT_LINE ('DUMP: NO MESSAGE !');

END IF;

COMMIT;

BEGIN

FND_GLOBAL.APPS_INITIALIZE (USER_ID => fnd_profile.VALUE('USER_ID'),

RESP_ID => fnd_profile.VALUE('RESP_ID'),

RESP_APPL_ID => fnd_profile.VALUE('RESP_APPL_ID'));

V_REQUEST_ID :=

FND_REQUEST.SUBMIT_REQUEST ('OFA',

'FARET',

NULL,

FALSE,

L_BOOK_TYPE_CODE);

COMMIT;

DBMS_OUTPUT.PUT_LINE (V_REQUEST_ID);

END;

END ;

Oracle API - How to Apply Cash Receipt against Standard AR Invoice

/*Following Procedure Used For Applied Receipt against Created AR Invoice Transaction*/

CREATE OR REPLACE PROCEDURE xx_apply_receipt_to_invice (p_cust_trx_id IN NUMBER,

p_cash_receipt_id IN NUMBER,

p_process_type IN VARCHAR2)

--local Variables

g_loc NUMBER := 0;

g_msg VARCHAR2 (500);

g_sysdate DATE := SYSDATE;

l_apl_return_status VARCHAR2 (1);

l_apl_msg_count NUMBER;

l_apl_msg_data VARCHAR2 (240);

l_org_id NUMBER;

l_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;

-- l_rcpt_method_name VARCHAR2 (20);

l_customer_trx_id NUMBER;

l_amount_applied NUMBER;

l_data_txt1 VARCHAR2 (1000) := NULL;

l_error_description1 VARCHAR2 (2000) := NULL;

l_msg_index_num1 NUMBER := 1;

l_resp_id NUMBER := NULL;

l_resp_appl_id NUMBER := NULL;

-- l_org_id NUMBER;

l_user_id NUMBER := NULL;

BEGIN

l_receipt_id := p_cash_receipt_id;

l_customer_trx_id := p_cust_trx_id;

l_msg_index_num1 := NULL;

SELECT CREATED_BY

INTO l_user_id

FROM ar_cash_receipts_all

WHERE cash_receipt_id = l_receipt_id;

SELECT amount

INTO l_amount_applied

FROM ar_cash_receipts_all

WHERE cash_receipt_id = l_receipt_id;

SELECT org_id

INTO l_org_id

FROM ra_customer_trx_all

WHERE customer_trx_id = p_cust_trx_id;

/*To get the initialization attribute values */

l_resp_id := XX_GET_RESP_DTLS (l_org_id);

l_resp_appl_id := XX_GET_APPL_DTLS (l_org_id);

BEGIN

DBMS_OUTPUT.put_line ('Initiaiizing Session ');

mo_global.init ('AR');

mo_global.set_policy_context ('S', l_org_id);

fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);

EXCEPTION

WHEN OTHERS

THEN

l_apl_msg_data := 'Session Initialization failed';

-- l_flag := 'N';

END;

AR_RECEIPT_API_PUB.APPLY (

p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_TRUE,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => l_apl_return_status,

x_msg_count => l_apl_msg_count,

x_msg_data => l_apl_msg_data,

p_cash_receipt_id => l_receipt_id,

p_customer_trx_id => l_customer_trx_id);

COMMIT;

IF l_apl_msg_count = 1

THEN

DBMS_OUTPUT.put_line ('l_apl_msg_data ' || l_apl_msg_data);

ELSIF l_apl_msg_count > 1

THEN

LOOP

l_apl_msg_data :=

fnd_msg_pub.GET (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_apl_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (

'Message' || '---' || ' ---' || l_apl_msg_data);

END LOOP;

END IF;

IF l_apl_return_status = 'S'

THEN

DBMS_OUTPUT.put_line (

'Receipt applied successfully for customer trx id '

|| l_customer_trx_id);

/*As per Zohaib requested Custom Table Not Required we remove the Custom Table Logic 17MAY2018*/

-- UPDATE xx_ar_invoice_recipt_mapping

-- SET RECEIPT_APPLIED_FLAG = 'Y',

-- ERROR_MSG = 'Receipt Applied Succussfully'

-- WHERE customer_trx_id = p_cust_trx_id;

IF p_process_type IN ('REGISTRATION', 'RENEW')

THEN

UPDATE xxag_pos_edirham_reg_ren_t

SET RECEIPT_APPLIED_FLAG = 'Y',

ar_payment_status = 'Y',

ERROR_MSG = 'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

-- COMMIT;

ELSIF p_process_type = 'TENDER'

THEN

UPDATE xxag_pon_edirham_rfq_pay_t

SET RECEIPT_APPLIED_FLAG = 'Y',

ar_payment_status = 'Y',

ERROR_MSG = 'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

-- COMMIT;

END IF;

COMMIT;

DBMS_OUTPUT.put_line ('Receipt Applied Succussfully!!');

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Receipt Applied Succussfully!!');

ELSE

FOR i IN 1 .. l_apl_msg_count

LOOP

FND_MSG_PUB.GET (p_msg_index => i,

p_encoded => 'F',

p_data => l_data_txt1,

p_msg_index_out => l_msg_index_num1);

l_error_description1 :=

SUBSTR (l_error_description1 || l_data_txt1, 1, 400);

DBMS_OUTPUT.put_line (l_error_description1);

END LOOP;

l_error_description1 :=

'Error in Apply Receipt' || l_error_description1;

/*As per Zohaib requested Custom Table Not Required we remove the Custom Table Logic 17MAY2018*/

-- UPDATE xx_ar_invoice_recipt_mapping

-- SET RECEIPT_APPLIED_FLAG = 'N', ERROR_MSG = l_error_description1

-- WHERE customer_trx_id = p_cust_trx_id;

IF p_process_type IN ('REGISTRATION', 'RENEW')

THEN

UPDATE xxag_pos_edirham_reg_ren_t

SET RECEIPT_APPLIED_FLAG = 'N',

ERROR_MSG = l_error_description1 --'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

ELSIF p_process_type = 'TENDER'

THEN

UPDATE xxag_pon_edirham_rfq_pay_t

SET RECEIPT_APPLIED_FLAG = 'N',

ERROR_MSG = l_error_description1 --- 'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

END IF;

COMMIT;

DBMS_OUTPUT.put_line (

'Apply Receipt Failed for customer trx id '

|| l_customer_trx_id

|| ' - '

|| l_error_description1);

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'Apply Receipt Failed for customer trx id '

|| l_customer_trx_id

|| ' - '

|| l_error_description1);

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'---------------------------------------------------------------------------------------');

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'---------------------------------------------------------------------------------------');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error in procedure ' || SQLERRM);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error in procedure ');

END;

/*---------END of Procedure xx_apply_receipt_to_invice -------*/

Oracle API - How to Create Credit Memo & Apply it against Standard AR Invoice

CREATE OR REPLACE PROCEDURE xx_create_cm_inv (p_cust_trx_id IN NUMBER)

-- This script was tested in 11i instance --

v_return_status VARCHAR2(1);

p_count NUMBER;

v_msg_count NUMBER;

v_msg_data VARCHAR2(2000);

v_request_id NUMBER;

v_context VARCHAR2(2);

l_cm_lines_tbl arw_cmreq_cover.cm_line_tbl_type_cover;

l_customer_trx_id NUMBER;

cm_trx_id NUMBER;

v_interface_header_rec arw_cmreq_cover.pq_interface_rec_type;

ind NUMBER:=0;

l_trx_number VARCHAR2(30);

CURSOR c_inv(p_cust_trx_id NUMBER)

SELECT rct.trx_number

, rct.customer_trx_id

, rctl.customer_trx_line_id

, rctl.quantity_invoiced

, unit_selling_price

FROM ra_customer_trx_all rct, ra_customer_trX_lines_all rctl

WHERE rct.customer_trx_id = rctl.customer_trx_id

-- AND trx_number = p_trx_number

AND rct.customer_trx_id=p_cust_trx_id

AND line_type = 'LINE';

-- PROCEDURE set_context

-- IS

-- BEGIN

-- DBMS_APPLICATION_INFO.set_client_info(0);

-- MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);

-- END set_context;

BEGIN

-- Setting the context ----

-- set_context;

-- fnd_global.apps_initialize (user_id => 0,

-- resp_id => v_resp_id,

-- resp_appl_id => 222

-- );

fnd_global.apps_initialize (0,51967, 222);

mo_global.init ('AR');

DBMS_OUTPUT.put_line('Invoking Credit Memo Creation process');

-- l_trx_number := '19445';

FOR lc_inv IN c_inv(p_cust_trx_id)

LOOP

ind := ind + 1;

l_customer_trx_id := lc_inv.customer_trx_id;

l_cm_lines_tbl(ind).customer_trx_line_id := lc_inv.customer_trx_line_id;

l_cm_lines_tbl(ind).quantity_credited := lc_inv.quantity_invoiced * -1;

l_cm_lines_tbl(ind).price := lc_inv.unit_selling_price;

l_cm_lines_tbl(ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * -1;

DBMS_OUTPUT.put_line('process'||ind);

END LOOP;

ar_credit_memo_api_pub.create_request( -- standard api parameters

p_api_version => 1.0

, p_init_msg_list => fnd_api.g_true

, p_commit => fnd_api.g_false

-- , p_validation_level => FND_API.G_VALID_LEVEL_FULL

-- credit memo request parameters

, p_customer_trx_id => l_customer_trx_id

, p_line_credit_flag => 'Y'

, P_CM_LINE_TBL => l_cm_lines_tbl

, p_cm_reason_code => 'RETURN'

, p_skip_workflow_flag => 'Y'

, p_batch_source_name => 'MANUAL-OTHER'

, p_interface_attribute_rec => v_interface_header_rec

, p_credit_method_installments => NULL

, p_credit_method_rules => NULL

, x_return_status => v_return_status

, x_msg_count => v_msg_count

, x_msg_data => v_msg_data

, x_request_id => v_request_id);

DBMS_OUTPUT.put_line('Message count ' || v_msg_count);

IF v_msg_count = 1

THEN

DBMS_OUTPUT.put_line('l_msg_data ' || v_msg_data);

ELSIF v_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

v_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);

IF v_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line('Message' || p_count || ' ---' || v_msg_data);

END LOOP;

END IF;

IF v_return_status <> 'S'

THEN

DBMS_OUTPUT.put_line('Failed');

ELSE

SELECT cm_customer_trx_id

INTO cm_trx_id

FROM ra_cm_requests_all

WHERE request_id = v_request_id;

DBMS_OUTPUT.put_line(' CM trx_id = ' || cm_trx_id);

-- You can issue a COMMIT; at this point if you want to save the created credit memo to the database

COMMIT;

END IF;

END xx_create_cm_inv;