How to add Barcode in rtf Template Report
1) Create form felds , change the font and write below syntax in the field properties.
2) Font uploading and mapping in xml publisher Administrator
Sunday, 17 October 2021
Thursday, 12 August 2021
WEBADI : How to add Multiple column in LOV
How to add Multiple column in LOV WEBADI
1) Go to Components
2) Click on Update and modify as shown below. Click on apply.
table-column-alias=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID
table-columns=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID
table-headers=ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID
3) Same column mapping need to be provided on description LOV section of Integrator Interface as shown below
description =ORGANIZATION_CODE,ORGANIZATION_NAME,ORGANIZATION_ID
3) Table Value set view
Output
Tuesday, 6 July 2021
Oracle APPS: AP : Query to get Document sequence Name and generate value
How to get document Sequence name
1) Write query to get db sequence name
SELECT seq.db_sequence_name
-- INTO l_sequence_name
FROM fnd_document_sequences seq, fnd_doc_sequence_assignments sa
WHERE seq.doc_sequence_id = sa.doc_sequence_id
AND sa.application_id = 200
AND sa.category_code ='AP Invoice'-- :l_document_name
AND (sa.method_code = 'A' )
2) get sequence value using below query
BEGIN
l_query :=
'select ' || l_sequence_name || '.nextval into :next_val from sys.dual';
EXECUTE IMMEDIATE l_query
INTO l_doc_sequence_value;
END;
return l_doc_sequence_value;
Friday, 2 July 2021
Oracle APPS : AP : Due Date Derivation Logic based on Payment Terms
1) Payment Terms
2) Special Calendars
Navigation:- Below Screenshot
-------------------------------------Query--------------------------------------------
SELECT apt.NAME,atl.calendar, atl.fixed_date, atl.due_day_of_month, atl.due_days
FROM ap_terms apt, ap_terms_lines atl
WHERE apt.term_id = atl.term_id
AND apt.NAME in ('RTEST')
select * from AP_OTHER_PERIOD_TYPES
where MODULE ='PAYMENT TERMS'
and period_type =:lv_payment_term
select due_date from AP_OTHER_PERIODS
where MODULE ='PAYMENT TERMS'
and period_type =:lv_payment_term
and :p_invoice_date between start_date and end_date
--------------------------------------------------------------------------------------------------------------------------
Logic Explained to derive Due date based on Invoice Date and Payment Term
Wednesday, 30 June 2021
Oracle APPS : HRMS : Query
HRMS Query
1) How to fetch Employee Latest Grade?
SELECT paaf.full_name || ' ' || paaf.employee_number emp_name,
paaf.person_id emp_id, pg.NAME grade
FROM per_all_people_f paaf,
per_all_assignments_f pa,
per_grades pg,
fnd_user fu
WHERE paaf.person_id = pa.person_id
AND paaf.effective_end_date IN (SELECT MAX (paafs.effective_end_date)
FROM per_all_people_f paafs
WHERE paafs.person_id = paaf.person_id)
AND pa.effective_end_date IN (
SELECT MAX (paaf.effective_end_date)
FROM per_all_assignments_f paafs
WHERE paafs.assignment_id = pa.assignment_id
AND paafs.person_id = pa.person_id)
AND pa.grade_id(+) = pg.grade_id
AND pa.person_id = fu.employee_id
AND fu.user_id = :UserId;
2)
Wednesday, 9 June 2021
Oracle Apps Query : How to delete Concurrent Program through API
begin
fnd_program.delete_program('XX_EMP_COST_ITAX','Payroll');
fnd_program.delete_executable(
end;
Tuesday, 1 June 2021
Oracle : SQL Query : Special Character, Substr
1) How to remove Special Characters
"REPLACE(REPLACE(TRIM(REGEXP_REPLACE(:FREIGHT_TERMS_CODE,'[^a-z_A-Z ]')),CHR(11),''),CHR(13),'')"
2) How to remove last 2 character from String
substr( xpem.period_name,1,length(xpem.period_name)-2)
3) Query to get Sunday?
select
(next_day(last_day(trunc(sysdate)),'?')-7
-next_day(trunc(sysdate,'mm')-1,'?'))/7+1
as "sundays"
from dual;
SELECT TO_CHAR(dat,'DD-MON-RRRR')
FROM
(SELECT TRUNC(SYSDATE,'MM') + level - 1 dat FROM dual
connect by level <= LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE,'MM') + 1)
WHERE TO_CHAR(dat,'DY') = 'SUN'
GROUP BY TO_CHAR(dat,'MON-RRRR')
/* Formatted on 2018/04/06 15:18 (Formatter Plus v4.8.8) */
SELECT TRUNC (:p_to_dat, 'MM') + LEVEL - 1 dat,TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') days,
CASE
WHEN TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') =
'SUN'
THEN 'Holiday'
END abc
FROM DUAL
CONNECT BY LEVEL <= LAST_DAY (TRUNC (:p_to_dat)) - TRUNC (:p_to_dat, 'MM') + 1
------------------------------------------------------End Query to get sunday--------------------
Thursday, 27 May 2021
Oracle Apps Technical Interview Question : Workflow
Workflow
1) How to Migrate Workflow ?
2) What is the Standard Process to Customize Seeded Workflow?
3) How to Customize Seeded Workflow that after patch it wont get remove.
4) How to Analyse Workflow Errors?
5) How to Launch Workflow from PLSQL?
6) Error Tables for Workflow?
7) Have u ever Customized Seeded Workflow and How?
8) How to hide Action History from Notifcations Page?
Ans:- Need to set null to #History Attributes
Oracle Apps Technical Interview Question : PLSQL
Oracle Apps Technical Interview Question : PLSQL
1) What is Exception? User Defined Exception
2) Can we write commit in Trigger? How ?
3) Is commit Required to submit request from Trigger?
4) Commit/RollBack, Save Point?
5) Difference between VARRAY and Record Type? Advantage of both?
6) What is object types?
7) What are the attributes of PLSQL Table.
Ans:- .extend, .count, .delete, .prior, .next, .first, .last
8) What is the Difference between ROWType and Record Type?
9) What is Bulk Collect and its exception types, loop
10) What happen when Expception “When Others Then” placed above “When No_Data_Found”?
Ans:- It will Raise Exception.
EBS : OPM : API to update Batch Step Qty
/* Formatted on 2021/05/27 20:12 (Formatter Plus v4.8.8) */
DECLARE
x_batch_step_rec gme_batch_steps%ROWTYPE;
p_batch_step_rec gme_batch_steps%ROWTYPE;
p_validation_level NUMBER DEFAULT 100;
x_message_count NUMBER;
x_return_status VARCHAR2 (240);
x_message_list VARCHAR2 (240);
BEGIN
fnd_global.apps_initialize (1536, 23326, 553);
p_batch_step_rec.batchstep_no := 11;
p_batch_step_rec.oprn_id := 74087;
p_batch_step_rec.actual_start_date := '01-MAY-21';
p_batch_step_rec.actual_cmplt_date := '03-MAY-21';
-- p_batch_step_rec.BATCH_ID:=466340;
p_batch_step_rec.batchstep_id := 334733;
p_batch_step_rec.actual_step_qty := 3;
gme_api_pub.update_batchstep_qty
(p_api_version => 1.0,
p_validation_level => p_validation_level,
p_init_msg_list => 'F',
p_commit => 'T',
p_org_code => 'KT',
p_batch_no => '20004203'
--,P_ACTUAL_START_DATE='3-MAY-2021'
-- ,p_add =>'N'
,
p_batch_step_rec => p_batch_step_rec,
x_batch_step_rec => x_batch_step_rec,
x_message_count => x_message_count,
x_message_list => x_message_list,
x_return_status => x_return_status
);
COMMIT;
DBMS_OUTPUT.put_line ( x_batch_step_rec.actual_cmplt_date
|| ','
|| x_batch_step_rec.actual_start_date
);
DBMS_OUTPUT.put_line ( 'x_return_status : '
|| x_return_status
|| ' x_message_list: '
|| x_message_list
);
IF x_message_list IS NULL
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END IF;
END;
Sunday, 23 May 2021
Oracle Apps Query : Form Personalization related query
1) Form Personlization header table
select rule_key,function_name,sequence ,rule_type,enabled,a.*
from fnd_form_custom_rules a
where form_name ='APXINWKB'
2) Form Personalization Action Table
select id, rule_key,function_name,a.sequence ,rule_type,TRIGGER_EVENT, trigger_object,b.*
from fnd_form_custom_rules a ,FND_FORM_CUSTOM_ACTIONS b
where a.ID = b.RULE_ID
and form_name ='APXINWKB'
Saturday, 22 May 2021
Windows File rename: How to rename bulk file through CMD mode
Windows File rename: How to rename bulk file through CMD mode
1) Open CMD and go to specific directory
2) type below command to display list of files
dir /b
3) Select all files and copy,
4) Paste the file in Excel and build the dos rename syntax as shown below
6) Multiple bulk files renamed
Monday, 26 April 2021
OPM : Query to get Item wise cost in OPM
OPM : Query to get Item wise cost in OPM
SELECT ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group,
SUM (NVL (cmpnt_cost, 0)) costs
--ccm.cost_cmpntcls_code,
FROM cm_cmpt_dtl_vw ccdv,
cm_cldr_mst_v ccmv,
cm_cmpt_mst ccm,
mtl_parameters mp,
org_organization_definitions ood,
gl_code_combinations gcc,
gmf_fiscal_policies gfp
WHERE 1 = 1
AND ccdv.period_id = ccmv.period_id
AND TRUNC (ccmv.start_date) >= :p_start_date
AND TRUNC (ccmv.end_date) <= :p_end_date
AND ccmv.calendar_code = :p_calendar_code
AND ccdv.cost_cmpntcls_id = ccm.cost_cmpntcls_id
AND ccmv.cost_type_id = gfp.cost_type_id
AND ccdv.organization_id = mp.organization_id
AND mp.organization_id = ood.organization_id
AND mp.material_account = gcc.code_combination_id
AND gcc.segment1 BETWEEN :p_child_low AND :p_child_high
AND (:p_business_unit IS NULL OR gcc.segment2 = :p_business_unit)
AND ood.legal_entity = gfp.legal_entity_id
GROUP BY ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group
Friday, 23 April 2021
OAF : Migration Command
OAF : Migration Command
java oracle.jrad.tools.xml.
RTF Template Function and XML method TAG
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
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
IS
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)||
'# | |'||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
c) Entity Primary Key mapping with VO Attribute
d) Fields added as shown below
------------------------------------------------------------------------------------------------------------------
Below Steps need to perform at Database
1)
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)
IS
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
Subscribe to:
Posts (Atom)