PL/SQL Compiler Limits

Came across this piece in PL/SQL Language Reference:

https://docs.oracle.com/database/121/LNPLS/limits.htm#LNPLS018

Table C-1 PL/SQL Compiler Limits

Item Limit
bind variables passed to a program unit 32768
exception handlers in a program unit 65536
fields in a record 65536
levels of block nesting 255
levels of record nesting 32
levels of subquery nesting 254
levels of label nesting 98
levels of nested collections no predefined limit
magnitude of a PLS_INTEGER orBINARY_INTEGER value -2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure 65536
objects referenced by a program unit 65536
precision of a FLOAT value (binary digits) 126
precision of a NUMBER value (decimal digits) 38
precision of a REAL value (binary digits) 63
size of an identifier (characters) 30
size of a string literal (bytes) 32767
size of a CHAR value (bytes) 32767
size of a LONG value (bytes) 32760
size of a LONG RAW value (bytes) 32760
size of a RAW value (bytes) 32767
size of a VARCHAR2 value (bytes) 32767
size of an NCHAR value (bytes) 32767
size of an NVARCHAR2 value (bytes) 32767
size of a BFILE value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a BLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a CLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of an NCLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a trigger 32 K
Advertisements

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

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;