AR Receipts Conversion-Overview

Populate Data in Staging Table (Design your staging table to include STATUS column and ERROR MESSAGE and Activity Date)
Define a cursor to select all rows (including rowid ) from staging table
Define Local Variables
–include ln_set_of_bks_id NUMBER := FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’);
ln_request_id NUMBER:= FND_GLOBAL.CONC_REQUEST_ID;
lc_status VARCHAR2(10);
lc_err_msg VARCHAR2(1000);

Loop through the record of cursor containg data from staging table:
Begin
FND_MESSAGE.CLEAR;
FOR lr_receipts_stg IN lcu_receipts_stg
LOOP
EXIT WHEN lcu_receipts_stg%NOTFOUND;
ln_err_flag := 1;
lc_status :=NULL;
lc_err_msg:=NULL;

Start Validations
populate lc_err_msg
lc_status
ln_err_flag accordingly

Update staging table and error tables accordingly

UPDATE XX_AR_CASH_RECEIPTS_STG
SET status=lc_status
,err_msg=lc_err_msg
,conversion_date=SYSDATE
,request_id=ln_request_id
WHERE receipt_number=lr_receipts_stg.receipt_number
AND rowid=lr_receipts_stg.rowid;

INSERT INTO XXL_AR_RECIPTS_CONV_ERRORS
(receipt_number
,customer_number
,status
,err_msg
,conversion_date
,request_id
)
VALUES (lr_receipts_stg.receipt_number
,lr_receipts_stg.customer_number
,’E’
,’Receipr Number is Null’
,SYSDATE
,ln_request_id
);
END IF;

 

Validations:
Receipt Number is Null
Receipt amount is Null
Receipt amount is negative
Receipt Date is Null .If not null then check Receipt date is not in open period of AR( gl_period_statuses , fnd_application )
operating unit is null .If not null then check if it exists.(hr_operating_units)
Currency code is null .If not null then check if it exists .(fnd_currencies)
receipt_method is null .If not null then check if it exists.(ar_receipt_methods)
GL Date is Null .If not null then check GL date is not in open period of AR
Customer Number and Customer Site.Check for duplicates.(when too_many_rows)(hz_cust_accounts_all,hz_cust_acct_sites_all,hz_party_sites,hz_cust_site_uses_all)
Duplicate Receipts Check(Based on Receipt Date, Receipt Number, Receipt Amount,pay_from_customer of ar_cash_receipts_all=ln_cust_account_id)

Update status column of staging table to Valid depending on flag value for valid records.

Print the different records status wise to log file

Define a procedure for receipt upload.

Define a cusror to pick all the records from staging table with Valid status

Define local variables:
ln_total_records
ln_uploaded_records
ln_error_records
—api out variables:
lcx_return_status
lnx_msg_count
lcx_msg_data
–api in variables:
ln_cr_id NUMBER;
ln_org_id NUMBER;
ln_receipt_method_id NUMBER;
ln_exchange_rate NUMBER;
p_exchange_rate NUMBER;
lc_rate_type VARCHAR2(50);
ln_amount NUMBER;
lc_location VARCHAR2(40);
lc_customer_number VARCHAR2(30);
ln_cust_account_id NUMBER;

Populate api in variables taking values from defined cursor(valid records) and base tables

Then
Call mo_global.init(‘AR’);

Call API:
AR_RECEIPT_API_PUB.CREATE_CASH(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
,p_currency_code => lr_receipts_stg_v.currency_code
,p_exchange_rate => ln_exchange_rate
,p_usr_exchange_rate_type => lc_rate_type
,p_amount => lr_receipts_stg_v.amount–ln_amount
,p_receipt_number => lr_receipts_stg_v.receipt_number
,p_receipt_date => lr_receipts_stg_v.receipt_date
,p_gl_date => SYSDATE-1
,p_customer_number => lc_customer_number
,p_location => lc_location
,p_receipt_method_id => ln_receipt_method_id
,p_receipt_method_name => lr_receipts_stg_v.receipt_method
,p_customer_receipt_reference => lr_receipts_stg_v.customer_receipt_reference
,p_comments => lr_receipts_stg_v.comments
,x_return_status => lcx_return_status
,x_msg_count => lnx_msg_count
,x_msg_data => lcx_msg_data
,p_cr_id => ln_cr_id
,p_org_id => ln_org_id
);

If lcx_return_status = FND_API.G_RET_STS_SUCCESS then update staging table’s status as Processed else update with status Error
Meanwhile get the message data for errored records

DBMS_OUTPUT.PUT_LINE(‘count:’||lnx_msg_count);
IF lnx_msg_count > 0 THEN
FOR l_index IN 1..lnx_msg_count
LOOP
lcx_msg_data := fnd_msg_pub.get(p_msg_index => l_index
,p_encoded => FND_API.G_FALSE
) ;
END LOOP;
DBMS_OUTPUT.PUT_LINE(SUBSTR(lcx_msg_data,1,250));

Insert these error records into error table

Print the number of successful ,errored records in log file.

 

 

 

 

 

 

Advertisements

Adding Responsibility to a user from backend

Begin
fnd_user_pkg.addresp (‘YOUR_USER_NAME’,’SYSADMIN’,’SYSTEM_ADMINISTRATOR’,’STANDARD’,’add responsibility to user using pl/sql’,sysdate-1,sysdate + 100);
commit;
dbms_output.put_line(‘responsibility added successfully to the user ‘);
exception
when others then
dbms_output.put_line(‘ responsibility adding failed due to’ || sqlcode || substr(sqlerrm, 1, 100));
rollback;
end;

–You can view the changes immediately.

Query to find concurrent program parameters and associated value sets

SELECT fcpl.user_concurrent_program_name “Concurrent Program Name”,
fcp.concurrent_program_name “Program Short Name”,
fdfcuv.column_seq_num “Column Seq #”,
fdfcuv.end_user_column_name “Parameter Name”,
fdfcuv.form_left_prompt “Prompt Name”,
fdfcuv.enabled_flag “Enabled Flag”,
fdfcuv.required_flag “Required Flag”,
fdfcuv.display_flag “Display Flag”,
fdfcuv.flex_value_set_id “Value Set ID”,
ffvs.flex_value_set_name “Value Set Name”,
flv.meaning “Default Type”,
fdfcuv.default_value “Default Value”
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fdfcuv.descriptive_flexfield_name = ‘$SRS$.’ || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = ‘FLEX_DEFAULT_TYPE’
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV(‘LANG’)
AND flv.LANGUAGE(+) = USERENV(‘LANG’)
AND fdfcuv.enabled_flag = ‘Y’
and FCPL.USER_CONCURRENT_PROGRAM_NAME = :pconcurrent_program
ORDER BY fdfcuv.column_seq_num;

Concurrent program running between particular date and their incompatible programs

SELECT distinct FU.USER_NAME,
papf.full_name,
FCR.REQUEST_ID,
FCR.ACTUAL_START_DATE,
FCR.ACTUAL_COMPLETION_DATE,
round( ((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60),2 )RUN_TIME_Mins,
FCR.IS_SUB_REQUEST,
fcr.parent_request_id,
FCR.HAS_SUB_REQUEST,
FCPT.USER_CONCURRENT_PROGRAM_NAME,
B.USER_CONCURRENT_QUEUE_NAME MANAGER,
FCPT2.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_TO,
fcs.incompatibility_type
FROM APPS.FND_CONCURRENT_REQUESTS FCR,
APPS.FND_USER FU,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.FND_CONCURRENT_PROCESSES a,
APPS.FND_CONCURRENT_QUEUES_VL B,

APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
APPS.FND_CONCURRENT_PROGRAMS_TL FCPT2,
APPS.FND_CONCURRENT_PROGRAM_SERIAL FCS
where FCR.ACTUAL_START_DATE >=:P_START_DATE
AND fcr.ACTUAL_COMPLETION_DATE <=:p_completion_date
AND FCR.REQUESTED_BY =FU.USER_ID
AND FU.USER_ID =PAPF.PERSON_ID
and FCR.REQUESTED_BY =PAPF.PERSON_ID
and a.CONCURRENT_QUEUE_ID = B.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = FCR.CONTROLLING_MANAGER
and FCPT.CONCURRENT_PROGRAM_ID =FCR.CONCURRENT_PROGRAM_ID
and FCS.RUNNING_CONCURRENT_PROGRAM_ID=FCPT.CONCURRENT_PROGRAM_ID
and FCS.TO_RUN_CONCURRENT_PROGRAM_ID=FCPT2.CONCURRENT_PROGRAM_ID
and FCPT.LANGUAGE=’US’
and fcpt2.language=’US’
AND fcr.REQUEST_ID = FCR.REQUEST_ID

Concurrent Program Executable

SELECT DISTINCT fcpt.user_concurrent_program_name ,
fcp.concurrent_program_name short_name ,
fat.application_name program_application_name ,
fet.executable_name ,
fat1.application_name executable_application_name ,
flv.meaning execution_method ,
fet.execution_file_name ,
FCP.ENABLE_TRACE
FROM APPS.FND_CONCURRENT_PROGRAMS_TL FCPT ,
APPS.FND_CONCURRENT_PROGRAMS FCP ,
APPS.FND_APPLICATION_TL FAT ,
APPS.FND_EXECUTABLES FET ,
APPS.FND_APPLICATION_TL FAT1 ,
apps.FND_LOOKUP_VALUES FLV
WHERE 1 =1
AND fcpt.user_concurrent_program_name=:p_program_name
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcpt.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fat1.application_id
AND FLV.LOOKUP_CODE = FET.EXECUTION_METHOD_CODE
AND FCPT.LANGUAGE =FAT.LANGUAGE
AND FCPT.LANGUAGE =Flv.LANGUAGE
AND fcpt.language =userenv(‘LANG’)
AND FLV.LOOKUP_TYPE =’CP_EXECUTION_METHOD_CODE’ ;

List of Users Having A Particular Responsibility

SELECT FU.USER_NAME,
papf.full_name,
frt.responsibility_name,
TO_CHAR(furg.start_date,’DD-MON-YYYY’) start_date,
furg.end_date
FROM fnd_user fu ,
fnd_user_resp_groups_direct furg ,
FND_RESPONSIBILITY_VL FRT,
per_all_people_f papf
WHERE fu.user_id = furg.user_id
AND frt.responsibility_id = furg.responsibility_id
AND frt.application_id = furg.responsibility_application_id
AND NVL(furg.end_date,sysdate+1) > sysdate
AND NVL(frt.end_date,sysdate +1) > sysdate
and NVL(FU.END_DATE,sysdate +1) > sysdate
AND papf.person_id =fu.user_id
AND FRT.RESPONSIBILITY_NAME =’Application Developer’ ;

Finding Responsibilities assigned to a user

SELECT fu.user_name ,
frt.responsibility_name,
furg.start_date,
FURG.END_DATE
FROM APPS.FND_USER FU ,
APPS.FND_USER_RESP_GROUPS_DIRECT FURG ,
apps.fnd_responsibility_vl frt
WHERE fu.user_id = furg.user_id
AND frt.responsibility_id = furg.responsibility_id
AND frt.application_id = furg.responsibility_application_id
AND NVL(furg.end_date,sysdate+1) > sysdate
AND NVL(FRT.END_DATE,sysdate +1) > sysdate
AND FU.USER_NAME = :p_user_name;

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

CREATE TABLE exception_test (
  id  NUMBER(10) NOT NULL
);

set serveroutput on;

DECLARE
TYPE exc_tab
IS
TABLE OF exception_test%ROWTYPE;
l_exc exc_tab := exc_tab();
l_error_count NUMBER;
E_EXC_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(e_exc_errors, -24381);
BEGIN
— Fill the collection.
FOR I IN 1 .. 100
LOOP
L_EXC.extend;
l_exc(l_exc.last).id := i;
END LOOP;
–Error Statments stated below
L_EXC(50).id := NULL;
L_EXC(51).id := NULL;
–End of Error Statments
EXECUTE IMMEDIATE ‘TRUNCATE TABLE exception_test’;
— Perform a bulk operation.
BEGIN
FORALL i IN l_exc.first .. l_exc.last SAVE EXCEPTIONS
INSERT INTO EXCEPTION_TEST VALUES l_exc
(i
);
EXCEPTION
WHEN e_exc_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line(‘Number of failures: ‘ || l_error_count);
FOR i IN 1 .. l_error_count
LOOP
DBMS_OUTPUT.put_line
(
‘Error: ‘ || i || ‘ Array Index: ‘ || SQL%BULK_EXCEPTIONS(i).error_index || ‘ Message: ‘ || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
)
;
END LOOP;
END;
END;
/