UTL Bank File

CREATE OR REPLACE procedure APPS.XXSCC_SAUDI_PAY_OUTPUT_FILE(
errbuf OUT varchar2,
retcode OUT varchar2
)
IS
v_emsg VARCHAR2(2000) := 'Proc Bank Payment interface Failed with ERROR ';
v_err_msg VARCHAR2(2000);
v_message VARCHAR2(2000);
v_no_asset NUMBER := 0 ;
Out_File_Handle UTL_FILE.FILE_TYPE;
Out_FileErrMsg VARCHAR2(100);
lv_total_emp number := 0;
lv_total_amt number := 0;
lv_date varchar2(10) ;
CURSOR lcu_bank_file IS
SELECT rownum,pact.payroll_action_id, pact.action_type, pact.consolidation_set_id,
pact.payroll_id, pact.start_date, pact.effective_date,
pay.payroll_name, cons.consolidation_set_name, ppy.VALUE netpay,
asact.assignment_id, asact.assignment_action_id, asg.employee_number,
asg.full_name, asg.original_date_of_hire, asg.grade, asg.ORGANIZATION,
asg.POSITION, pym.org_payment_method_name, bnk.bank, bnk.branch,
asg.account_name account_name,
bnk.account_number,
pym.CURRENCY_CODE
FROM pay_payroll_actions pact,
pay_all_payrolls_f pay,
pay_assignment_actions asact,
pay_consolidation_sets cons,
pay_pre_payments ppy,
pay_org_payment_methods_f pym,
(SELECT ass.assignment_id, per.employee_number, per.full_name,
per.original_date_of_hire, per.full_name account_name,
gra.NAME grade, pos.NAME POSITION, org.NAME ORGANIZATION
FROM per_all_people_f per,
per_all_assignments_f ass,
hr_all_organization_units org,
per_grades gra,
per_positions pos
WHERE per.person_id = ass.person_id
AND TRUNC (SYSDATE) BETWEEN per.effective_start_date
AND per.effective_end_date
AND ass.effective_end_date =
(SELECT MAX (asg2.effective_end_date)
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = ass.assignment_id)
AND ass.grade_id = gra.grade_id(+)
AND ass.position_id = pos.position_id(+)
AND ass.organization_id = org.organization_id) asg,
(select
ppm.external_account_id,
ppm.org_payment_method_id,
bnk.segment1 bank,
bnk.segment2 branch,
bnk.segment3 account_name, bnk.segment4 account_number
from pay_org_payment_methods_f ppm ,pay_external_accounts bnk
where
ppm.external_account_id=bnk.external_account_id
AND TRUNC (SYSDATE) BETWEEN ppm.effective_start_date
AND ppm.effective_end_date) bnk
WHERE pact.action_status = 'C' -- Completed
AND (pact.action_type = 'P' OR pact.action_type = 'U') -- Prepayment
AND pact.payroll_id = pay.payroll_id
AND pact.effective_date BETWEEN pay.effective_start_date
AND pay.effective_end_date
AND pact.consolidation_set_id = cons.consolidation_set_id
AND pact.payroll_action_id = asact.payroll_action_id
AND asact.assignment_action_id = ppy.assignment_action_id
AND asact.assignment_id = asg.assignment_id
AND ppy.org_payment_method_id = pym.org_payment_method_id
AND ppy.ORG_PAYMENT_METHOD_ID = bnk.EXTERNAL_ACCOUNT_ID(+)
order by asg.employee_number;



BEGIN

select to_char(sysdate,'mmddyyyy') into lv_date from dual;

Out_File_Handle := UTL_FILE.FOPEN('/netappbkup/clonescc/oracle/db/tech_st/10.2.0/appsutil/outbound/PRDCLONE_ebsdev','XXSCC_Saudi_Payment_Output_file'||lv_date||'.txt', 'W');
MO_GLOBAL.SET_POLICY_CONTEXT('S', FND_PROFILE.VALUE('org_id'));
UTL_FILE.PUT_LINE(Out_File_Handle,'000' ||
'SAUDICEM'||
rpad(TO_CHAR(sysdate,'YYYYMMDD'),8)||
rpad('SAUDI CEMENT COMPANY PAYROLL',35)
);
/*
UTL_FILE.PUT_LINE(Out_File_Handle,'PAYROLL_ID' ||chr(9)||
'EMPLOYEE_NUMBER'||chr(9)||
'FULL_NAME'||chr(9)||
'NETPAY'||chr(9)||
'BANK'||chr(9)||
'BRANCH'||chr(9)||
'ACCOUNT_NUMBER'
); */
lv_total_emp := 0;
lv_total_amt := 0;

FOR c_bank_file IN lcu_bank_file
LOOP
UTL_FILE.PUT_LINE(Out_File_Handle,lpad('112',3)||
lpad(c_bank_file.ROWNUM,6,'0')||
rpad(c_bank_file.PAYROLL_ID,16) ||
rpad(nvl(c_bank_file.EMPLOYEE_NUMBER,' '),12)||
'RIBLSARIXXX'||
rpad(nvl(c_bank_file.ACCOUNT_NUMBER,' '),34)||
lpad(c_bank_file.NETPAY,15,'0')||
rpad(nvl(c_bank_file.CURRENCY_CODE,' '),3)||
rpad(nvl(c_bank_file.BANK,' '),35)||
rpad(nvl(c_bank_file.BRANCH,' '),20)||
rpad('CITY',9)||
rpad(nvl(c_bank_file.FULL_NAME,' '),35) ||
rpad(nvl(c_bank_file.payroll_name,' '),140)
);
lv_total_emp := lv_total_emp+1;
lv_total_amt := lv_total_amt+c_bank_file.NETPAY;
END LOOP;
/*FOR c_bank_file_total IN lcu_bank_file_total
LOOP
UTL_FILE.PUT_LINE(Out_File_Handle,'999'||
lpad(c_bank_file_total.TOTAL_NETPAY,18,'0')||
lpad(c_bank_file_total.TOTAL_EMPLOYEE,6,'0')
);

END LOOP;*/
UTL_FILE.PUT_LINE(Out_File_Handle,'999'||
lpad(lv_total_amt,18,'0')||lpad(lv_total_emp,6,'0')
);

UTL_FILE.FCLOSE(Out_File_Handle);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'The Output File is available at this path :--/orabackup/crpinst/UAT/oracle/db/tech_st/10.2.0/appsutil/outbound/UAT_ebsdev,xxscc_Asset_Details_outbound_file.xls');
-- ------------ EXCEPTION PART BEGINS HERE ---------------
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The file location or the filename is invalid. Modify the INIT.ORA parameter initialization file of your instance');
DBMS_OUTPUT.PUT_LINE('The file location or the filename is invalid. Modify the INIT.ORA parameter initialization file of your instance');
RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The value provided for the open_mode parameter in UTL_FILE.FOPEN was invalid. It must be "A," "R," or "W." ');
DBMS_OUTPUT.PUT_LINE('The value provided for the open_mode parameter in UTL_FILE.FOPEN was invalid. It must be "A," "R," or "W." ');
RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'UTL_FILE could not open or operate on the file as requested. You are trying to write to a read-only file.');
DBMS_OUTPUT.PUT_LINE('UTL_FILE could not open or operate on the file as requested. You are trying to write to a read-only file.');
RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The operating system returned an error when tried to write to the file');
DBMS_OUTPUT.PUT_LINE('The operating system returned an error when tried to write to the file');
RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The file handle passed to a UTL_FILE program was invalid. Call UTL_FILE.FOPEN to obtain a valid file handle.');
DBMS_OUTPUT.PUT_LINE('The file handle passed to a UTL_FILE program was invalid. Call UTL_FILE.FOPEN to obtain a valid file handle.');
RAISE;
WHEN VALUE_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'You are trying to read or write lines in the file which are too long. The current implementation of UTL_FILE limits the size of a line read by UTL_FILE.GET_LINE to 1022 bytes. ');
DBMS_OUTPUT.PUT_LINE('You are trying to read or write lines in the file which are too long. The current implementation of UTL_FILE limits the size of a line read by UTL_FILE.GET_LINE to 1022 bytes. ');
RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Something went wrong and the PL/SQL runtime engine cannot assign blame to any of the previous exceptions.');
DBMS_OUTPUT.PUT_LINE('Something went wrong and the PL/SQL runtime engine cannot assign blame to any of the previous exceptions.');
RAISE;
WHEN OTHERS THEN
Out_FileErrMsg := SUBSTR(SQLERRM,1,100);
v_emsg:=SQLERRM;
v_message := v_emsg || v_err_msg;
UTL_FILE.FCLOSE(Out_File_Handle);
FND_FILE.PUT_LINE(FND_FILE.LOG,Out_FileErrMsg);
DBMS_OUTPUT.PUT_LINE('Some error is there'||SUBSTR(SQLERRM,1,100));

END XXSCC_SAUDI_PAY_OUTPUT_FILE;
/