Oracle Technology

Friday, 23 April 2021

TAR File : How to Create TAR FILE

RTF Template Function and XML method TAG

1) How to use NVL function in RTF template (XML Publisher)

<?xdofx:nvl(RISF_VC_LY,0)-nvl(RISF_VC_AC,0)?>

Saturday, 3 April 2021

OPM : API : How to upload GMD Specification validity for Customer

How to upload GMD Specification validity for Customer

DECLARE

--l_customer_spec_vrs GMD_CUSTOMER_SPEC_VRS%ROWTYPE;

l_customer_spec_vrs_tbl GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;

--l_customer_spec_vrs_out GMD_CUSTOMER_SPEC_VRS%ROWTYPE;

x_customer_spec_vrs_tbl GMD_SPEC_VRS_PUB.customer_spec_vrs_tbl;

l_return_status CHAR(1);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

BEGIN

fnd_profile.initialize (2820);

fnd_global.apps_initialize(user_id =>1142,resp_id =>23805 ,resp_appl_id =>552 );

fnd_msg_pub.initialize;

gme_common_pvt.g_error_count := 0;

gme_common_pvt.set_timestamp;

gme_common_pvt.g_move_to_temp := fnd_api.g_false;

--l_customer_spec_vrs.spec_vr_id := NULL;

l_customer_spec_vrs_tbl(1).spec_id :=12416;--- 12420;

-- l_customer_spec_vrs.ORG_ID := 110;

l_customer_spec_vrs_tbl(1).cust_id := 10988;

l_customer_spec_vrs_tbl(1).ship_to_site_id := NULL;

l_customer_spec_vrs_tbl(1).spec_vr_status := 100;--700

l_customer_spec_vrs_tbl(1).delete_mark := 0;

l_customer_spec_vrs_tbl(1).start_date := SYSDATE;

-- l_customer_spec_vrner_id := q.USER_ID;

l_customer_spec_vrs_tbl(1).creation_date := SYSDATE;

l_customer_spec_vrs_tbl(1).created_by := 1133;

l_customer_spec_vrs_tbl(1).last_updated_by := 1133;

l_customer_spec_vrs_tbl(1).last_update_date := SYSDATE;

l_customer_spec_vrs_tbl(1).last_update_login := 1133;

apps.GMD_SPEC_VRS_PUB.CREATE_CUSTOMER_SPEC_VRS

( p_api_version => '1.0'

, p_init_msg_list => apps.FND_API.G_FALSE

, p_commit => apps.FND_API.G_TRUE

, p_validation_level => apps.fnd_api.g_valid_level_full

, p_customer_spec_vrs_tbl => l_customer_spec_vrs_tbl

, p_user_name => 'ALAM'

, x_customer_spec_vrs_tbl => x_customer_spec_vrs_tbl

, x_return_status => l_return_status

, x_msg_count => l_msg_count

, x_msg_data => l_msg_data

);

COMMIT;

dbms_output.put_line( l_customer_spec_vrs_tbl(1).spec_id );

dbms_output.put_line('return_status:-----'||l_return_status);

dbms_output.put_line('Msg_Count:--------'|| l_msg_count||','||l_msg_data);

--dbms_output.put_line('Msg_Count:--------'|| l_msg_count);

--dbms_output.put_line('msg_data ---------'||x_msg_data);

--dbms_output.put_line('spec_id-----------'||x_spec);

--dbms_output.put_line('spec_test_id------'||x_spec_tests_tbl);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

THEN

FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP

l_msg_data := FND_MSG_PUB.get( p_msg_index => i,

p_encoded => FND_API.G_FALSE

);

dbms_output.put_line('The API call failed with error '||l_msg_data);

END LOOP;

ELSE

dbms_output.put_line('The API call ended with SUCESSS status');

END IF;

END;

Monday, 29 March 2021

Oracle Apps Query : how to Find DFF Flex Fields Attributes

To Find DFF Flex field attribute

SELECT ffv.application_table_name, ffv.descriptive_flexfield_name,

ffv.context_column_name, ffv.title, att.application_column_name,

att.end_user_column_name, att.column_seq_num, att.enabled_flag,

att.required_flag, att.security_enabled_flag, att.display_flag,

att.flex_value_set_id, att.form_left_prompt, ffv.*

FROM fnd_descriptive_flexs_vl ffv, fnd_descr_flex_col_usage_vl att

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

-- AND ffv.descriptive_flexfield_name = 'PO_LINES'

AND (ffv.title) = 'Transaction Information'

EBS : Query to find Operating units in Oracle Apps

How to find Operating Units in Oracle Apps

SELECT hr.organization_id org_id, hr.NAME operating_unit,po_moac_utils_pvt.get_ou_shortcode (organization_id) ou_short_code

FROM hr_operating_units hr

WHERE po_moac_utils_pvt.check_access (hr.organization_id) = 'Y'

ORDER BY 1

===========================================================================

#Oracle Apps #EBS #OU #Operating Units

Oracle Apps : how to delete Concurrent request/Executables

SET SERVEROUTPUT ON
DECLARE
   -- Change the following two parameters
   var_templateCode    VARCHAR2 (100) := 'Order_Details';     -- Template Code
   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.
BEGIN
   FOR RS
      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
                 T1.DATA_SOURCE_CODE,
                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME
            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
           WHERE T1.TEMPLATE_CODE = var_templateCode
                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
   LOOP
      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS
            WHERE     LOB_CODE = var_templateCode
                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES
            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND TEMPLATE_CODE = var_templateCode
                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Selected template has been ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef
      THEN
         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS
               WHERE LOB_CODE = RS.DATA_SOURCE_CODE
                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND LOB_TYPE IN
                            ('XML_SCHEMA',
                             'DATA_TEMPLATE',
                             'XML_SAMPLE',
                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES
               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (
            'Selected Data Defintion has been ' || RS.DATA_SOURCE_CODE || ' deleted.');
      END IF;
   END LOOP;
COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'Unable to delete XML Publisher Template ' || var_templateCode);
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;

Oracle Apps Query P2P-GL: PO to Gl Link Query and details

select a.ITEM_DESCRIPTION,a.*

from ap_invoice_lines_all a

select * from mtl_system_items_b

where description like '%MILK%'

AND ORGANIZATION_ID=103

AND INVENTORY_ITEM_ID =2135

2135 -- MILK (COW)

---------------------PO---------------------------------------------------------

SELECT * FROM PO_HEADERS_ALL

WHERE PO_HEADER_ID=2017

SELECT *FROM PO_LINES_ALL

WHERE ITEM_ID IN ( select INVENTORY_ITEM_ID from mtl_system_items_b

where description like '%MILK%'

AND ORGANIZATION_ID=103)

AND ITEM_ID=2135

AND PO_HEADER_ID=2017

-------------------Receipts-----------------------------------------------------

SELECT * FROM RCV_SHIPMENT_LINES R

WHERE 1=1--ORGANIZATION_ID=103

AND ITEM_ID=2135

AND PO_HEADER_ID=2017

AND PO_LINE_ID=2034

SELECT TRANSACTION_ID,a.* FROM RCV_TRANSACTIONS a

WHERE ORGANIZATION_ID=103

--AND ITEM_ID=2135

AND PO_HEADER_ID=2017

AND SHIPMENT_LINE_ID = 5046;

--------------------MTL ACCOUNTING----------------------------------------------

select TRANSACTION_ID,a.* from mtl_material_transactions a

where a.RCV_TRANSACTION_ID in (2060,2062,2063);

select * from mtl_transaction_accounts

where TRANSACTION_ID =89463

--------------AP INV------------------------------------------------------------

select * from ap_invoices_all

where invoice_id = 35029

select * from ap_invoice_distributions_all aid

where (invoice_id ,aid.INVOICE_LINE_NUMBER) in (

select invoice_id ,ail.LINE_NUMBER from ap_invoice_lines_all ail

where po_header_id=2017

AND PO_LINE_ID=2034)

--------------Sub Ledger Inv ---------------------------------------------------

select *from xla.XLA_TRANSACTION_ENTITIES xte

where 1=1

AND xte.entity_code = 'AP_INVOICES'

and SOURCE_ID_INT_1=35029 --invoice id--

select * from xla_ae_headers

where entity_id=60015

select * from xla_ae_lines

where ae_header_id=71836

-----------------------INV GL---------------------------------------------------

select * from gl_import_references

where gl_sl_link_id in (

select gl_sl_link_id from xla_ae_lines

where ae_header_id=71836 )

select (select gl_flexfields_pkg.get_description_sql (:char_of_account_id, 5, gcc.segment5) FROM gl_code_combinations gcc

where gcc.CODE_COMBINATION_ID =gil.CODE_COMBINATION_ID ) acc_desc

,gil.* from gl_je_lines gil

where je_header_id in (54034)

-------------------AP Pay------------------

select * from ap_invoice_payments_all

where invoice_id=35029

select * from ap_checks_all

where check_id = 33542

--------------Sub Ledger Pay ---------------------------------------------------

select *from xla.XLA_TRANSACTION_ENTITIES xte

where 1=1

AND xte.entity_code = 'AP_PAYMENTS'

and SOURCE_ID_INT_1=33542 --check id--

select * from xla_ae_headers

where entity_id=60079

select gl_sl_link_id,a.* from xla_ae_lines a

where ae_header_id=71837

-----------------------Payments GL---------------------------------------------------

select * from gl_import_references

where gl_sl_link_id in (

select gl_sl_link_id from xla_ae_lines

where ae_header_id in (71837 ,71836))

select (select gl_flexfields_pkg.get_description_sql (:char_of_account_id, 5, gcc.segment5) FROM gl_code_combinations gcc

where gcc.CODE_COMBINATION_ID =gil.CODE_COMBINATION_ID ) acc_desc

,gil.* from gl_je_lines gil

where je_header_id in (54036)

--------------------------------------------------------------------------------

select * from gl_code_combinations_kfv

where code_combination_id in (2012,3001)

select * from ap_suppliers

where vendor_id=2231

1)PO

2)Receipt

a) MTL transaction--->

Inventory Valuation dr

Receiving Inspection cr

3)AP INV DIST(standard Invoice)---

INVENTORY AP ACCRUAL

a) subledger accounting-->

1) Accural A/C Dr

To Liability A/C Cr

4) GL Lines --->

liability ac Dr

Cash Clearing Cr

OAF : VO Extension

EBS : Prepare Shell Scripts to download Concurrent program using PLSQL ( Oracle Apps)

Oracle Apps: How to Prepare shell scripts file to download all custom concurrent Program from the system using plsql

DECLARE

l_string varchar2(2000);

l_conc_prog_string varchar2(1000);

l_conc_prog_func_string varchar2(1000);

fileHandler UTL_FILE.FILE_TYPE;

ctlCode varchar2(2000);

l_cnt Number :=0;

CURSOR cur_conc_prog

SELECT concurrent_program_name conc_prog

FROM fnd_concurrent_programs

WHERE concurrent_program_name LIKE 'XX%'

ORDER BY concurrent_program_name;

BEGIN

-------------------------------Opeining File --------------------------------------------------

fileHandler := UTL_FILE.FOPEN('APPS_DATA_FILE_DIR', 'Conc_Prog_download_LDT'||'_run.sh', 'W');

--------------------------Start Priting Header Portion -----------------------------------------

l_string := ( '# +===================================================================+'||chr(10) ||

'# | |'||chr(10)||

'# |File : '||'Conc_Prog_download_LDT'||'_run.sh |'||chr(10)||

'# |Description : This file will install all front-end |'||chr(10)||

'# |Change History: |'||chr(10)||

'# |--------------- |'||chr(10)||

'# |Version Date Author Remarks |'||chr(10)||

'# |------- ----------- -------------- ----------------------------|'||chr(10)||

'# |1.0 '|| to_Char(sysdate)||' XX Initial Version |'||chr(10)||

'# +===================================================================+'||chr(10)

);

l_string := l_string ||chr(10)||('TIME_STAMP=`date +%Y%m%d%H%M`'||chr(10)||

'LOG_FILE=`echo XX_$TIME_STAMP".log"`'||chr(10)||

'TNS_DETAIL=`echo $HOSTNAME:$ORACLE_PORT:$ORACLE_SID` '||chr(10));

UTL_FILE.PUTF(fileHandler, l_string);

l_string := CHR(10) ||

'CHKLOGIN(){ '||CHR(10)||

'if sqlplus /nolog <<! >/dev/null 2>&1 ' ||CHR(10)||

' WHENEVER SQLERROR EXIT 1; ' ||CHR(10)||

' CONNECT $1 ; ' ||CHR(10)||

' EXIT; ' ||CHR(10)||

'!'||CHR(10)||

' then ' ||CHR(10)||

' echo OK ' ||CHR(10)||

' else ' ||CHR(10)||

' echo NOK ' ||CHR(10)||

' fi ' ||CHR(10)||

' } ' ||CHR(10)||CHR(10)||CHR(10);

UTL_FILE.PUTF(fileHandler, l_string);

l_string := CHR(10) ||

'APPS_LOGIN_ID="$1"'||CHR(10)||

'XX_LOGIN_ID="$2"'||CHR(10)||CHR(10)||CHR(10)||CHR(10);

UTL_FILE.PUTF(fileHandler, l_string);

l_string := '# *******************************************************************'||CHR(10)||

'# Check if APPS Login Id is entered else prompt to get it'||CHR(10)||

'# *******************************************************************'||CHR(10)||

'while [ "$APPS_LOGIN_ID" = "" -o `CHKLOGIN "$APPS_LOGIN_ID" "DUAL"` = "NOK" ]'||CHR(10)||

'do'||CHR(10)||

'if [ "$APPS_LOGIN_ID" = "" ];then'||CHR(10)||

'echo " Enter APPS schema Userid/Passwd (apps/apps): "'||CHR(10)||

'read APPS_LOGIN_ID'||CHR(10)||

'else'||CHR(10)||

'echo "Enter APPS Userid/Passwd (apps/apps): "'||CHR(10)||

'read APPS_LOGIN_ID'||CHR(10)||

'fi'||CHR(10)||

'done'||CHR(10)||

'APPS_LOGIN=`echo $APPS_LOGIN_ID | cut -d"/" -f1`'||CHR(10)||

'APPS_PWD=`echo $APPS_LOGIN_ID | cut -d"/" -f2`'||CHR(10);

UTL_FILE.PUTF(fileHandler, l_string);

l_string := '# *******************************************************************'||chr(10)||

'# Creating Concurrent_Prog in Apps'||chr(10)||

'# *******************************************************************';

UTL_FILE.PUTF(fileHandler, l_string);

-------------------------End Prinitng Header Portion ------------------------------------------

FOR i IN cur_conc_prog

LOOP

l_string := NULL;

l_conc_prog_string := NULL;

l_conc_prog_func_string := NULL;

----------------------------------------------- CREATING RUN SCRIPT-----------------------------------------------------------------------------------------

-------------------------------Start Printing Concurrent Program -------------------------------------------

l_string := chr(10)||chr(10)|| '# Start Deploying : *******************************'|| i.conc_prog||'_CC'||'************************************************************';

l_string := l_string ||chr(10)||chr(10)||chr(10)||'echo "Downloading ldt of Concurrent Programs '||i.conc_prog||'_CC.ldt in Apps ..." | tee -a $LOG_FILE'||CHR(10);

l_conc_prog_string := l_string ||CHR(10)||'$FND_TOP/bin/FNDLOAD $APPS_LOGIN_ID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ' ||i.conc_prog||'_CC.ldt PROGRAM APPLICATION_SHORT_NAME="'||'XX'||'" CONCURRENT_PROGRAM_NAME="'||i.conc_prog||'"';

UTL_FILE.PUTF(fileHandler,chr(10)|| l_conc_prog_string);

/* l_string := (CHR(10)||

'if [ $? = 0 ];' ||CHR(10)||

'then' ||CHR(10)||

'echo "Registration of conc_prog :- '||i.conc_prog_name||'_conc_prog.ldt successful " | tee -a $LOG_FILE' ||CHR(10)||

'else' ||CHR(10)||

'echo "Error in registration of conc_prog :- '||i.conc_prog_name||'_conc_prog.ldt Please correct and rerun" | tee -a $LOG_FILE' ||CHR(10)||

'exit' ||CHR(10)||

'fi' ||CHR(10));

UTL_FILE.PUTF(fileHandler, l_string);*/

-------------------------------END Printing conc_prog -------------------------------------------

l_cnt := l_cnt + 1;

END LOOP;

UTL_FILE.PUTF(fileHandler, chr(10)||chr(10)|| '#Number Of Concurrent Program Downloaded Succesfully :-'||l_cnt);

UTL_FILE.FCLOSE(fileHandler);

END;

Thursday, 25 March 2021

Oracle Apps Query : Query To get item details

select segment1, description, item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT,

-- category_set_name,

max(Inventory) Inventory ,

max(Product_Family) Product_Family,

max(MRP) MRP,

max(CST) CST,

max(PUR) PUR,

max(PRAMAC_PRODUCT_LINE) PRAMAC_PRODUCT_LINE,

max(QUALITY) QUALITY,

max(SALES) SALES,

max(INTRASTAT_Classification) INTRASTAT_Classification ,

max(Sales_Category_Subcategory ) Sales_Category_Subcategory,

max(PRAMAC_CLASS_MAPPING ) PRAMAC_CLASS_MAPPING,

max(MANUFACTURED_IN )MANUFACTURED_IN ,

max(LEAN) LEAN,

max(R&D_MAINTENANCE_BOM) R&D_MAINTENANCE_BOM,

max(Product_Categories) Product_Categories,

max(Sequence_of_Events)Sequence_of_Events,

max(Product_Family1) Product_Family1

from

(

SELECT distinct null source, A.ORGANIZATION_ID, a.segment1, a.description, NULL long_description, a.PRIMARY_UNIT_OF_MEASURE, null templates,

a.item_type,inventory_item_status_code,A.INVENTORY_ITEM_FLAG,A.STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

,b.category_set_name,a.inventory_item_id,

decode ( b.category_set_name,'Inventory',b.SEGMENT1) Inventory,

decode ( b.category_set_name,'Product Family',b.SEGMENT1) Product_Family,

decode ( b.category_set_name,'MRP',b.SEGMENT1) as "MRP",

decode ( b.category_set_name,'CST',b.SEGMENT1) as "CST",

decode ( b.category_set_name,'PUR',b.SEGMENT1) as "PUR",

decode ( b.category_set_name,'PRAMAC PRODUCT LINE',b.SEGMENT1) as "PRAMAC_PRODUCT_LINE",

decode ( b.category_set_name,'QUALITY',b.SEGMENT1) as "QUALITY",

decode ( b.category_set_name,'SALES',b.SEGMENT1) as "SALES",

decode ( b.category_set_name,'INTRASTAT Classification',b.SEGMENT1) INTRASTAT_Classification,

decode ( b.category_set_name,'Sales Category & Subcategory',b.SEGMENT1) Sales_Category_Subcategory,

decode ( b.category_set_name,'PRAMAC_CLASS_MAPPING',b.SEGMENT1) PRAMAC_CLASS_MAPPING,

decode ( b.category_set_name,'MANUFACTURED IN',b.SEGMENT1) MANUFACTURED_IN,

decode ( b.category_set_name,'LEAN',b.SEGMENT1) LEAN,

decode ( b.category_set_name,'R&D MAINTENANCE BOM',b.SEGMENT1) R&D_MAINTENANCE_BOM,

decode ( b.category_set_name,'Product Categories',b.SEGMENT1) Product_Categories,

decode ( b.category_set_name,'Sequence of Events',b.SEGMENT1) Sequence_of_Events,

decode ( b.category_set_name,'Product Family',b.SEGMENT2) Product_Family1

FROM mtl_system_items_b a, mtl_item_categories_v b

WHERE 1 = 1

AND a.inventory_item_status_code = 'Active'

AND a.inventory_item_id = b.inventory_item_id

AND a.organization_id = b.organization_id

AND a.organization_id = 11

) xx

group by segment1, description, xx.inventory_item_id, item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

OAF : How to add Attachment Link in OAF Page through Personlization : Oracle APPS

How to create Attachment link through OAF Personalization

OAF Page for Customization = /oracle/apps/per/selfservice/extrainfotype/webui/EitPG

1) Define Entity

a) Go to Applciation Developer

a) Enter Entity Data

Table := XX_PER_ARREAR_UPLOAD_STG

Entity Id := PERSON_ID

Entity Name := PERSON_ID

Prompt := PERSON_ID

Application := Human Resources

1) 2) OAF Personalization to create Attachment Link

a) a) Add Attachment Link Attribute

b) Entity mapping

c) Entity Primary Key mapping with VO Attribute

d) Fields added as shown below

------------------------------------------------------------------------------------------------------------------

Below Steps need to perform at Database

CREATE TABLE XX_PER_ARREAR_UPLOAD_STG

(PERSON_ID NUMBER,

MEDIA_ID VARCHAR2(100),

FILE_NAME VARCHAR2(240),

message varcha2(2000)

);

6) 2) Create Procedures :- XX_PER_ARREAR_FILE_UPLOAD

3) 3) Check correct oracle directory and define in procedure

8) 4) Run the Scripts to upload file

dPProcedure :- XX_PER_ARREAR_FILE_UPLOAD

CREATE OR REPLACE PROCEDURE XX_PER_ARREAR_FILE_UPLOAD

(P_EMP_NO VARCHAR2)

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 := 'Arrear Attachment';

l_filename VARCHAR2 (240) := p_emp_no||'.xls';

--l_file_path VARCHAR2 (240) := 'test';

---'SALE_INVOICE_PATH'; --Server Directory Path for upload files

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) := 'PERSON_ID';

l_oracle_directory varchar2(100):= 'ECX_UTL_LOG_DIR_OBJ';

l_content_type varchar2(100):= 'application/vnd.ms-excel';

--application/vnd.ms-excel--application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

--- <entity_name>;

l_category_name VARCHAR2 (100) := 'Miscellaneous';--XX_ARREAR_FILE_UPLOAD';

--<category_name>;

l_message varchar2(2000);

BEGIN

bEGIN

select DISTINCT PERSON_ID INTO l_pk1_value from per_all_people_f

where employee_number =P_EMP_NO;

END;

insert into XX_PER_ARREAR_UPLOAD_STG (person_id) values(l_pk1_value);

--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 = 'TECHHARDS'; -- <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

--FATEMP

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 (l_oracle_directory, l_filename);

--dbms_output.put_line('fils:'||fils);

-- 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, /*'text/plain',*/ l_content_type,

--'application/pdf',--

SYSDATE,

NULL, 'FNDATTCH', NULL, EMPTY_BLOB ()

,'US', /*'UTF8'*/

/* 'AR8MSWIN1256' */ 'WE8MSWIN1252',--'AR8ISO8859P6',

'binary'

/*'text'*/

)

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 => -1,

x_datatype_id => l_short_datatype_id, -- FILE

x_security_id => NULL,

--<security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

x_publish_flag => /*'N'*/ 'Y',

--This flag allow the file to share across multiple organization

x_category_id => l_category_id,

x_security_type => /*1*/ 4,

x_usage_type => /*'S'*/ 'O',

x_language => 'US',

x_description => l_filename,

x_file_name => l_filename,

x_media_id => l_media_id

);

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 => -1,

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*/ 4,

x_security_id => NULL,

--<security ID defined in your Attchmentsssss, 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

);

update XX_PER_ARREAR_UPLOAD_STG

set media_id = l_media_id,

file_name = l_filename,

message='SUCCESSFUL'

where person_id =l_pk1_value;

COMMIT;

DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

Exception when others then

l_message := SQLERRM;

update XX_PER_ARREAR_UPLOAD_STG

set media_id = l_media_id,

file_name = l_filename,message = l_message

where person_id =l_pk1_value;

END;

--=========================================================================

# OAF , # OAF Personalization , # Add Attachment Link through Personalization , # Oracle Apps , # EBS

Oracle Technology

Total Pageviews

Pages

Friday, 23 April 2021

TAR File : How to Create TAR FILE

RTF Template Function and XML method TAG

Saturday, 3 April 2021

OPM : API : How to upload GMD Specification validity for Customer

Monday, 29 March 2021

Oracle Apps Query : how to Find DFF Flex Fields Attributes

EBS : Query to find Operating units in Oracle Apps

Oracle Apps : how to delete Concurrent request/Executables

Oracle Apps Query P2P-GL: PO to Gl Link Query and details

OAF : VO Extension

EBS : Prepare Shell Scripts to download Concurrent program using PLSQL ( Oracle Apps)

Oracle Apps: How to Prepare shell scripts file to download all custom concurrent Program from the system using plsql

Thursday, 25 March 2021

Oracle Apps Query : Query To get item details

OAF : How to add Attachment Link in OAF Page through Personlization : Oracle APPS

How to create Attachment link through OAF Personalization

FUSION: Purchasing Query

Report Abuse