FTP in UNIX with Error Handling

Here is the script to handle FTP in UNIX. It also includes error including.

unix command $? will not capture error status, so error message has to be written to a logfile and then read.

error_log=/tmp/error_log.log
lc_server_name="your.ftp.server.name"
echo "1" $lc_server_name
ftp -i -n -d<<EOF >$error_log
open $lc_server_name
user userid password
bye
EOF
result=`grep -c 'Not connected.' error_log.log`
result1=`grep -c 'Login failed.' error_log.log`
echo $result
echo $result1
if [ ${result} -gt 0 -o ${result1} -gt 0 ]; then
echo "Connect to FTP failed"
else
echo "Connection Successful"
fi

APP-FND-01926 - The custom event WHEN-LOGON-CHANGED raised unhandled exception.

"APP-FND-01926 - The custom event WHEN-LOGON-CHANGED raised unhandled exception. ORA-06508 PL/SQL could not find program unit being called"

Usually this error occurs when opening Form in application.

This happens mostly after Upgrade/Clone of applications.

Reason being invalid objects in Custom.pll, form throws an error when opened.

This solution applicable in most cases when apps is cloned.

Compilation of pll's from Unix will compile only standard pll's. Custom pll's used has to be compiled manually.

To know existing custom Pll's open custom.pll in forms developer and expand Attached Libraries. The libraries you see there are probably the pll's that has to be compiled from Unix.

Compile custom pll's using the following command to generate plx.

f60gen module=.pll userid=userid/pwd output_file=$AU_TOP/resource/.plx module_type=library batch=no compile_all=special

Eg:

f60gen module=ABC.pll userid=APPS/PWD output_file=$AU_TOP/resource/ABC.plx module_type=library batch=no compile_all=special


For Upgrade

Check CUSTOM package body in custom.pll, you might have missed some objects that are referred. If any DB objects are missing, compiling (SHIFT+CTRL+K) form might help.

Attachments API - FND_DOCUMENTS_PKG, FND_ATTACHED_DOCUMENTS_PKG

Working on attachments need some knowledge of how an attachment functions (Definitions, Entity, Document Category, Entities and Blocks, Primary Keys in attachments (If exists)) in Oracle Apps. To understand the below API and what it actually does its advisable to read the Attachments Chapter in Developers Guide.

Primary Key information that uniquely identifies the product (such as the product_ID).

Below API takes single file from a shared drive and inserts into fnd_lobs. Then it's attached to a particular Id (like order_id, invoice_id..) using the combination of pk1_value, entity name and category. In this example i used PDF as attachment. Quering the table fnd_document_datatypes will give different attachment types.

This sample shows how an attachment API works. You can use it to customize/enhance to requirement.

DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE:=<Primary Key information that uniquely identifies the product (such as the product_ID)>;
l_description fnd_documents_tl.description%TYPE:='Test Attachment';
l_filename VARCHAR2(240) := '<File Name>';
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length INTEGER;
l_entity_name VARCHAR2(100) := <entity_name>;
l_category_name VARCHAR2(100) := <category_name>;

BEGIN

fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);

SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;


-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name = <user_name>;

-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = 'FILE';

-- Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE USER_NAME = l_category_name;

-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
--apps.fnd_documents_long_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id
--l_media_id
FROM DUAL;


SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;

fils := BFILENAME (<FLIE PATH>, l_filename);

-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.

INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf
SYSDATE,
NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,
'US', 'UTF8', 'binary'
)
RETURNING file_data
INTO x_blob;

-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;


-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_datatype_id => l_short_datatype_id, -- FILE
X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => 'N', --This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => 'S',
x_language => 'US',
x_description => l_filename,--l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);

commit;



fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_language => 'US',
x_description => l_filename--l_description
);
COMMIT;
fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 6,
x_category_id => l_category_id,
x_security_type => 1,
X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_filename,--l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
END;
/

Exploding BOM - bompxinq.exploder_userexit

The main purpose of exploding BOM is to get the details from the temporary table that oracle uses to hold data. Its session based. So once you are out of that session the data is gone. BOM_SMALL_EXPL_TEMP is the table used to hold the data in a session.

Data cannot be retrived be querying the View from FORM --> HELP-->Record History.
Hence explode the BOM for the Item you needed data.

There are many ways  of holding the data.You can write to a file, print report so on..Its upto you.

In this post i used a table BOM_SMALL_EXPL_TEMP1 to hold the data. Remember its not a temporary table, its a copy of BOM_SMALL_EXPL_TEMP with all the columns.

I too found it difficult to find the values to explode BOM. So i have explored the standard oracle form to find out what columns its uses to explode. All the comments after assignments are the standard oracle Block.FieldName (Block Name -> B_BILL_OF_MATLS). So you can pass the values of your Appliction if needed.

Version : 12.0.6

create or replace Procedure EAM_BOM_EXPLODE
( p_item_id in number ) /* The BOM assembly which you want to explode */
is

v_group_id NUMBER;
x_error_message VARCHAR2 (2000);
x_error_code NUMBER;
sess_id number;
l_rec_count number;

begin

fnd_global.apps_initialize(<>,<>,<>);

delete from BOM_SMALL_EXPL_TEMP1;

commit;

SELECT bom_explosion_temp_s.NEXTVAL
INTO v_group_id
FROM DUAL;

SELECT bom_explosion_temp_session_s.NEXTVAL
INTO sess_id
FROM Dual ;

bompxinq.exploder_userexit(
Verify_Flag => 0,
Org_Id => **ORG_ID**,
Order_By => 1,--:B_Bill_Of_Matls.Bom_Bill_Sort_Order_Type,
Grp_Id => v_group_id,
Session_Id => 0,
Levels_To_Explode => 20, --:B_Bill_Of_Matls.Levels_To_Explode,
Bom_Or_Eng => 1, -- :Parameter.Bom_Or_Eng,
Impl_Flag => 1, --:B_Bill_Of_Matls.Impl_Only,
Plan_Factor_Flag => 2, --:B_Bill_Of_Matls.Planning_Percent,
Explode_Option => 3, --:B_Bill_Of_Matls.Bom_Inquiry_Display_Type,
Module => 2,--:B_Bill_Of_Matls.Costs,
Cst_Type_Id => 0,--:B_Bill_Of_Matls.Cost_Type_Id,
Std_Comp_Flag => 2,
Expl_Qty => 1,--:B_Bill_Of_Matls.Explosion_Quantity,
Item_Id => p_item_id,--:B_Bill_Of_Matls.Assembly_Item_Id,
Alt_Desg => null,--:B_Bill_Of_Matls.Alternate_Bom_Designator,
Comp_Code => null,
Unit_Number_From => 0, --NVL(:B_Bill_Of_Matls.Unit_Number_From, :CONTEXT.UNIT_NUMBER_FROM),
Unit_Number_To => 'ZZZZZZZZZZZZZZZZZ', --NVL(:B_Bill_Of_Matls.Unit_Number_To, :CONTEXT.UNIT_NUMBER_TO),
Rev_Date => sysdate, --:B_Bill_Of_Matls.Disp_Date,
Show_Rev => 1, -- yes
Material_Ctrl => 2, --:B_Bill_Of_Matls.Material_Control,
Lead_Time => 2, --:B_Bill_Of_Matls.Lead_Time,
err_msg => x_error_message, --err_msg
error_code => x_error_code); --error_code

select count(*) into l_rec_count from
--BOM_EXPLOSION_TEMP temp
BOM_SMALL_EXPL_TEMP temp where temp.group_id = v_group_id;

DBMS_OUTPUT.PUT_LINE('l_rec_count = '||l_rec_count);

insert into BOM_SMALL_EXPL_TEMP1 select * from BOM_SMALL_EXPL_TEMP;

commit;

DBMS_OUTPUT.PUT_LINE(x_error_message);
DBMS_OUTPUT.PUT_LINE(x_error_code);
DBMS_OUTPUT.PUT_LINE('grp_id = '||v_group_id);
DBMS_OUTPUT.PUT_LINE('sess_id = '||sess_id);

End;

Once you are done compiling execute the following command to insert the data into the table you have created in the above case its BOM_SMALL_EXPL_TEMP1.

Begin
EAM_BOM_EXPLODE(p_item_id);
End;
/
select * from BOM_SMALL_EXPL_TEMP1;

Have a Good Day.