oracleapps world

Tuesday, 29 March 2022

External Table in Oracle EBS

An external table is a table whose data come from flat files stored outside of the database.

No DML can be performed on external tables but they can be used for query, join and sort operations

Example:-

CREATE TABLE "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD"

( "COL1" VARCHAR2(150 BYTE),

"COL2" VARCHAR2(150 BYTE),

"COL3" VARCHAR2(150 BYTE),

"COL4" VARCHAR2(150 BYTE),

"COL5" VARCHAR2(150 BYTE),

"COL6" VARCHAR2(150 BYTE),

"COL7" VARCHAR2(150 BYTE),

"COL8" VARCHAR2(150 BYTE),

"COL9" VARCHAR2(150 BYTE),

"COL10" VARCHAR2(150 BYTE),

"COL11" VARCHAR2(150 BYTE),

"COL12" VARCHAR2(150 BYTE),

"COL13" VARCHAR2(150 BYTE),

"COL14" VARCHAR2(150 BYTE)

)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY "GP_CONV"

ACCESS PARAMETERS

( records delimited by newline

badfile GP_CONV:'xxunv_ap_invoice_upload_bad.txt'

logfile GP_CONV:'xxunv_ap_invoice_upload_log.txt'

fields terminated by ',' optionally enclosed by '"'

(

col1 ,

col2 ,

col3 ,

col4 ,

col5 ,

col6 ,

col7 ,

col8 ,

col9 ,

col10 ,

col11 ,

col12 ,

col13 ,

col14 )

)

LOCATION

( "GP_CONV":'xxunv_ap_invoice_upload.CSV'

)

REJECT LIMIT UNLIMITED ;

GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "REPORTING";

GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "READONLYAPPS";

GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "APPS";

GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "XXUNV";

Delete the duplicate data in oracle

Here the program ran twice and the data has been loaded twice. Now the below query removes the duplicates.

DELETE FROM XXUNV.XXUNV_FIS_AR_PAYMENTS_INT_ALL

WHERE rowid IN(

SELECT MAX(rowid)

FROM XXUNV.XXUNV_FIS_AR_PAYMENTS_INT_ALL

WHERE 1=1

AND TRUNC (creation_date) = TO_DATE ('20210821', 'YYYYMMDD')

AND payment_id IN(

SELECT payment_id

FROM XXUNV.XXUNV_FIS_AR_PAYMENTS_INT_ALL

WHERE 1=1

AND TRUNC (creation_date) = TO_DATE ('20210821', 'YYYYMMDD')

GROUP BY payment_id

HAVING COUNT(*) > 1)

GROUP BY payment_id);

COMMIT;

DELETE FROM XXUNV.XXUNV_FIS_AR_PAY_HIST_ALL

WHERE rowid IN(

SELECT MAX(rowid)

FROM XXUNV.XXUNV_FIS_AR_PAY_HIST_ALL

WHERE 1=1

AND TRUNC (creation_date) = TO_DATE ('20210821', 'YYYYMMDD')

AND payment_id IN(

SELECT payment_id

FROM XXUNV.XXUNV_FIS_AR_PAY_HIST_ALL

WHERE 1=1

AND TRUNC (creation_date) = TO_DATE ('20210821', 'YYYYMMDD')

GROUP BY payment_id

HAVING COUNT(*) > 1)

GROUP BY payment_id);

COMMIT;

Thursday, 24 March 2022

Query for AP Details Audit Report

<?xml version = '1.0' encoding = 'UTF-8'?>

</parameters>

<![CDATA[

SELECT

ap.check_date check_date,

ap.payment_method_code,

ap.payment_amount,

ap.bank_account_name,

ap.check_id,

ap.check_number,

ap.status_lookup_code,

ap.creation_date creation_date,

ap.currency_code,

ap.vendor_name,

ap.vendor_id,

ap.segment1,

ap.vendor_site_code,

ap.address_line1,

ap.address_line2,

ap.address_line3,

ap.city,

ap.state,

ap.zip,

ap.invoice_amount,

ap.invoice_date invoice_date,

ap.invoice_id,

ap.invoice_num,

ap.last_updated_by,

ap.updated_by,

ap.payment_terms,

ap.sob_desc,

po.po_number,

po.buyer,

po.category,

po.category2,

po.location_code,

po.address_line_1,

po.town_or_city,

po.postal_code,

po.state state1,

ap.invoice_distribution_id,

ap.amount,

ap.accounting_date accounting_date,

ap.account_code,

ap.company,

ap.Company_Name,

ap.account,

ap.account_name,

ap.department department,

ap.department_name,

ap.program program,

ap.program_name,

ap.responsibility responsibility,

ap.responsibility_name,

ap.intercompany intercompany,

ap.tbd tbd,

ap.cleared_date cleared_date,

ap.project_number,

ap.task_number,

ap.image_url,

TO_CHAR(SYSDATE,'DDMONYYHHMMSS') sysdate1

FROM

(

SELECT

ac.check_date check_date,

ac.amount Payment_Amount,

ac.bank_account_name,

ac.check_id,

ac.check_number,

ac.creation_date creation_date,

ac.currency_code,

ac.status_lookup_code,

api.invoice_amount,

api.invoice_date invoice_date,

api.invoice_id,

api.invoice_num,

api.last_updated_by,

f.description Updated_By,

(SELECT name

FROM ap.ap_terms_tl apt

WHERE apt.language = 'US'

AND apt.term_id = api.terms_id) Payment_terms,

gll.name SOB_Desc,

api.payment_method_code,

aps.vendor_name,

aps.vendor_id,

aps.segment1,

apss.vendor_site_code,

apss.address_line1,

apss.address_line2,

apss.address_line3,

apss.city,

apss.state,

apss.zip,

apd.invoice_distribution_id,

apd.amount,

apd.accounting_date accounting_date,

(gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7) Account_Code,

gcc.segment1 Company,

(SELECT dvl.description

FROM apps.fnd_flex_value_sets dv,

apps.fnd_flex_values_vl dvl

WHERE dv.flex_value_set_name = 'UVN_GL_COMPANY'

AND dv.flex_value_set_id = dvl.flex_value_set_id

AND dvl.flex_value = gcc.segment1) company_name,

gcc.segment2 Account,

(SELECT dvl.description

FROM apps.fnd_flex_value_sets dv,

apps.fnd_flex_values_vl dvl

WHERE dv.flex_value_set_name = 'UVN_GL_NATURAL_ACCOUNT'

AND dv.flex_value_set_id = dvl.flex_value_set_id

AND dvl.flex_value = gcc.segment2) account_name,

gcc.segment3 Department,

(SELECT dvl.description

FROM apps.fnd_flex_value_sets dv,

apps.fnd_flex_values_vl dvl

WHERE dv.flex_value_set_name = 'UVN_GL_DEPARTMENT'

AND dv.flex_value_set_id = dvl.flex_value_set_id

AND dvl.flex_value = gcc.segment3) department_name,

gcc.segment4 Program,

(SELECT dvl.description

FROM apps.fnd_flex_value_sets dv,

apps.fnd_flex_values_vl dvl

WHERE dv.flex_value_set_name = 'UVN_GL_PROGRAM_EVENTS'

AND dv.flex_value_set_id = dvl.flex_value_set_id

AND dvl.flex_value = gcc.segment4) program_name,

gcc.segment5 Responsibility,

(SELECT dvl.description

FROM apps.fnd_flex_value_sets dv,

apps.fnd_flex_values_vl dvl

WHERE dv.flex_value_set_name = 'UVN_GL_RESP_CENTER'

AND dv.flex_value_set_id = dvl.flex_value_set_id

AND dvl.flex_value = gcc.segment5) responsibility_name,

gcc.segment6 Intercompany,

gcc.segment7 TBD,

apd.po_distribution_id,

ac.cleared_date cleared_date,

api.attribute7 image_url,

ppt.project_number,

ppt.task_number

FROM

ap.ap_checks_all ac

INNER JOIN ap.ap_invoice_payments_all aip

ON( ac.check_id = aip.check_id

AND ac.status_lookup_code <> 'VOIDED'

AND ac.check_date >= TO_DATE(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS')

AND ac.check_date <= TO_DATE(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS') )

INNER JOIN ap.ap_invoices_all api

ON(api.invoice_id = aip.invoice_id

--AND api.set_of_Books_id = aip.set_of_Books_id

AND api.org_id = aip.org_id

--AND Api.Set_Of_Books_Id = '1001'

AND api.org_id = FND_PROFILE.VALUE('ORG_ID')

)

INNER JOIN ap.ap_invoice_distributions_all apd

ON( api.invoice_id = apd.invoice_id

AND apd.set_of_Books_id = api.set_of_Books_id)

INNER JOIN gl.gl_code_combinations gcc

ON(apd.dist_code_combination_id = gcc.code_combination_id)

INNER JOIN ap.ap_suppliers aps

ON(aps.vendor_id = api.vendor_id)

INNER JOIN ap.ap_supplier_sites_all apss

ON( apss.vendor_id = aps.vendor_id

AND apss.vendor_site_id = api.vendor_site_id)

INNER JOIN gl.gl_ledgers gll

ON( Gll.Ledger_Id = Api.Set_Of_Books_Id)

INNER JOIN fnd_user f

ON( f.user_id = api.last_updated_by )

LEFT JOIN (SELECT pp.project_id,

pp.segment1 project_number,

pt.task_id,

pt.task_number

FROM pa.pa_projects_all pp,

pa.pa_tasks pt

WHERE pp.project_id = pt.project_id) ppt

ON (apd.project_id = ppt.project_id

AND apd.task_id = ppt.task_id)

WHERE 1 = 1

AND NVL(gcc.segment3,'X') = NVL(:P_DEP_CODE,NVL(gcc.segment3,'X'))

AND NVL(apss.state, 'X')= NVL(:P_VENDOR_STATE, NVL(apss.state,'X'))

AND aps.segment1 = NVL(:P_VENDOR_NUM,aps.segment1)

) ap

LEFT OUTER JOIN (

SELECT

poh.segment1 po_number,

f2.description buyer,

cat.segment1 category,

cat.segment2 category2,

hrl.location_code,

hrl.address_line_1,

hrl.address_line_2,

hrl.address_line_3,

hrl.town_or_city,

hrl.postal_code,

hrl.region_2 state,

pod.po_distribution_id po_dist,

f2.user_id user_id

FROM po.po_headers_all poh

INNER JOIN po.po_lines_all pll

ON(poh.po_header_id = pll.po_header_id)

INNER JOIN po.po_distributions_all pod

ON(pod.po_line_id = pll.po_line_id)

INNER JOIN fnd_user f2

ON( f2.user_id = poh.created_by)

INNER JOIN inv.mtl_categories_b cat

ON(cat.category_id = pll.category_id)

INNER JOIN hr.hr_locations_all hrl

ON( hrl.location_id = pod.deliver_to_location_id )

WHERE 1=1

) po ON ap.po_distribution_id = po.po_dist

WHERE 1=1

AND NVL(po.user_id,1) = NVL(:P_PO_BUYER, NVL(po.user_id,1))

AND NVL(po.state, 'X')= NVL(:P_DELIVER_STATE, NVL(po.state,'X'))

AND NVL(po.po_number,1) = NVL(:P_PO_NUM, NVL(po.po_number,1))

AND NVL(UPPER(po.category), 'X') = NVL(UPPER(:P_INV_MTL_CAT1), NVL(UPPER(po.category),'X'))

AND NVL(UPPER(po.category2),'X') = NVL(UPPER(:P_INV_MTL_CAT2),NVL(UPPER(po.category2),'X'))

]]>

</sqlStatement>

</dataQuery>

</group>

</dataStructure>

</dataTemplate>

Sunday, 6 March 2022

Steps to create custom table in Oracle apps R12.2.X

For new table creations we have to follow the below command.

After creating the table in custom schema execute below script to generate editoning view and synonym for it in APPS schema.

exec AD_ZD_TABLE.UPGRADE('XXUNV','XXUNV_DISPUTE_REQ_HDRS');

When you altered the table after the table upgrade command.

Run the below statement.

exec AD_ZD_TABLE.PATCH('XXUNV','XXUNV_DISPUTE_REQ_HDRS');

Wednesday, 9 February 2022

How to Spool the data in SQL developer tool

set SERVEROUTPUT ON SIZE 1000000

set headsep off

set pagesize 0

set trimspool on

set lines 240

spool E:\prebill.csv

select trx_number, trx_date from ra_customer_trx_all where trunc(trx_date) between trunc(sysdate-1) and trunc(sysdate);

spool off;

Note:- Select all the script and press F5, it will generate the output in E Drive with file name as prebill.csv

Thursday, 3 February 2022

Duplicate Invoice number cannot be completed or voided in Oracle

Requirement:- We have an issue, 2 users created the AR Invoice with the Same Invoice number and same gl_date and Unable to open the form as it is saying "Duplicate invoice number cannot be completed or voided in Oracle".

CREATE TABLE xx_delete_transaction

SELECT customer_trx_id, trx_date,

trx_number,

bill_to_customer_id,

bill_to_site_use_id,

org_id,

creation_date,

created_by,

'N' v_ret_status,

'N' v_msg_count,

'N' v_msg_data,

'N' v_message_tbl

FROM ra_customer_trx_all

WHERE org_id = 103 AND customer_trx_id = '2800613' ;

DECLARE

CURSOR cur_all_trx

SELECT ROWID,

NULL party_number,

rct.org_id,

rct.customer_trx_id,

rct.trx_number

FROM xx_delete_transaction rct

WHERE NVL (V_RET_STATUS, 0) <> 'S';

xv_msg_data VARCHAR2 (4000) := NULL;

xv_msg_count NUMBER := 0;

v_msg_index NUMBER := 0;

xv_ret_status VARCHAR2 (1) := NULL;

v_message_tbl arp_trx_validate.message_tbl_type;

v_res VARCHAR2 (4000) := NULL;

v_res_name VARCHAR2 (4000) := NULL;

v_app VARCHAR2 (4000) := NULL;

v_user NUMBER := 0;

BEGIN

DBMS_OUTPUT.put_line ('Detele Transaction...');

FOR c_rec IN cur_all_trx

LOOP

DBMS_OUTPUT.put_line (' Transaction No.: ' || c_rec.trx_number);

DBMS_OUTPUT.put_line (' Transaction ID : ' || c_rec.customer_trx_id);

DBMS_OUTPUT.put_line (' Org ID : ' || c_rec.org_id);

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

---- Setting the org context for the particular session

apps.mo_global.set_policy_context ('S', c_rec.org_id);

-- apps.mo_global.init('AR');

SELECT application_id, responsibility_id

INTO v_app, v_res

FROM fnd_responsibility_tl

WHERE responsibility_id = 50331

and Language ='US';

apps.fnd_global.apps_initialize (v_user, v_res, v_app);

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

xv_ret_status := NULL;

xv_msg_count := NULL;

xv_msg_data := NULL;

UPDATE ar_system_parameters_all

SET invoice_deletion_flag = 'Y'

WHERE org_id = c_rec.org_id;

ar_invoice_api_pub.delete_transaction (

p_api_name => 'Delete_Transaction',

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_customer_trx_id => c_rec.customer_trx_id,

p_return_status => xv_ret_status,

p_msg_count => xv_msg_count,

p_msg_data => xv_msg_data,

p_errors => v_message_tbl);

UPDATE xx_delete_transaction

SET v_ret_status = xv_ret_status

WHERE ROWID = c_rec.ROWID;

UPDATE xx_delete_transaction

SET v_msg_count = xv_msg_count

WHERE ROWID = c_rec.ROWID;

IF xv_ret_status <> 'S'

THEN

DBMS_OUTPUT.put_line (' Status: ' || xv_ret_status);

UPDATE xx_delete_transaction

SET v_msg_data = v_ret_status

WHERE ROWID = c_rec.ROWID;

FOR i IN 1 .. xv_msg_count

LOOP

apps.fnd_msg_pub.get (i,

apps.fnd_api.g_false,

xv_msg_data,

v_msg_index);

DBMS_OUTPUT.put_line (' Error : ' || xv_msg_data);

END LOOP;

DBMS_OUTPUT.put_line (' ' || xv_msg_data);

ELSE

DBMS_OUTPUT.put_line (' Deleted.');

-- Revert back to the original value for the deletion flag

UPDATE ar_system_parameters_all

SET invoice_deletion_flag = 'N'

WHERE org_id = c_rec.org_id;

END IF;

DBMS_OUTPUT.put_line ('--------------------');

COMMIT;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);

END;

How to create a Custom Lookup Code and Lookup Values in AR_LOOKUPS table from backend using API

Requirement: There is a lookup in fnd lookups table, business wants that lookup to be modified by them, adding lookup values, modifying and disabling them.

Instead of giving the FND_LOOKUPS function, we want to give them the AR Lookups function for them to modify the lookup values.

Below is the Anonymous block, we have written to get the values from FND Lookups table to AR Lookups table.

DECLARE

ln_rowid VARCHAR2 (1000);

ln_rowid1 VARCHAR2 (1000);

BEGIN

fnd_lookup_types_pkg.insert_row (x_rowid => ln_rowid,

x_lookup_type => 'XX_PO_BOXES',

x_security_group_id => 0,

x_view_application_id => 222,

x_application_id => 222,

x_customization_level => 'U',

x_meaning => 'Lookup for PO Boxes',

x_description => 'Lookup for PO Boxes',

x_creation_date => SYSDATE,

x_created_by => 0,

x_last_update_date => SYSDATE,

x_last_updated_by => 0,

x_last_update_login => -1

);

COMMIT;

for i in (select lookup_code,

start_date_active,

end_date_active,

tag,

meaning,

description

from fnd_lookup_values_vl

where lookup_type = 'XX_PO_BOXES'

)

loop

fnd_lookup_values_pkg.insert_row (x_rowid => ln_rowid1,

x_lookup_type => 'XX_PO_BOXES',

x_security_group_id => 0,

x_view_application_id => 222,

x_lookup_code => i.lookup_code,

x_tag => i.tag,

x_attribute_category => NULL,

x_attribute1 => NULL,

x_attribute2 => NULL,

x_attribute3 => NULL,

x_attribute4 => NULL,

x_enabled_flag => 'Y',

x_start_date_active => i.start_date_active,

x_end_date_active => i.end_date_active,

x_territory_code => NULL,

x_attribute5 => NULL,

x_attribute6 => NULL,

x_attribute7 => NULL,

x_attribute8 => NULL,

x_attribute9 => NULL,

x_attribute10 => NULL,

x_attribute11 => NULL,

x_attribute12 => NULL,

x_attribute13 => NULL,

x_attribute14 => NULL,

x_attribute15 => NULL,

x_meaning => i.meaning,

x_description => i.description,

x_creation_date => SYSDATE,

x_created_by => 0,

x_last_update_date => SYSDATE,

x_last_updated_by => 0,

x_last_update_login => -1

);

COMMIT;

end loop;

EXCEPTION WHEN NO_DATA_FOUND THEN

dbms_output.put_line('No Data Found');

WHEN OTHERS THEN

NULL;

END;