We all write packages with different structures and formats based on the different guidelines provided by the organizations or projects.
I have tried to formalize one way of writing, which can be edited for your standard.
Hope this helps you to give you a little booster to start your code
CREATE OR REPLACE PACKAGE XXCUST_AP_PKG IS
------------------------------------------------------------------------------------
-- * PL/SQL PACKAGE : XXCUST_AP_PKG
-- * DATE : 01-Jan-2020
-- * PURPOSE : <Program Information - Project Request Reference>
--* XXAPCUSTPROG : <Concurrent Programs Information
------------------------------------------------------------------------------------
-- * VERSION DD-MON-YYYY PERSON CHANGES MADE
-- * ---------- ------------- ------------- --------------------------------
-- * 1.0 01-Jan-2020 Developer Initiated
-- ---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--XXAPCUSTPROG--<Program Name>
--PROCEDURE main Parameters
--p_inv_create_dt_from varchar2 --FND_DATE_STANDARD -- Invoice Creation Date From
--p_inv_create_dt_to varchar2 --FND_DATE_STANDARD -- Invoice Creation Date To
--p_org_id number --XX_ORG_ID_VS -- Organization Name
--p_vendor_ID varchar2 --XXAP_VENDOR_NAME -- Vendor Name
--p_vendoe_site_code varchar2 --XXAP_AP_VENDOR_SITE -- Vendor Site Code
--p_invoice_num varchar2 --XXAP Invoice Numbers -- Invoice Number
--p_print_log varchar2 DEFAULT 'Y'
---------------------------------------------------------------------------------
PROCEDURE main ( p_err_buff out varchar2
, p_err_code out varchar2
, p_inv_create_dt_from varchar2
, p_inv_create_dt_to varchar2
, p_org_id number
, p_vendor_ID varchar2
, p_vendoe_site_code varchar2
, p_invoice_num varchar2
, p_print_log varchar2 DEFAULT 'Y'
);
END XXCUST_AP_PKG;
CREATE OR REPLACE PACKAGE BODY XXCUST_AP_PKG IS
------------------------------------------------------------------------------------
-- * PL/SQL PACKAGE : XXCUST_AP_PKG
-- * DATE : 01-Jan-2020
-- * PURPOSE : <Program Information - Project Request Reference>
--* XXAPCUSTPROG : <Concurrent Programs Information
------------------------------------------------------------------------------------
-- * VERSION DD-MON-YYYY PERSON CHANGES MADE
-- * ---------- ------------- ------------- --------------------------------
-- * 1.0 01-Jan-2020 Developer Initiated
-- ---------------------------------------------------------------------------------
gp_print_log VARCHAR2(10) := 'Y';
gp_delimiter CHAR(1) := '~';
------------------------------------
procedure write_log(p_str varchar2) IS
BEGIN
IF gp_print_log = 'Y' THEN
fnd_file.put_line(fnd_file.log,to_char(sysdate,'HH24:MI:SS')||':'||p_str);
dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||':'||p_str);
END IF;
end write_log;
procedure write_msg(p_str varchar2) IS
BEGIN
fnd_file.put_line(fnd_file.log,to_char(sysdate,'HH24:MI:SS')||':'||p_str);
end write_msg;
procedure write_out(p_str varchar2) IS
BEGIN
fnd_file.put_line(fnd_file.output,p_str);
end write_out;
------------------------------------------------------------------------------------
--XXAPCUSTPROG--<Program Name>
--Parameters
--p_inv_create_dt_from varchar2 --FND_DATE_STANDARD -- Invoice Creation Date From
--p_inv_create_dt_to varchar2 --FND_DATE_STANDARD -- Invoice Creation Date To
--p_org_id number --XX_ORG_ID_VS -- Organization Name
--p_vendor_ID varchar2 --XXAP_VENDOR_NAME -- Vendor Name
--p_vendoe_site_code varchar2 --XXAP_AP_VENDOR_SITE -- Vendor Site Code
--p_invoice_num varchar2 --XXAP Invoice Numbers -- Invoice Number
--p_print_log varchar2 DEFAULT 'Y'
------------------------------------------------------------------------------------
PROCEDURE main ( p_err_buff out varchar2
, p_err_code out varchar2
, p_inv_create_dt_from varchar2
, p_inv_create_dt_to varchar2
, p_org_id number
, p_vendor_ID varchar2
, p_vendoe_site_code varchar2
, p_invoice_num varchar2
, p_print_log varchar2 DEFAULT 'Y'
) IS
CURSOR cur_inv is
SELECT '1' invoice_num from DUAL;
--
l_inv_rec_count NUMBER := 0;
l_inv_rec_psd_count NUMBER := 0;
l_inv_rec_err_count NUMBER := 0;
l_interface_line_id NUMBER;
l_line_status BOOLEAN;
l_err_msg VARCHAR2(4000);
BEGIN
write_msg('Program Starts');
gp_print_log := p_print_log;
FOR rec_inv in cur_inv LOOP
BEGIN
l_inv_rec_count := l_inv_rec_count + 1;
write_out( l_inv_rec_count || '::' || rec_inv.invoice_num);
l_interface_line_id:=XXAP_CUST_TBL_S.nextval;
--
insert into XXAP_CUST_TBL(interface_line_id,status)
values (l_interface_line_id, 'NEW');
l_inv_rec_psd_count := l_inv_rec_psd_count + 1;
--
EXCEPTION WHEN OTHERS THEN
l_inv_rec_err_count := l_inv_rec_err_count + 1;
write_out( l_inv_rec_count || '::' || rec_inv.invoice_num || ' :: Error');
END;
END LOOP;
write_out('Invoice Total Count ::'||l_inv_rec_count);
write_out('Invoice Processed Count ::'||l_inv_rec_psd_count);
write_out('Invoice Error Count ::'||l_inv_rec_err_count);
write_msg('Program Ends');
EXCEPTION WHEN OTHERS THEN
write_out('Error ::'||SQLERRM);
write_out('Invoice Total Count <when error occured>::'||l_inv_rec_count);
write_out('Invoice Processed Count <when error occured>::'||l_inv_rec_psd_count);
write_out('Invoice Error Count <when error occured>::'||l_inv_rec_err_count);
write_msg('Error ::'||SQLERRM);
write_msg('Program Ends with Error');
END main;
END XXCUST_AP_PKG;
No comments:
Post a Comment