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;

Query for AP Details Audit Report

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

<dataTemplate name="XXUNVAPDETAUDRPT" defaultPackage="XXUNV_AP_DET_RPT_BURST_PKG" description="XXUNV AP Details Audit Report" version="1.0">

<properties><property name="db_fetch_size" value="400" /></properties> 

   <parameters> 

    <parameter name="P_FROM_DATE"           dataType="character"/>

    <parameter name="P_TO_DATE"            dataType="character"/>   

<parameter name="P_COMP_CODE"           dataType="character"/>

<parameter name="P_DEP_CODE"            dataType="character"/>

<parameter name="P_INV_MTL_CAT1"        dataType="character"/>

<parameter name="P_INV_MTL_CAT2"        dataType="character"/>

<parameter name="P_PO_BUYER"            dataType="number"/>

<parameter name="P_VENDOR_STATE"        dataType="character"/>

<parameter name="P_DELIVER_STATE"      dataType="character"/>

<parameter name="P_PO_NUM"            dataType="character"/>

<parameter name="P_VENDOR_NUM"          dataType="character"/>

    <parameter name="P_EMAIL"      dataType="character"/>   

   </parameters> 

  <dataQuery>

  <sqlStatement name="Q1">

  <![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> 

  <dataTrigger name="beforeReport" source="XXUNV_AP_DET_RPT_BURST_PKG.beforeReport"/>   

  <dataStructure>

  <group name="G_AP_DTLS" source="Q1">   

<element name="CHECK_DATE"                      value="CHECK_DATE"/>

<element name="PAYMENT_METHOD_CODE"            value="PAYMENT_METHOD_CODE"/>

<element name="PAYMENT_AMOUNT"                  value="PAYMENT_AMOUNT"/>   

<element name="BANK_ACCOUNT_NAME"               value="BANK_ACCOUNT_NAME"/>

<element name="CHECK_ID"                        value="CHECK_ID"/>

<element name="CHECK_NUMBER"                    value="CHECK_NUMBER"/>

<element name="STATUS_LOOKUP_CODE"              value="STATUS_LOOKUP_CODE"/>

<element name="CREATION_DATE"                   value="CREATION_DATE"/>

<element name="CURRENCY_CODE"                   value="CURRENCY_CODE"/>

<element name="VENDOR_NAME"                     value="VENDOR_NAME"/>

<element name="VENDOR_ID"                       value="VENDOR_ID"/>

<element name="SEGMENT1"                        value="SEGMENT1"/>

<element name="VENDOR_SITE_CODE"                value="VENDOR_SITE_CODE"/>

<element name="ADDRESS_LINE1"                   value="ADDRESS_LINE1"/>

<element name="ADDRESS_LINE2"                   value="ADDRESS_LINE2"/>

<element name="ADDRESS_LINE3"                   value="ADDRESS_LINE3"/>

<element name="CITY"                        value="CITY"/>

<element name="STATE"                        value="STATE"/>

<element name="ZIP"                        value="ZIP"/>

<element name="INVOICE_AMOUNT"                  value="INVOICE_AMOUNT"/>

<element name="INVOICE_DATE"                    value="INVOICE_DATE"/>

<element name="INVOICE_ID"                      value="INVOICE_ID"/>

<element name="INVOICE_NUM"                     value="INVOICE_NUM"/>

<element name="LAST_UPDATED_BY"                 value="LAST_UPDATED_BY"/>

<element name="UPDATED_BY"                  value="UPDATED_BY"/>

<element name="PAYMENT_TERMS"                   value="PAYMENT_TERMS"/>

<element name="SOB_DESC"                        value="SOB_DESC"/>

<element name="PO_NUMBER"                       value="PO_NUMBER"/>

<element name="BUYER"                           value="BUYER"/>

<element name="CATEGORY"                        value="CATEGORY"/>

<element name="CATEGORY2"                       value="CATEGORY2"/>

<element name="LOCATION_CODE"                   value="LOCATION_CODE"/>

<element name="ADDRESS_LINE_1"                  value="ADDRESS_LINE_1"/>

<element name="TOWN_OR_CITY"                    value="TOWN_OR_CITY"/>

<element name="POSTAL_CODE"                     value="POSTAL_CODE"/>

<element name="STATE1"                        value="STATE1"/>

<element name="INVOICE_DISTRIBUTION_ID"         value="INVOICE_DISTRIBUTION_ID"/>

<element name="AMOUNT"                        value="AMOUNT"/>

<element name="ACCOUNTING_DATE"                 value="ACCOUNTING_DATE"/>

<element name="ACCOUNT_CODE"                    value="ACCOUNT_CODE"/>

<element name="COMPANY"                        value="COMPANY"/>

<element name="COMPANY_NAME"                    value="COMPANY_NAME"/>

<element name="ACCOUNT"                        value="ACCOUNT"/>

<element name="ACCOUNT_NAME"                    value="ACCOUNT_NAME"/>

<element name="DEPARTMENT"                      value="DEPARTMENT"/>

<element name="DEPARTMENT_NAME"                 value="DEPARTMENT_NAME"/>

<element name="PROGRAM"                        value="PROGRAM"/>

<element name="PROGRAM_NAME"                    value="PROGRAM_NAME"/>

<element name="RESPONSIBILITY"                  value="RESPONSIBILITY"/>

<element name="RESPONSIBILITY_NAME"             value="RESPONSIBILITY_NAME"/>

<element name="INTERCOMPANY"                    value="INTERCOMPANY"/>

<element name="TBD"                        value="TBD"/>

<element name="CLEARED_DATE"                    value="CLEARED_DATE"/>

<element name="PROJECT_NUMBER"                  value="PROJECT_NUMBER"/>

<element name="TASK_NUMBER"                     value="TASK_NUMBER"/>

<element name="IMAGE_URL"                       value="IMAGE_URL"/>

<element name="SYSDATE1"                        value="SYSDATE1"/>

   </group>

 </dataStructure>  

 <dataTrigger name="afterReport"  source="XXUNV_AP_DET_RPT_BURST_PKG.afterReport"/>

 </dataTemplate>

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');