Introduction:
This procedure is used to extract the adjustments from EBS for Cloud Import.
create or replace
PROCEDURE XXXX_AR_ADJ_EXTRACT_CLOUD
(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_org_id IN NUMBER,
p_trx_date_from IN DATE,
p_trx_date_to IN DATE
)
AS
———————————————————————
— Created By :
— Creation Date : 15-Jul-2020
— File Name : XXXX_AR_ADJ_EXTRACT_CLOUD
— Description : This script will display the Adjustment details
— Change History:
— Version Date Name Remarks
— ———– ————- ————— ——————–
— 1.0 17-Feb-2021 Sivachandaran Base Version
———————————————————————–
AR_ADJ_data UTL_FILE.file_type;
PATH VARCHAR2 (200);
format VARCHAR2 (200);
v_yearmmdd VARCHAR2 (200);
CURSOR AR_ADJ
IS
SELECT hca.account_number customer_name,
rct.trx_number INVOICE_NUMBER,
to_char(rct.trx_date,’YYYY-MM-DD’) trx_date,
psa.amount_due_original amount_due_original,
aa.AMOUNT ADJ_AMOUNT,
DECODE(rt.name,’MISC ADJUSTMENT’,’MISC ADJUSTMENTS’,rt.name) activity_name,
aa.TYPE adj_type,
to_char(aa.gl_date,’YYYY-MM-DD’) adj_gl_date,
DECODE(gl.segment1, NULL, NULL, gl.segment1 || ‘.’ || gl.segment2 || ‘.’ || gl.segment3 || ‘.’ || gl.segment4 || ‘.’ || gl.segment5 || ‘.’ || gl.segment6 || ‘.’ || gl.segment7 || ‘.’ || gl.segment8 || ‘.’ || gl.segment9) gl_account,
(select lkp1.meaning from ar_lookups lkp1 where lkp1.lookup_type = ‘ADJUST_REASON’
and lkp1.lookup_code=aa.REASON_CODE) ADJ_REASON,
aa.comments comments,
DECODE(rct.org_id,’82’,’USA’,’83’,’CANADA’,’USA’) country,
aa.adjustment_id
FROM
ar_adjustments_all aa,
ar_receivables_trx_all rt,
ra_customer_trx_all rct,
gl_code_combinations gl,
hz_cust_accounts hca,
ar_payment_schedules_all psa
WHERE rt.receivables_trx_id = aa.receivables_trx_id
AND aa.code_combination_id = gl.code_combination_id(+)
and rct.CUSTOMER_TRX_ID=aa.CUSTOMER_TRX_ID
and rct.CUSTOMER_TRX_ID=psa.CUSTOMER_TRX_ID
and hca.cust_account_id=rct.BILL_TO_CUSTOMER_ID
and rct.org_id=p_org_id
and rct.trx_number IN(‘XXXX’)
and rct.trx_date between :p_trx_date_from AND :p_trx_date_to
and hca.attribute3 in(‘XXXX’,’XXXX’);
BEGIN
SELECT TO_CHAR (SYSDATE, ‘YYYYMMDDHHMISS’)
INTO v_yearmmdd
FROM DUAL;
PATH :=’XXXX_AR_CLOUD_OUTBOUND’;
format := ‘XXXX_EBS_AR_ADJ_EXTRACT’ ||v_yearmmdd ||’.csv’;
AR_ADJ_data := UTL_FILE.fopen (PATH, format, ‘W’);
UTL_FILE.put_line (AR_ADJ_data,
‘Customer Number’
|| ‘,’
|| ‘Invoice Number’
|| ‘,’
|| ‘Invoice Date’
|| ‘,’
|| ‘Original Invoice Amount’
|| ‘,’
|| ‘Amount to Adjust’
|| ‘,’
|| ‘Activity Name’
|| ‘,’
|| ‘Type’
|| ‘,’
|| ‘GL Date’
|| ‘,’
|| ‘GL Account’
|| ‘,’
|| ‘Oracle Adj Reason’
|| ‘,’
|| ‘Comments’
|| ‘,’
|| ‘Country’
|| ‘,’
|| ‘Source System Ref No’
);
FOR i IN AR_ADJ
LOOP
UTL_FILE.put_line (AR_ADJ_data,
i.customer_name
|| ‘,’
||REPLACE(REPLACE(i.Invoice_Number,CHR(44),’ ‘),’,’,”)
|| ‘,’
|| i.trx_date
|| ‘,’
|| i.amount_due_original
|| ‘,’
||i.ADJ_AMOUNT
|| ‘,’
||REPLACE(REPLACE(i.activity_name,CHR(44),’ ‘),’,’,”)
|| ‘,’
||REPLACE(REPLACE(i.adj_type,CHR(44),’ ‘),’,’,”)
|| ‘,’
|| i.adj_gl_date
|| ‘,’
|| i.gl_account
|| ‘,’
|| i.ADJ_REASON
|| ‘,’
||REPLACE(REPLACE(i.comments,CHR(35),’ ‘),’,’,”)||’EBSAdj#’||i.adjustment_id
|| ‘,’
|| i.country
|| ‘,’
|| i.adjustment_id
);
END LOOP;
UTL_FILE.fclose (AR_ADJ_data);
END;
