Custom Program LDT Download and Upload

SELECT ftl.user_concurrent_program_name, fcp.concurrent_program_name,
fe.execution_file_name, fe.execution_method_code,
‘FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ‘
|| fcp.concurrent_program_name
|| ‘.ldt ‘
|| ‘PROGRAM APPLICATION_SHORT_NAME=’
|| ””
|| fa.application_short_name
|| ””
|| ‘ CONCURRENT_PROGRAM_NAME=’
|| ””
|| fcp.concurrent_program_name
|| ”” ldt_download,
‘FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ‘
|| fcp.concurrent_program_name
|| ‘.ldt’
|| ‘ – WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE’
ldt_upload
FROM fnd_concurrent_programs_tl ftl,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_application fa
WHERE ftl.concurrent_program_id = fcp.concurrent_program_id
AND ftl.application_id = fa.application_id
AND fe.application_id = ftl.application_id
AND fe.executable_id = fcp.executable_id
AND ftl.user_concurrent_program_name LIKE ‘XX%’
AND fcp.enabled_flag = ‘Y’
ORDER BY fe.execution_method_code

Advertisements

Finding Trace File

SELECT request_id, oracle_process_id trace_id, req.enable_trace trace_flag,
dest.VALUE
|| ‘/’
|| LOWER (dbnm.VALUE)
|| ‘_ora_’
|| oracle_process_id
|| ‘.trc’ trace_file,
prog.user_concurrent_program_name,
execname.execution_file_name || execname.subroutine_name executable,
DECODE (phase_code, ‘R’, ‘Running’)
|| ‘-‘
|| DECODE (status_code, ‘R’, ‘Normal’) program_status,
ses.SID SID, ses.serial# serial_no, ses.module module
FROM fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
WHERE 1 = 1 –req.request_id = &request
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = ‘user_dump_dest’
AND dbnm.NAME = ‘db_name’
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id;

Item Assignment to Inventory Org

The basic script is as mentioned below:

DECLARE
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
l_item_table ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
BEGIN
apps.fnd_global.apps_initialize (apps.fnd_profile.VALUE (‘USER_ID’),
apps.fnd_profile.VALUE (‘RESP_ID’),
apps.fnd_profile.VALUE (‘RESP_APPL_ID’)
);
ego_item_pub.assign_item_to_org
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
— p_inventory_item_id => 54110,–i.inventory_item_id –non mandatory
p_item_number => ‘Segment1 of mtl system items ‘,
— Pass Segment 1 Here
p_organization_id => ‘Oganization in whick to assign’,
x_return_status => x_return_status,
x_msg_count => x_msg_count
);

IF x_return_status = ‘S’
THEN
DBMS_OUTPUT.put_line (‘Success’);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Item Not Assigned ‘);
error_handler.get_message_list (x_message_list => x_message_list);

FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
END LOOP;
END IF;
END;

Item assignment fails in cases where the items have been created using template and in the template ,some org attributes have been enabled but no value is assigned to them.

Therefore, make sure to disabled those attributes or assign a value. Don’t leave the field as null.

Creating Employee As Supplier

The prerequisite is to have Create/Update Employee Supplier Details (POS_HT_SP_EMP_SUPPLIER) with the grant flag checked in the menu of the responsibility for full access to employee suppliers.

Setting mo_global.init(‘SQLAP’) is a must if running standalone script.

If submitting from concurrent program from custom responsibility, make sure the function the function Create/Update Employee Supplier Details is attached to Menu of the custom responsibility.

 
DECLARE
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2 (10);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
p_count NUMBER;

BEGIN
mo_global.init(‘SQLAP’); –A must if running standalone script.
_vendor_rec.vendor_name := ’07MAR2′;
l_vendor_rec.employee_id := 20158; –not mandatory
l_vendor_rec.vendor_type_lookup_code := ‘EMPLOYEE’;
ap_vendor_pub_pkg.create_vendor (p_api_version => 1,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_vendor_rec => l_vendor_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id
);

IF (l_return_status <> ‘S’)
THEN
DBMS_OUTPUT.put_line (‘ Error in Creating Employee Type Supplier..’);

IF (l_msg_count = 1)
THEN
DBMS_OUTPUT.put_line (‘x_msg_data ‘ || l_msg_data);
ELSIF (l_msg_count > 1)
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF (l_msg_data IS NULL)
THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line ( ‘ Error Message’
|| p_count
|| ‘ —‘
|| l_msg_data
);
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.put_line (‘Vendor_id:’ || l_vendor_id);
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (‘Exception:’ || SQLERRM);
END;

 

Query to get Accounting KFF Information

select FIF.APPLICATION_ID ,
FIF.ID_FLEX_CODE ,
FIF.ID_FLEX_NAME ,
FIF.APPLICATION_TABLE_NAME ,
FIF.DESCRIPTION ,
FIFS.ID_FLEX_NUM ,
FIFS.ID_FLEX_STRUCTURE_CODE ,
FIFSE.SEGMENT_NAME,
FIFSE.SEGMENT_NUM,
FIFSE.FLEX_VALUE_SET_ID
from FND_ID_FLEXS FIF ,
FND_ID_FLEX_STRUCTURES FIFS ,
FND_ID_FLEX_SEGMENTS FIFSE
where FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and FIF.ID_FLEX_CODE = FIFS.ID_FLEX_CODE
and FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and FIF.ID_FLEX_CODE LIKE ‘GL#’
and FIF.ID_FLEX_NAME LIKE ‘Accounting Flexfield’;

Getting XML Data from query

declare
QRY_HANDLE_INFO DBMS_XMLGEN.CTXHANDLE;
V_ORDER_DETAILS_QUERY varchar2(100);
v_xml_result CLOB;

begin
V_ORDER_DETAILS_QUERY:=’select sysdate from dual’;
— DBMS_XMLGEN.SETROWSETTAG(QRY_HANDLE_INFO, ‘Headers’);
— DBMS_XMLGEN.setRowTag(qry_handle_info, ‘Header’);
QRY_HANDLE_INFO:=DBMS_XMLGEN.NEWCONTEXT(V_ORDER_DETAILS_QUERY);
V_XML_RESULT:=DBMS_XMLGEN.GETXML(QRY_HANDLE_INFO);
v_xml_result := REPLACE( v_xml_result
, ‘<?xml version=”1.0″?>’
, ‘<?xml version=”1.0″ encoding=”ISO-8859-1″ ?>’ );
DBMS_OUTPUT.PUT_LINE(V_XML_RESULT);
DBMS_XMLGEN.closeContext (qry_handle_info);
end;

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.

 

 

 

 

 

 

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