CREATE OR REPLACE PACKAGE XXEXPD_AP_VAL AUTHID CURRENT_USER AS
/*
***************************************************************************/
/* $Header: XXEXPD_PAYABLES_VALIDATIONS.pks#1 $ */
/* $Author: xqian $
*/
/* $DateTime: 2016/02/19 09:52:13 $ */
/* $Revision: #1 $ */
/*
*/
/* Copyright Artic
Inc.
*/
/*
*/
/* RICE ID :
Data Fix Script */
/* File Name :
XXEXPD_PAYABLES_VALIDATIONS.pks */
/* Purpose :
ORASUP-57954 Fix the problem of EXPD Invoice Due Not Paid Report erroring out *
/
/*
***************************************************************************/
/*
***************************************************************************/
/* Program History:
*/
/* ---------------
*/
/* Version Author
Date Comments */
/* ------- --------------
-----------
----------------------
*/
/* 1.0 Ranjit
P 06/28/2018 Adding packages */
/*
***************************************************************************/
FUNCTION get_remit_site(p_vendor_site_id NUMBER)
RETURN VARCHAR2;
FUNCTION get_inv_po_numbers(p_invoice_id IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_wf_rejection_notes(p_invoice_id IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_email_address(p_party_site_id IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_supsite_pay_method(p_vendor_site_id IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_supplier_bank(p_party_site_id NUMBER)
RETURN VARCHAR2 ;
FUNCTION get_short_text(p_pk1_value NUMBER,p_entity_name VARCHAR2)
RETURN VARCHAR2 ;
FUNCTION get_ven_inv_bal(p_display_flag VARCHAR2,p_vendor_id NUMBER,p_org_id NUMBER)
RETURN NUMBER;
PROCEDURE conc_request_status_email(p_conc_request_id IN NUMBER) ;
FUNCTION get_hold_name ( P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_hold_reason ( P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_hold_date ( P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_hold_by ( P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2;
FUNCTION get_ou_name(p_org_id NUMBER)
RETURN VARCHAR2;
FUNCTION get_org_id(p_org_name VARCHAR2)
RETURN NUMBER;
END XXEXPD_AP_VAL;
/
SHOW ERROR;
CREATE OR REPLACE
PACKAGE BODY XXEXPD_AP_VAL AS
/*
***************************************************************************/
/* $Header: XXEXPD_PAYABLES_VALIDATIONS */
/* $Author: xqian $ */
/* $DateTime: 2016/02/19 09:52:13 $ */
/* $Revision: #1 $
*/
/*
*/
/* Copyright
*/
/*
*/
/* RICE ID :
Data Fix Script */
/* File Name :
XXEXPD_PAYABLES_VALIDATIONS.pks */
/* Purpose :
ORASUP-57954 Fix the problem of EXPD Invoice Due Not Paid Report erroring out *
/
/*
***************************************************************************/
/*
***************************************************************************/
/* Program History:
*/
/* ---------------
*/
/* Version Author
Date Comments */
/* ------- --------------
-----------
----------------------
*/
/* 1.0 Ranjit
P 06/28/2018 Adding packages for */
/*
***************************************************************************/
FUNCTION get_inv_po_numbers(p_invoice_id IN NUMBER) RETURN VARCHAR2
/* ***************************************************************************/
/* Procedure Name : get_inv_po_numbers */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Function Retruns the PO Number for an Invoice */
/*
***************************************************************************/
IS
l_po_numbers VARCHAR2(240);
l_po_count NUMBER;
BEGIN
/* SELECT count(*)
INTO l_po_count
FROM
po_headers_all poh WHERE po_header_id =
(SELECT
aila.po_header_id FROM ap_invoice_lines_all aila WHERE
aila.invoice_id = p_invoice_id
and
aila.line_type_lookup_code = 'ITEM'
and
po_header_id IS NOT NULL
and ROWNUM
= 1);
IF
l_po_count = 0 THEN
RETURN
NULL;
END IF;*/
SELECT SUBSTR(LISTAGG(segment1, ';') WITHIN GROUP (ORDER BY segment1), 1, 240)
INTO l_po_numbers
FROM
(SELECT DISTINCT SEGMENT1
--INTO l_po_count
FROM po_headers_all poh WHERE po_header_id =
(SELECT
aila.po_header_id FROM ap_invoice_lines_all aila WHERE
aila.invoice_id = p_invoice_id
and aila.line_type_lookup_code = 'ITEM'
and po_header_id IS NOT NULL));
/*(SELECT DISTINCT poh.segment1
FROM
po_headers_all poh,
ap_invoice_lines_all aila
WHERE
aila.invoice_id = p_invoice_id
AND aila.line_type_lookup_code = 'ITEM'
AND aila.po_header_id = poh.po_header_id
AND aila.org_id = poh.org_id
AND aila.po_header_id IS NOT NULL); */
RETURN l_po_numbers;
EXCEPTION
WHEN OTHERS THEN
RETURN l_po_numbers;
END get_inv_po_numbers;
FUNCTION get_wf_rejection_notes(p_invoice_id IN NUMBER) RETURN VARCHAR2
/*
***************************************************************************/
/* Procedure Name : get_wf_rejection_notes */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Function Returns the Rejected Comments */
/* ***************************************************************************/
IS
l_rej_notes VARCHAR2(4000);
CURSOR cur_wf_rej_notes IS
SELECT text_value
FROM wf_item_attribute_values
WHERE item_type = 'APINVAPR'
AND name = 'WF_NOTE'
AND item_key
= p_invoice_id||'_1';
BEGIN
FOR i IN cur_wf_rej_notes LOOP
l_rej_notes := l_rej_notes||i.text_value;
END LOOP;
RETURN l_rej_notes;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
FUNCTION get_email_address(p_party_site_id IN NUMBER) RETURN VARCHAR2
/*
***************************************************************************/
/* Procedure Name : get_email_address */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Function returns the Email address of Supplier Site */
/* ***************************************************************************/
IS
CURSOR get_email_cur IS
SELECT HCP.EMAIL_ADDRESS
FROM HZ_PARTY_SITES HPS,
HZ_CONTACT_POINTS HCP,
AP_SUPPLIERS ASS
WHERE 1=1
AND hps.party_site_id = p_party_site_id
AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND HPS.PARTY_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND HCP.CONTACT_POINT_TYPE='EMAIL';
l_email_addr VARCHAR2(300);
BEGIN
OPEN get_email_cur;
FETCH get_email_cur INTO l_email_addr;
CLOSE get_email_cur;
RETURN l_email_addr;
EXCEPTION
WHEN OTHERS THEN
RETURN l_email_addr;
END get_email_address;
FUNCTION get_supsite_pay_method(p_vendor_site_id IN NUMBER) RETURN VARCHAR2
/*
***************************************************************************/
/* Procedure Name : get_supsite_pay_method */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Function returns the Supplier Site Payment Method */
/*
***************************************************************************/
IS
CURSOR supsite_paymethod_cur IS
SELECT paym.payment_method_code
FROM ap_supplier_sites_all sites,
iby_external_payees_all
payee,
iby_ext_party_pmt_mthds
paym
WHERE payee.ext_payee_id = paym.ext_pmt_party_id
AND sites.vendor_site_id = payee.supplier_site_id
AND sites.vendor_site_id = p_vendor_site_id
AND paym.primary_flag = 'Y';
l_pay_method VARCHAR2(100);
BEGIN
OPEN supsite_paymethod_cur;
FETCH supsite_paymethod_cur INTO l_pay_method;
CLOSE supsite_paymethod_cur;
RETURN l_pay_method;
EXCEPTION
WHEN OTHERS THEN
RETURN l_pay_method;
END get_supsite_pay_method;
/*
***************************************************************************/
/* Procedure Name : get_remit_site */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Function returns the Remit Supplier Site of Vendor Site */
/*
***************************************************************************/
FUNCTION get_remit_site(p_vendor_site_id NUMBER) RETURN VARCHAR2 IS
l_remit_site VARCHAR2(100);
BEGIN
SELECT asa1.vendor_site_code
INTO l_remit_site
FROM iby_ext_payee_relationships
iep,
ap_supplier_sites_all asa,
ap_supplier_sites_all asa1
WHERE iep.supplier_site_id = asa.vendor_site_id
AND asa1.vendor_site_id = iep.REMIT_SUPPLIER_SITE_ID
AND asa.vendor_site_id = p_vendor_site_id;
RETURN l_remit_site;
EXCEPTION
WHEN OTHERS THEN
return l_remit_site;
END get_remit_site;
FUNCTION get_supplier_bank(p_party_site_id NUMBER) RETURN VARCHAR2
/* ***************************************************************************/
/* Procedure Name : get_supplier_bank */
/* Author :
Ranjit Ponugoti */
/* In Parameters
: */
/* Out Parameters : None
*/
/* Purpose :
This Function returns Supplier Bank Details */
/*
***************************************************************************/
IS
CURSOR supp_addr_cur IS
SELECT ieba.masked_bank_account_num
FROM iby_ext_bank_accounts ieba
,iby_external_payees_all iepa
,iby_pmt_instr_uses_all ipiua
WHERE ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ipiua.instrument_type = 'BANKACCOUNT'
AND ipiua.payment_flow = 'DISBURSEMENTS'
AND NVL(ipiua.end_date,SYSDATE) >= SYSDATE
AND ipiua.order_of_preference = 1
AND iepa.party_site_id = p_party_site_id;
/*CURSOR supp_site_cur IS
SELECT ieba.masked_bank_account_num
FROM ap_supplier_sites_all assa
,hz_parties
hp
,iby_ext_bank_accounts
ieba
,iby_external_payees_all
iepa
,iby_pmt_instr_uses_all ipiua
WHERE
assa.vendor_site_id = iepa.supplier_site_id
AND
hp.party_id = ieba.bank_id
AND
ipiua.instrument_id = ieba.ext_bank_account_id
AND
ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND
ipiua.instrument_type = 'BANKACCOUNT'
AND
ipiua.payment_flow = 'DISBURSEMENTS'
AND
ipiua.order_of_preference = 1
AND
assa.vendor_site_id = p_vendor_site_id;*/
l_bank_accout VARCHAR2(100);
BEGIN
OPEN supp_addr_cur;
FETCH supp_addr_cur INTO l_bank_accout;
CLOSE supp_addr_cur;
RETURN l_bank_accout;
EXCEPTION
WHEN OTHERS THEN
RETURN '0';
END get_supplier_bank;
FUNCTION get_short_text(p_pk1_value NUMBER,p_entity_name VARCHAR2) RETURN VARCHAR2
/*
***************************************************************************/
/* Procedure Name : get_short_text */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Function returns Short Attachement Texts */
/*
***************************************************************************/
IS
/*CURSOR cur_attach_text
IS SELECT REPLACE(REPLACE(fdst.short_text,CHR(10),'
'),CHR(13),' ') short_text
FROM
fnd_documents_short_text fdst
, fnd_documents_vl fd
, fnd_attached_documents fad
, fnd_document_categories_vl fc
WHERE
fd.media_id = fdst.media_id
AND fd.document_id = fad.document_id
AND fad.category_id = fc.category_id
AND fd.datatype_name = 'Short Text'
AND fad.entity_name = p_entity_name
AND fad.pk1_value = p_pk1_value
AND ROWNUM=1;*/
l_text VARCHAR2(4000);
BEGIN
/*
for i in cur_attach_text loop
l_text := l_text || i.short_text;
end loop;
RETURN l_text;
EXCEPTION
WHEN OTHERS THEN
l_text := 'Contains More than 4000 Characters';
*/
SELECT SUBSTR(LISTAGG(short_text, ',') WITHIN GROUP (ORDER BY seq_num), 1, 4000)
INTO l_text
FROM
(SELECT REPLACE(REPLACE(fdst.short_text,CHR(10),' '),CHR(13),' ') short_text,fad.seq_num
FROM fnd_documents_short_text
fdst
, fnd_documents_vl fd
, fnd_attached_documents fad
, fnd_document_categories_vl fc
WHERE fd.media_id = fdst.media_id
AND fd.document_id = fad.document_id
AND fad.category_id = fc.category_id
AND fd.datatype_name = 'Short Text'
AND fad.entity_name = p_entity_name
AND fad.pk1_value = p_pk1_value);
RETURN l_text;
EXCEPTION
WHEN OTHERS THEN
RETURN l_text;
END get_short_text;
FUNCTION get_ven_inv_bal(p_display_flag VARCHAR2,p_vendor_id NUMBER,p_org_id NUMBER) RETURN NUMBER
/*
***************************************************************************/
/* Procedure Name : get_ven_inv_bal */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose : This Function Calculates and returns
Invoice Balances */
/*
***************************************************************************/
IS
l_bal number := 0;
BEGIN
IF p_display_flag = 'Y' THEN
SELECT ((SELECT SUM(amount_remaining)
FROM ap_payment_schedules_all aps
WHERE EXISTS (SELECT 1
FROM ap_invoices_all aia
WHERE invoice_id = aps.invoice_id
AND vendor_id
= p_vendor_id
AND org_id
= p_org_id))-
( SELECT NVL(SUM(AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id)),0)
FROM ap_invoices_all i
WHERE i.org_id
= p_org_id
AND i.vendor_id = p_vendor_id
AND AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id) >0
AND i.invoice_type_lookup_code = 'PREPAYMENT'))
into l_bal
from dual;
RETURN l_bal;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END get_ven_inv_bal;
PROCEDURE conc_request_status_email(p_conc_request_id IN NUMBER)
/*
***************************************************************************/
/* Procedure Name : conc_request_status_email */
/* Author :
Ranjit Ponugoti */
/* In Parameters
:
*/
/* Out Parameters : None
*/
/* Purpose :
This Procedure sends email Status of Concurrent Request */
/*
***************************************************************************/
IS
lv_email_address VARCHAR2(1000);
lv_cc_email_address VARCHAR2(1000);
lv_error_count NUMBER;
lv_total_count NUMBER;
lv_proc_count NUMBER;
lv_preproc_err_count VARCHAR2(1);
lv_preproc_vend_err_cnt NUMBER;
lv_smtp_port NUMBER := fnd_profile.VALUE ('FND_SMTP_PORT'); /*FND: SMTP Port*/
lv_smtp_host VARCHAR2(100):= fnd_profile.VALUE ('FND_SMTP_HOST'); /*FND: SMTP Host*/
lv_mail_conn UTL_SMTP.connection;
lv_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
lv_msg_body CLOB;
lv_request_id NUMBER;
lv_instance VARCHAR2(100);
lv_subject VARCHAR2(400);
crlf VARCHAR2(2) := chr(13)||chr(10);
l_exp_end_date VARCHAR2(100) := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
l_exp_start_date VARCHAR2(100) ;
l_req_status VARCHAR2(100) ;
l_share_drive VARCHAR2(200) ;
lc_phase VARCHAR2(200) ;
lc_status VARCHAR2(200) ;
lc_dev_phase VARCHAR2(200) ;
lc_dev_status VARCHAR2(200) ;
lc_message VARCHAR2(200) ;
l_req_return_status BOOLEAN;
p_attach_name VARCHAR2(100) DEFAULT 'Logfile.txt';
p_attach_mime VARCHAR2(100) DEFAULT 'text/plain';
p_attach_clob CLOB := 'This is a very small CLOB!';
l_step PLS_INTEGER := 12000;
CURSOR cur_req_details IS
SELECT *
FROM fnd_concurrent_requests
WHERE request_id = p_conc_request_id;
l_req_det_rec cur_req_details%ROWTYPE;
BEGIN
LOOP
--
--To make process execution to wait for 1st program to
complete
--
l_req_return_status :=
fnd_concurrent.wait_for_request (request_id =>
p_conc_request_id
,INTERVAL => 5 --interval
Number of seconds to wait between checks
,max_wait => 60 --Maximum number of seconds
to wait for the request completion
,phase => lc_phase
,STATUS => lc_status
,dev_phase => lc_dev_phase
,dev_status => lc_dev_status
,message => lc_message
);
EXIT WHEN UPPER (lc_phase) = 'COMPLETED';
END LOOP;
OPEN cur_req_details;
FETCH cur_req_details INTO l_req_det_rec;
CLOSE cur_req_details;
--insert into xx_debug values('Parameter
Count'||p_tot_files);
SELECT DECODE(l_req_det_rec.status_code,'C','Success','E','Error','X','Warning','NA')
INTO l_req_status
FROM dual;
IF l_req_det_rec.argument1 = 'Does not equal' AND RTRIM(l_req_det_rec.argument2,';') IS NULL THEN
l_req_det_rec.argument2 := 'All';
ELSIF l_req_det_rec.argument1 = 'Equals' AND RTRIM(l_req_det_rec.argument2,';') IS NULL THEN
l_req_det_rec.argument2 := 'None';
END IF;
IF l_req_det_rec.argument12 = 'Does not equal' AND RTRIM(l_req_det_rec.argument13,';') IS NULL THEN
l_req_det_rec.argument13 := 'All';
ELSIF l_req_det_rec.argument12 = 'Equals' AND RTRIM(l_req_det_rec.argument13,';') IS NULL THEN
l_req_det_rec.argument13 := 'None';
END IF;
IF INSTR(l_req_det_rec.argument17,'@') != 0 THEN
lv_email_address := l_req_det_rec.argument17;
else
lv_email_address := 'ranreddy@Artic.com';
END IF;
l_share_drive := '"\\chelsqlpsfdb101\oracle$\Finance\DBNP\EXPD_AP_DBNP_REPORT_'||to_char(sysdate,'DDMONYYYY')||'.xls"';
lv_subject := 'EXPD Invoice DBNP Report from
Oracle - '||l_req_det_rec.request_id||' - '||l_req_status;
lv_msg_body := '<html> <head>
<title>Notification:</title> </head> <body> <p>
===========================================================================<br>
================
EXPD Invoice Due but Not Paid Report =====================<br>
===========================================================================<br>
Concurrent
Request Id : '||l_req_det_rec.request_id||'<br>
Parameters:
<br>
Operating
Unit: '||l_req_det_rec.argument1||'<br>
List
of Operating Units: '||RTRIM(l_req_det_rec.argument2,';')||'<br>
Invoice
Date From: '||NVL(RTRIM(l_req_det_rec.argument3,chr(0)),'NULL')||'<br>
Invoice
Date To: '||NVL(RTRIM(l_req_det_rec.argument4,chr(0)),'NULL')||'<br>
Sort
Invoices By: '||NVL(RTRIM(l_req_det_rec.argument5,chr(0)),'All')||'<br>
Minimum
Amount Due: '||l_req_det_rec.argument6||'<br>
Maximum
Amount Due: '||l_req_det_rec.argument7||'<br>
Invoice
Type: '||l_req_det_rec.argument8||'<br>
Trading
Partner: '||l_req_det_rec.argument9||'<br>
Business
Classification: '||NVL(RTRIM(l_req_det_rec.argument12,chr(0)),'All')||'<br>
List
of Business Classifications '||RTRIM(l_req_det_rec.argument13,';')||'<br>
Aging
Period Name: '||l_req_det_rec.argument14||'<br>
Include
Credit Balances: '||NVL(l_req_det_rec.argument15,'Y')||'<br>
Email
Status to: '||l_req_det_rec.argument17||'<br><br>
Start
Time: '||to_char(l_req_det_rec.actual_start_date,'DD-MON-YYYY HH24:MI:SS')||'<br>
End
Time : '||to_char(l_req_det_rec.actual_completion_date,'DD-MON-YYYY HH24:MI:SS')||'<br>
<br>
<b>Link
to the Report </b>: <a href='||l_share_drive||'>EXPD Invoice DBNP Report</a><br>
Please
click on the link to access the report from the current job run.<br>
If
you do not have permission to the file or file path, please contact your
Windows Administrator<br>
For
any system issues, you may log a ORASUP JIRA request:
http://go/orasup/.<br>
===========================================================================<br>
PS:
File Transfer may take longer. If the Folder link above does not work, please
try after some time.
</p>
</body>
</html>';
lv_mail_conn := UTL_SMTP.open_connection
(lv_smtp_host, lv_smtp_port);
UTL_SMTP.helo (lv_mail_conn, lv_smtp_host);
UTL_SMTP.mail (lv_mail_conn, 'donotreply-orcl-sys-@Artic.com');
IF lv_email_address IS NULL THEN
dbms_output.put_line('Invalid Mail Address');
ELSE
UTL_SMTP.rcpt (lv_mail_conn, lv_email_address);
END IF;
UTL_SMTP.open_data (lv_mail_conn);
UTL_SMTP.write_data
(lv_mail_conn,'Date: '|| TO_CHAR (SYSTIMESTAMP, 'DD Mon YYYY HH24:MI:SS TZH:TZM')|| UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'To: ' || lv_email_address || UTL_TCP.crlf);
IF NVL(INSTR(lv_cc_email_address,'@'),0) <> 0 THEN
FOR i IN (SELECT LEVEL AS id, REGEXP_SUBSTR(lv_cc_email_address, '[^;]+', 1, LEVEL) AS cc_email_name
FROM dual
CONNECT BY REGEXP_SUBSTR(lv_cc_email_address, '[^;]+', 1, LEVEL) IS NOT NULL) loop
utl_smtp.Rcpt(lv_mail_conn,i.cc_email_name);
END LOOP;
UTL_SMTP.write_data
(lv_mail_conn,'Cc: ' || lv_cc_email_address || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data
(lv_mail_conn,'From: ' || 'donotreply-orcl-sys@artic.com' || UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'Subject: ' || lv_subject || UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'Reply-To: '|| 'donotreply-orcl-sys@artic.com'|| UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'Content-Type: multipart/mixed;
boundary="'|| lv_boundary|| '"'|| UTL_TCP.crlf|| UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'--' || lv_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'Content-Type: text/html;
charset="iso-8859-1"'|| UTL_TCP.crlf|| UTL_TCP.crlf);
--write email body
UTL_SMTP.write_data(lv_mail_conn, lv_msg_body);
UTL_SMTP.write_data
(lv_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data
(lv_mail_conn,'--' || lv_boundary || '--' || UTL_TCP.crlf);
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(lv_mail_conn, '--' || lv_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(lv_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(lv_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(lv_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
END LOOP;
UTL_SMTP.write_data(lv_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.close_data
(lv_mail_conn);
UTL_SMTP.quit (lv_mail_conn);
dbms_output.put_line('Success');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Un-Expected Error '||SQLERRM);
END conc_request_status_email;
FUNCTION GET_HOLD_NAME(P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2
AS
lv_hold_name VARCHAR2(250);
BEGIN
SELECT SUBSTR(LISTAGG(hold_name, ',') WITHIN GROUP (ORDER BY invoice_id), 1, 250)
INTO lv_hold_name
FROM
(select DISTINCT DECODE(aha.release_reason,NULL,flv.meaning,NULL) hold_name, aha.invoice_id
FROM ap_holds_all aha
,fnd_lookup_values flv
WHERE
flv.lookup_type = 'HOLD CODE'
AND NVL(aha.status_flag,'XXX') <> 'R'
AND aha.hold_lookup_code = flv.lookup_code
AND aha.invoice_id = P_INVOICE_ID);
RETURN lv_hold_name;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;
FUNCTION GET_HOLD_REASON(P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2
AS
lv_hold_reason VARCHAR2(250);
BEGIN
SELECT SUBSTR(LISTAGG(hold_reason,',') WITHIN GROUP (ORDER BY invoice_id), 1, 250)
INTO lv_hold_reason
FROM
(select DISTINCT DECODE(aha.release_reason,NULL,aha.hold_reason,NULL) hold_reason, aha.invoice_id
FROM ap_holds_all aha
WHERE NVL(aha.status_flag,'XXX') <> 'R'
AND aha.invoice_id = P_INVOICE_ID);
RETURN lv_hold_reason;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;
FUNCTION GET_HOLD_DATE(P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2
AS
lv_hold_date VARCHAR2(250);
BEGIN
SELECT SUBSTR(TRIM(LISTAGG(hold_date, ',') WITHIN GROUP (ORDER BY invoice_id)), 1, 250)
INTO lv_hold_date
FROM
(SELECT DISTINCT DECODE(aha.release_reason,NULL,to_char(aha.hold_date,'DD-MON-RR'),NULL) hold_date, aha.invoice_id
FROM ap_holds_all aha
WHERE NVL(aha.status_flag,'XXX') <> 'R'
AND aha.invoice_id = P_INVOICE_ID);
RETURN lv_hold_date;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;
FUNCTION GET_HOLD_BY(P_INVOICE_ID IN NUMBER)
RETURN VARCHAR2
AS
lv_hold_by VARCHAR2(250);
BEGIN
SELECT SUBSTR(LISTAGG(held_by, ',') WITHIN GROUP (ORDER BY invoice_id), 1, 250)
INTO lv_hold_by
FROM
(SELECT DISTINCT DECODE(aha.release_reason,NULL, fu.user_name ,NULL) held_by, aha.invoice_id
FROM ap_holds_all aha
,fnd_user fu
WHERE NVL(aha.status_flag,'XXX') <> 'R'
AND aha.invoice_id = P_INVOICE_ID
AND aha.held_by = fu.user_id);
RETURN lv_hold_by;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END;
FUNCTION get_ou_name(p_org_id NUMBER)
RETURN VARCHAR2 IS
l_org_name VARCHAR2(240);
BEGIN
select name
into l_org_name
from hr_operating_units hou
where organization_id = p_org_id;
RETURN l_org_name;
EXCEPTION
WHEN OTHERS THEN
return l_org_name;
END get_ou_name;
FUNCTION get_org_id(p_org_name VARCHAR2)
RETURN NUMBER IS
l_org_id NUMBER;
BEGIN
select organization_id
into l_org_id
from hr_operating_units hou
where name = p_org_name;
RETURN l_org_id;
EXCEPTION
WHEN OTHERS THEN
return l_org_id;
END get_org_id;
END XXEXPD_AP_VAL;
/
SHOW ERROR;
EXIT;
No comments:
Post a Comment