plsql stored procedure submitting rdf and xml bursting program with timing intervals
A main program say Email ASN is an stored procedure upon submitting this program it will submit an rdf program with bursting xml program to receive email
Package Specification:
CREATE OR REPLACE PACKAGE <pkg name> AUTHID CURRENT_USER AS
-- -----------------------------------------------------------------------------
--
-- -----------------------------------------------------------------------------
--
-- Program Name : XX01.sql
-- Description : This package is used to send ASN in Email
--
-- Package Name :
-- Change Record:
-- -----------------------------------------------------------------------------
-- Version Date Author Remarks
--
-- -----------------------------------------------------------------------------
PROCEDURE delivery_asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,x_burst_request_id OUT NOCOPY NUMBER);
PROCEDURE asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,p_ou IN NUMBER
,p_order_num_from IN NUMBER
,p_order_num_to IN NUMBER
,p_ship_date_from IN VARCHAR2
,p_ship_date_to IN VARCHAR2
,p_delivery_from IN NUMBER
,p_delivery_to IN NUMBER);
END xx01_asn_email_pkg;
/
Package Body:
CREATE OR REPLACE PACKAGE BODY <pkg name> AS
-- -----------------------------------------------------------------------------
--
-- -----------------------------------------------------------------------------
--
-- Program Name : XX01.sql
-- Description : This package is used to send custom faxes with rightfax
--
-- Package Name : XX01
--
-- Change Record:
-- ---------------------------------------------------------------------------------------------------
-- Version Date Author Remarks
-- ---------------------------------------------------------------------------------------------------
g_conc_req_id VARCHAR2(150) := to_char(fnd_global.conc_request_id);
PROCEDURE delivery_asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,x_burst_request_id OUT NOCOPY NUMBER) IS
l_req_wait1 BOOLEAN;
l_req_wait2 BOOLEAN;
l_req_wait3 BOOLEAN;
l_phase_code VARCHAR2(100);
l_phase VARCHAR2(100);
l_dev_status VARCHAR2(100);
l_status_code VARCHAR2(100);
l_msg VARCHAR2(100);
l_burst_req_id fnd_concurrent_requests.request_id%TYPE; --Variable to hold "XML Publisher Report Bursting Program" Request Id
l_req_id fnd_concurrent_requests.request_id%TYPE; --Variable to hold "TBP Pack List Report - Email" Request Id
l_burst_status_code VARCHAR2(100);
l_burst_phase_code VARCHAR2(100);
l_burst_phase VARCHAR2(100);
l_burst_dev_status VARCHAR2(100);
l_burst_msg VARCHAR2(100);
l_burst_src_file VARCHAR2(500);
l_retcode NUMBER := 0;
l_errbuf VARCHAR2(100);
-- -------------------------------------
xml_layout BOOLEAN;
l_org_id NUMBER := 0;
l_set_of_book_id NUMBER;
-- -------------------------------------
BEGIN
fnd_file.put_line(fnd_file.log
,'---------------------------------------------------------------');
-- call the ADD_LAYOUT API prior to submitting the Printed Purchase Order Report
xml_layout := fnd_request.add_layout(template_appl_name => 'XX01'
,template_code => 'XX01_WSHRDPACK_EMAIL'
,template_language => 'en'
,template_territory => 'US'
,output_format => 'PDF');
--l_org_id := fnd_global.org_id;
--FND_FILE.PUT_LINE(FND_FILE.LOG,' org_id: '||l_org_id);
--fnd_request.set_org_id(l_org_id);
--Submit the TBP Pack List Report
-- Case # 130929 - removed parameter to accomodate change in Pack List Report Program change
fnd_file.put_line(fnd_file.log, 'Calling Pack List Report - Email');
l_req_id := fnd_request.submit_request('XX01'
,'XX01_WSHRDPACK_EMAIL'
,NULL
,NULL
,FALSE
,NULL
,NULL
, -- Passing NULL to delivery_id
NULL
,NULL
,'MSTK'
,'MTLL'
,g_conc_req_id);
COMMIT;
--Wait for the Program Completion
IF l_req_id > 0
THEN
l_phase := 'XX';
WHILE (upper(l_phase) <> 'COMPLETE')
LOOP
l_req_wait1 := fnd_concurrent.wait_for_request(request_id => l_req_id
,INTERVAL => 2
,max_wait => 0
,phase => l_phase_code
,status => l_status_code
,dev_phase => l_phase
,dev_status => l_dev_status
,message => l_msg);
EXIT WHEN upper(l_phase) = 'COMPLETE';
END LOOP;
END IF; -- l_req_id > 0
IF (upper(l_status_code) = 'NORMAL')
THEN
--Case# 151266: Added Profile option check before emailing
IF nvl(fnd_profile.value_specific(NAME => 'XX01_ENABLE_BURSTING_EMAIL'
,user_id => fnd_global.user_id
,responsibility_id => fnd_global.resp_id
,application_id => fnd_global.resp_appl_id
,org_id => fnd_global.org_id
,server_id => fnd_global.server_id)
,'N') = 'Y'
THEN
--Submit the XX01 SALES ORDER ACK CONTROL FILE to generate the control file for rightfax
fnd_file.put_line(fnd_file.log, 'Calling XML Publisher Report Bursting Program');
fnd_file.put_line(fnd_file.log, ' Reqest ID: ' || l_req_id);
l_burst_req_id := fnd_request.submit_request(application => 'XDO'
,program => 'XDOBURSTREP'
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => 'Y'
,argument2 => l_req_id
,argument3 => 'Y'
,argument4 => chr(0)
,argument5 => chr(0)
,argument6 => chr(0)
,argument7 => chr(0)
,argument8 => chr(0)
,argument9 => chr(0)
,argument10 => chr(0)
,argument11 => chr(0)
,argument12 => chr(0)
,argument13 => chr(0)
,argument14 => chr(0)
,argument15 => chr(0)
,argument16 => chr(0)
,argument17 => chr(0)
,argument18 => chr(0)
,argument19 => chr(0)
,argument20 => chr(0)
,argument21 => chr(0)
,argument22 => chr(0)
,argument23 => chr(0)
,argument24 => chr(0)
,argument25 => chr(0)
,argument26 => chr(0)
,argument27 => chr(0)
,argument28 => chr(0)
,argument29 => chr(0)
,argument30 => chr(0)
,argument31 => chr(0)
,argument32 => chr(0)
,argument33 => chr(0)
,argument34 => chr(0)
,argument35 => chr(0)
,argument36 => chr(0)
,argument37 => chr(0)
,argument38 => chr(0)
,argument39 => chr(0)
,argument40 => chr(0)
,argument41 => chr(0)
,argument42 => chr(0)
,argument43 => chr(0)
,argument44 => chr(0)
,argument45 => chr(0)
,argument46 => chr(0)
,argument47 => chr(0)
,argument48 => chr(0)
,argument49 => chr(0)
,argument50 => chr(0)
,argument51 => chr(0)
,argument52 => chr(0)
,argument53 => chr(0)
,argument54 => chr(0)
,argument55 => chr(0)
,argument56 => chr(0)
,argument57 => chr(0)
,argument58 => chr(0)
,argument59 => chr(0)
,argument60 => chr(0)
,argument61 => chr(0)
,argument62 => chr(0)
,argument63 => chr(0)
,argument64 => chr(0)
,argument65 => chr(0)
,argument66 => chr(0)
,argument67 => chr(0)
,argument68 => chr(0)
,argument69 => chr(0)
,argument70 => chr(0)
,argument71 => chr(0)
,argument72 => chr(0)
,argument73 => chr(0)
,argument74 => chr(0)
,argument75 => chr(0)
,argument76 => chr(0)
,argument77 => chr(0)
,argument78 => chr(0)
,argument79 => chr(0)
,argument80 => chr(0)
,argument81 => chr(0)
,argument82 => chr(0)
,argument83 => chr(0)
,argument84 => chr(0)
,argument85 => chr(0)
,argument86 => chr(0)
,argument87 => chr(0)
,argument88 => chr(0)
,argument89 => chr(0)
,argument90 => chr(0)
,argument91 => chr(0)
,argument92 => chr(0)
,argument93 => chr(0)
,argument94 => chr(0)
,argument95 => chr(0)
,argument96 => chr(0)
,argument97 => chr(0)
,argument98 => chr(0)
,argument99 => chr(0)
,argument100 => chr(0));
COMMIT;
--Wait for the Program Completion
IF l_burst_req_id > 0
THEN
l_burst_phase := 'XX';
WHILE (upper(l_burst_phase) <> 'COMPLETE')
LOOP
l_req_wait2 := fnd_concurrent.wait_for_request(request_id => l_burst_req_id
,INTERVAL => 2
,max_wait => 0
,phase => l_burst_phase_code
,status => l_burst_status_code
,dev_phase => l_burst_phase
,dev_status => l_burst_dev_status
,message => l_burst_msg);
EXIT WHEN upper(l_burst_phase) = 'COMPLETE';
END LOOP;
l_burst_src_file := 'o' || l_burst_req_id || '.out';
x_burst_request_id := l_burst_req_id;
END IF; -- l_burst_req_id > 0
fnd_file.put_line(fnd_file.log, ' Burst Program Req ID: ' || l_burst_req_id);
fnd_file.put_line(fnd_file.log, ' Burst Program request Status: ' || l_burst_status_code);
--
ELSE
fnd_file.put_line(fnd_file.log
,'***Profile Option XX01: Enable Report Bursting Email set to "No", Bursting program NOT submitted');
END IF; --if FND_PROFILE.VALUE_SPECIFIC() = 'Y'
END IF; --IF (UPPER (l_status_code) = 'NORMAL') THEN
IF (upper(l_burst_status_code) = 'ERROR')
OR (upper(l_status_code) = 'ERROR')
THEN
x_retcode := 2; --Setting the Concurrent program status to Error
x_errbuf := 'Burst Program/Pack List - Email programs completed in error';
ELSIF (upper(l_burst_status_code) = 'WARNING')
OR (upper(l_status_code) = 'WARNING')
THEN
x_retcode := 1; --Setting the Concurrent program status to Warning
x_errbuf := 'Burst Program/Pack List - Email programs completed in warning';
ELSIF (upper(l_burst_status_code) = 'NORMAL')
OR (upper(l_status_code) = 'NORMAL')
THEN
x_retcode := 0; --Setting the Concurrent program status to Normal
x_errbuf := 'Program completed normally';
END IF;
COMMIT;
fnd_file.put_line(fnd_file.log, 'End of delivery_ASN_Email_Main');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error in delivery_ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END delivery_asn_email_main;
-- --------------------------------------------------------------------------
PROCEDURE asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,p_ou IN NUMBER
,p_order_num_from IN NUMBER
,p_order_num_to IN NUMBER
,p_ship_date_from IN VARCHAR2
,p_ship_date_to IN VARCHAR2
,p_delivery_from IN NUMBER
,p_delivery_to IN NUMBER) IS
CURSOR get_confirmed_delivery(p_ou NUMBER
,p_order_num_from NUMBER
,p_order_num_to NUMBER
,p_ship_date_from DATE
,p_ship_date_to DATE
,p_delivery_from NUMBER
,p_delivery_to NUMBER) IS
SELECT DISTINCT c.delivery_id
FROM wsh_delivery_assignments a
,wsh_delivery_details b
,wsh_new_deliveries c
,oe_order_headers_all d
,oe_order_lines_all f
--,hz_cust_site_uses_all g --*<3.0> Commented
--,hz_cust_acct_sites_all h --*<3.0> Commented
WHERE a.delivery_id = c.delivery_id
AND a.delivery_detail_id = b.delivery_detail_id
AND b.source_header_id = d.header_id
AND b.source_code = 'OE'
AND d.header_id = f.header_id
AND b.source_line_id = f.line_id
--AND g.cust_acct_site_id = h.cust_acct_site_id --*<3.0> Commented
--AND d.ship_to_org_id = g.site_use_id --*<3.0> Commented
--*<3.0> Added an EXISTS condition
AND EXISTS (SELECT 1
FROM hz_cust_site_uses_all ship_su
,hz_cust_acct_sites_all ship_cas
,hz_cust_site_uses_all bill_su
,hz_cust_acct_sites_all bill_cas
WHERE ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_su.site_use_id = d.ship_to_org_id
--
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id
AND bill_su.site_use_id = d.invoice_to_org_id
--
AND decode(nvl(ship_cas.attribute3, 'NULL')
,'NULL'
,nvl(bill_cas.attribute3, 'NULL')
,ship_cas.attribute3) = 'EMAIL')
--and d.invoice_to_org_id = g.site_use_id
AND c.status_code = 'CL'
--AND h.attribute3 = 'EMAIL' --'FAX' --*<3.0> Commented
AND d.org_id = nvl(p_ou, d.org_id)
AND trunc(c.confirm_date) BETWEEN p_ship_date_from AND p_ship_date_to
AND d.order_number BETWEEN p_order_num_from AND p_order_num_to
AND c.delivery_id BETWEEN p_delivery_from AND p_delivery_to;
l_order_num_from NUMBER;
l_order_num_to NUMBER;
l_delivery_from NUMBER;
l_delivery_to NUMBER;
l_ship_date_from DATE;
l_ship_date_to DATE;
x_burst_req_id NUMBER := 0;
l_burst_flag NUMBER := 0;
BEGIN
x_retcode := 0;
IF p_ship_date_from IS NULL
AND p_ship_date_to IS NULL
AND p_order_num_from IS NULL
AND p_order_num_to IS NULL
AND p_delivery_from IS NULL
AND p_delivery_to IS NULL
THEN
x_retcode := 2;
x_errbuf := 'Order number, Ship date, Delivery all null,' ||
'please enter order number/ship date/deliver.';
fnd_file.put_line(fnd_file.log, x_errbuf);
RETURN;
END IF;
IF p_ship_date_from IS NOT NULL
THEN
l_ship_date_from := trunc(fnd_date.canonical_to_date(p_ship_date_from));
ELSE
l_ship_date_from := trunc(SYSDATE) - 360;
END IF;
fnd_file.put_line(fnd_file.log
,'Date from: ' || to_char(l_ship_date_from, 'DD-MON-YYYY HH:MI:SS'));
IF p_ship_date_to IS NOT NULL
THEN
l_ship_date_to := trunc(fnd_date.canonical_to_date(p_ship_date_to));
ELSE
l_ship_date_to := trunc(SYSDATE);
END IF;
fnd_file.put_line(fnd_file.log, 'Date To: ' || to_char(l_ship_date_to, 'DD-MON-YYYY HH:MI:SS'));
IF p_order_num_from IS NULL
THEN
l_order_num_from := -9999;
ELSE
l_order_num_from := p_order_num_from;
END IF;
fnd_file.put_line(fnd_file.log, 'Order From: ' || l_order_num_from);
IF p_order_num_to IS NULL
THEN
l_order_num_to := 99999999999;
ELSE
l_order_num_to := p_order_num_to;
END IF;
fnd_file.put_line(fnd_file.log, 'Order To: ' || l_order_num_to);
IF p_delivery_from IS NULL
THEN
l_delivery_from := -9999;
ELSE
l_delivery_from := p_delivery_from;
END IF;
fnd_file.put_line(fnd_file.log, 'Delivery From: ' || l_delivery_from);
IF p_delivery_to IS NULL
THEN
l_delivery_to := 99999999999;
ELSE
l_delivery_to := p_delivery_to;
END IF;
fnd_file.put_line(fnd_file.log, 'Delivery To: ' || l_delivery_to);
FOR x IN get_confirmed_delivery(p_ou
,l_order_num_from
,l_order_num_to
,l_ship_date_from
,l_ship_date_to
,l_delivery_from
,l_delivery_to)
LOOP
l_burst_flag := 1;
BEGIN
UPDATE wsh_new_deliveries
SET --*<ADTI-269>
/*attribute1 = 'N'*/ attribute3 = g_conc_req_id --setting a flag - so that the ASN Email program would pick these records
WHERE delivery_id = x.delivery_id;
fnd_file.put_line(fnd_file.log
,'Updated wsh_new_deliveries for Delivery ID: ' || x.delivery_id);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log
,'Error updating wsh_new_deliveries in ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END;
END LOOP;
IF l_burst_flag = 1 --Call subsequent program only when there are eligible records from the cursor
THEN
delivery_asn_email_main(x_errbuf, x_retcode, x_burst_req_id);
ELSE
fnd_file.put_line(fnd_file.log
,'****** No eligible records found for the given input parameters. ******');
END IF;
IF x_retcode = 0
THEN
FOR x IN get_confirmed_delivery(p_ou
,l_order_num_from
,l_order_num_to
,l_ship_date_from
,l_ship_date_to
,l_delivery_from
,l_delivery_to)
LOOP
BEGIN
UPDATE wsh_new_deliveries
SET --*<ADTI-269>
/*attribute1*/ attribute3 = g_conc_req_id || ': Y' || '; Req ID; ' ||
x_burst_req_id || '; ' ||
to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') --Processed the record
WHERE delivery_id = x.delivery_id;
fnd_file.put_line(fnd_file.log
,'Updated wsh_new_deliveries after burst program for Delivery ID: ' ||
x.delivery_id);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log
,'Error updating wsh_new_deliveries in ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error in ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END asn_email_main;
END xx01_asn_email_pkg;
/
A main program say Email ASN is an stored procedure upon submitting this program it will submit an rdf program with bursting xml program to receive email
Package Specification:
CREATE OR REPLACE PACKAGE <pkg name> AUTHID CURRENT_USER AS
-- -----------------------------------------------------------------------------
--
-- -----------------------------------------------------------------------------
--
-- Program Name : XX01.sql
-- Description : This package is used to send ASN in Email
--
-- Package Name :
-- Change Record:
-- -----------------------------------------------------------------------------
-- Version Date Author Remarks
--
-- -----------------------------------------------------------------------------
PROCEDURE delivery_asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,x_burst_request_id OUT NOCOPY NUMBER);
PROCEDURE asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,p_ou IN NUMBER
,p_order_num_from IN NUMBER
,p_order_num_to IN NUMBER
,p_ship_date_from IN VARCHAR2
,p_ship_date_to IN VARCHAR2
,p_delivery_from IN NUMBER
,p_delivery_to IN NUMBER);
END xx01_asn_email_pkg;
/
Package Body:
CREATE OR REPLACE PACKAGE BODY <pkg name> AS
-- -----------------------------------------------------------------------------
--
-- -----------------------------------------------------------------------------
--
-- Program Name : XX01.sql
-- Description : This package is used to send custom faxes with rightfax
--
-- Package Name : XX01
--
-- Change Record:
-- ---------------------------------------------------------------------------------------------------
-- Version Date Author Remarks
-- ---------------------------------------------------------------------------------------------------
g_conc_req_id VARCHAR2(150) := to_char(fnd_global.conc_request_id);
PROCEDURE delivery_asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,x_burst_request_id OUT NOCOPY NUMBER) IS
l_req_wait1 BOOLEAN;
l_req_wait2 BOOLEAN;
l_req_wait3 BOOLEAN;
l_phase_code VARCHAR2(100);
l_phase VARCHAR2(100);
l_dev_status VARCHAR2(100);
l_status_code VARCHAR2(100);
l_msg VARCHAR2(100);
l_burst_req_id fnd_concurrent_requests.request_id%TYPE; --Variable to hold "XML Publisher Report Bursting Program" Request Id
l_req_id fnd_concurrent_requests.request_id%TYPE; --Variable to hold "TBP Pack List Report - Email" Request Id
l_burst_status_code VARCHAR2(100);
l_burst_phase_code VARCHAR2(100);
l_burst_phase VARCHAR2(100);
l_burst_dev_status VARCHAR2(100);
l_burst_msg VARCHAR2(100);
l_burst_src_file VARCHAR2(500);
l_retcode NUMBER := 0;
l_errbuf VARCHAR2(100);
-- -------------------------------------
xml_layout BOOLEAN;
l_org_id NUMBER := 0;
l_set_of_book_id NUMBER;
-- -------------------------------------
BEGIN
fnd_file.put_line(fnd_file.log
,'---------------------------------------------------------------');
-- call the ADD_LAYOUT API prior to submitting the Printed Purchase Order Report
xml_layout := fnd_request.add_layout(template_appl_name => 'XX01'
,template_code => 'XX01_WSHRDPACK_EMAIL'
,template_language => 'en'
,template_territory => 'US'
,output_format => 'PDF');
--l_org_id := fnd_global.org_id;
--FND_FILE.PUT_LINE(FND_FILE.LOG,' org_id: '||l_org_id);
--fnd_request.set_org_id(l_org_id);
--Submit the TBP Pack List Report
-- Case # 130929 - removed parameter to accomodate change in Pack List Report Program change
fnd_file.put_line(fnd_file.log, 'Calling Pack List Report - Email');
l_req_id := fnd_request.submit_request('XX01'
,'XX01_WSHRDPACK_EMAIL'
,NULL
,NULL
,FALSE
,NULL
,NULL
, -- Passing NULL to delivery_id
NULL
,NULL
,'MSTK'
,'MTLL'
,g_conc_req_id);
COMMIT;
--Wait for the Program Completion
IF l_req_id > 0
THEN
l_phase := 'XX';
WHILE (upper(l_phase) <> 'COMPLETE')
LOOP
l_req_wait1 := fnd_concurrent.wait_for_request(request_id => l_req_id
,INTERVAL => 2
,max_wait => 0
,phase => l_phase_code
,status => l_status_code
,dev_phase => l_phase
,dev_status => l_dev_status
,message => l_msg);
EXIT WHEN upper(l_phase) = 'COMPLETE';
END LOOP;
END IF; -- l_req_id > 0
IF (upper(l_status_code) = 'NORMAL')
THEN
--Case# 151266: Added Profile option check before emailing
IF nvl(fnd_profile.value_specific(NAME => 'XX01_ENABLE_BURSTING_EMAIL'
,user_id => fnd_global.user_id
,responsibility_id => fnd_global.resp_id
,application_id => fnd_global.resp_appl_id
,org_id => fnd_global.org_id
,server_id => fnd_global.server_id)
,'N') = 'Y'
THEN
--Submit the XX01 SALES ORDER ACK CONTROL FILE to generate the control file for rightfax
fnd_file.put_line(fnd_file.log, 'Calling XML Publisher Report Bursting Program');
fnd_file.put_line(fnd_file.log, ' Reqest ID: ' || l_req_id);
l_burst_req_id := fnd_request.submit_request(application => 'XDO'
,program => 'XDOBURSTREP'
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => 'Y'
,argument2 => l_req_id
,argument3 => 'Y'
,argument4 => chr(0)
,argument5 => chr(0)
,argument6 => chr(0)
,argument7 => chr(0)
,argument8 => chr(0)
,argument9 => chr(0)
,argument10 => chr(0)
,argument11 => chr(0)
,argument12 => chr(0)
,argument13 => chr(0)
,argument14 => chr(0)
,argument15 => chr(0)
,argument16 => chr(0)
,argument17 => chr(0)
,argument18 => chr(0)
,argument19 => chr(0)
,argument20 => chr(0)
,argument21 => chr(0)
,argument22 => chr(0)
,argument23 => chr(0)
,argument24 => chr(0)
,argument25 => chr(0)
,argument26 => chr(0)
,argument27 => chr(0)
,argument28 => chr(0)
,argument29 => chr(0)
,argument30 => chr(0)
,argument31 => chr(0)
,argument32 => chr(0)
,argument33 => chr(0)
,argument34 => chr(0)
,argument35 => chr(0)
,argument36 => chr(0)
,argument37 => chr(0)
,argument38 => chr(0)
,argument39 => chr(0)
,argument40 => chr(0)
,argument41 => chr(0)
,argument42 => chr(0)
,argument43 => chr(0)
,argument44 => chr(0)
,argument45 => chr(0)
,argument46 => chr(0)
,argument47 => chr(0)
,argument48 => chr(0)
,argument49 => chr(0)
,argument50 => chr(0)
,argument51 => chr(0)
,argument52 => chr(0)
,argument53 => chr(0)
,argument54 => chr(0)
,argument55 => chr(0)
,argument56 => chr(0)
,argument57 => chr(0)
,argument58 => chr(0)
,argument59 => chr(0)
,argument60 => chr(0)
,argument61 => chr(0)
,argument62 => chr(0)
,argument63 => chr(0)
,argument64 => chr(0)
,argument65 => chr(0)
,argument66 => chr(0)
,argument67 => chr(0)
,argument68 => chr(0)
,argument69 => chr(0)
,argument70 => chr(0)
,argument71 => chr(0)
,argument72 => chr(0)
,argument73 => chr(0)
,argument74 => chr(0)
,argument75 => chr(0)
,argument76 => chr(0)
,argument77 => chr(0)
,argument78 => chr(0)
,argument79 => chr(0)
,argument80 => chr(0)
,argument81 => chr(0)
,argument82 => chr(0)
,argument83 => chr(0)
,argument84 => chr(0)
,argument85 => chr(0)
,argument86 => chr(0)
,argument87 => chr(0)
,argument88 => chr(0)
,argument89 => chr(0)
,argument90 => chr(0)
,argument91 => chr(0)
,argument92 => chr(0)
,argument93 => chr(0)
,argument94 => chr(0)
,argument95 => chr(0)
,argument96 => chr(0)
,argument97 => chr(0)
,argument98 => chr(0)
,argument99 => chr(0)
,argument100 => chr(0));
COMMIT;
--Wait for the Program Completion
IF l_burst_req_id > 0
THEN
l_burst_phase := 'XX';
WHILE (upper(l_burst_phase) <> 'COMPLETE')
LOOP
l_req_wait2 := fnd_concurrent.wait_for_request(request_id => l_burst_req_id
,INTERVAL => 2
,max_wait => 0
,phase => l_burst_phase_code
,status => l_burst_status_code
,dev_phase => l_burst_phase
,dev_status => l_burst_dev_status
,message => l_burst_msg);
EXIT WHEN upper(l_burst_phase) = 'COMPLETE';
END LOOP;
l_burst_src_file := 'o' || l_burst_req_id || '.out';
x_burst_request_id := l_burst_req_id;
END IF; -- l_burst_req_id > 0
fnd_file.put_line(fnd_file.log, ' Burst Program Req ID: ' || l_burst_req_id);
fnd_file.put_line(fnd_file.log, ' Burst Program request Status: ' || l_burst_status_code);
--
ELSE
fnd_file.put_line(fnd_file.log
,'***Profile Option XX01: Enable Report Bursting Email set to "No", Bursting program NOT submitted');
END IF; --if FND_PROFILE.VALUE_SPECIFIC() = 'Y'
END IF; --IF (UPPER (l_status_code) = 'NORMAL') THEN
IF (upper(l_burst_status_code) = 'ERROR')
OR (upper(l_status_code) = 'ERROR')
THEN
x_retcode := 2; --Setting the Concurrent program status to Error
x_errbuf := 'Burst Program/Pack List - Email programs completed in error';
ELSIF (upper(l_burst_status_code) = 'WARNING')
OR (upper(l_status_code) = 'WARNING')
THEN
x_retcode := 1; --Setting the Concurrent program status to Warning
x_errbuf := 'Burst Program/Pack List - Email programs completed in warning';
ELSIF (upper(l_burst_status_code) = 'NORMAL')
OR (upper(l_status_code) = 'NORMAL')
THEN
x_retcode := 0; --Setting the Concurrent program status to Normal
x_errbuf := 'Program completed normally';
END IF;
COMMIT;
fnd_file.put_line(fnd_file.log, 'End of delivery_ASN_Email_Main');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error in delivery_ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END delivery_asn_email_main;
-- --------------------------------------------------------------------------
PROCEDURE asn_email_main(x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,p_ou IN NUMBER
,p_order_num_from IN NUMBER
,p_order_num_to IN NUMBER
,p_ship_date_from IN VARCHAR2
,p_ship_date_to IN VARCHAR2
,p_delivery_from IN NUMBER
,p_delivery_to IN NUMBER) IS
CURSOR get_confirmed_delivery(p_ou NUMBER
,p_order_num_from NUMBER
,p_order_num_to NUMBER
,p_ship_date_from DATE
,p_ship_date_to DATE
,p_delivery_from NUMBER
,p_delivery_to NUMBER) IS
SELECT DISTINCT c.delivery_id
FROM wsh_delivery_assignments a
,wsh_delivery_details b
,wsh_new_deliveries c
,oe_order_headers_all d
,oe_order_lines_all f
--,hz_cust_site_uses_all g --*<3.0> Commented
--,hz_cust_acct_sites_all h --*<3.0> Commented
WHERE a.delivery_id = c.delivery_id
AND a.delivery_detail_id = b.delivery_detail_id
AND b.source_header_id = d.header_id
AND b.source_code = 'OE'
AND d.header_id = f.header_id
AND b.source_line_id = f.line_id
--AND g.cust_acct_site_id = h.cust_acct_site_id --*<3.0> Commented
--AND d.ship_to_org_id = g.site_use_id --*<3.0> Commented
--*<3.0> Added an EXISTS condition
AND EXISTS (SELECT 1
FROM hz_cust_site_uses_all ship_su
,hz_cust_acct_sites_all ship_cas
,hz_cust_site_uses_all bill_su
,hz_cust_acct_sites_all bill_cas
WHERE ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_su.site_use_id = d.ship_to_org_id
--
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id
AND bill_su.site_use_id = d.invoice_to_org_id
--
AND decode(nvl(ship_cas.attribute3, 'NULL')
,'NULL'
,nvl(bill_cas.attribute3, 'NULL')
,ship_cas.attribute3) = 'EMAIL')
--and d.invoice_to_org_id = g.site_use_id
AND c.status_code = 'CL'
--AND h.attribute3 = 'EMAIL' --'FAX' --*<3.0> Commented
AND d.org_id = nvl(p_ou, d.org_id)
AND trunc(c.confirm_date) BETWEEN p_ship_date_from AND p_ship_date_to
AND d.order_number BETWEEN p_order_num_from AND p_order_num_to
AND c.delivery_id BETWEEN p_delivery_from AND p_delivery_to;
l_order_num_from NUMBER;
l_order_num_to NUMBER;
l_delivery_from NUMBER;
l_delivery_to NUMBER;
l_ship_date_from DATE;
l_ship_date_to DATE;
x_burst_req_id NUMBER := 0;
l_burst_flag NUMBER := 0;
BEGIN
x_retcode := 0;
IF p_ship_date_from IS NULL
AND p_ship_date_to IS NULL
AND p_order_num_from IS NULL
AND p_order_num_to IS NULL
AND p_delivery_from IS NULL
AND p_delivery_to IS NULL
THEN
x_retcode := 2;
x_errbuf := 'Order number, Ship date, Delivery all null,' ||
'please enter order number/ship date/deliver.';
fnd_file.put_line(fnd_file.log, x_errbuf);
RETURN;
END IF;
IF p_ship_date_from IS NOT NULL
THEN
l_ship_date_from := trunc(fnd_date.canonical_to_date(p_ship_date_from));
ELSE
l_ship_date_from := trunc(SYSDATE) - 360;
END IF;
fnd_file.put_line(fnd_file.log
,'Date from: ' || to_char(l_ship_date_from, 'DD-MON-YYYY HH:MI:SS'));
IF p_ship_date_to IS NOT NULL
THEN
l_ship_date_to := trunc(fnd_date.canonical_to_date(p_ship_date_to));
ELSE
l_ship_date_to := trunc(SYSDATE);
END IF;
fnd_file.put_line(fnd_file.log, 'Date To: ' || to_char(l_ship_date_to, 'DD-MON-YYYY HH:MI:SS'));
IF p_order_num_from IS NULL
THEN
l_order_num_from := -9999;
ELSE
l_order_num_from := p_order_num_from;
END IF;
fnd_file.put_line(fnd_file.log, 'Order From: ' || l_order_num_from);
IF p_order_num_to IS NULL
THEN
l_order_num_to := 99999999999;
ELSE
l_order_num_to := p_order_num_to;
END IF;
fnd_file.put_line(fnd_file.log, 'Order To: ' || l_order_num_to);
IF p_delivery_from IS NULL
THEN
l_delivery_from := -9999;
ELSE
l_delivery_from := p_delivery_from;
END IF;
fnd_file.put_line(fnd_file.log, 'Delivery From: ' || l_delivery_from);
IF p_delivery_to IS NULL
THEN
l_delivery_to := 99999999999;
ELSE
l_delivery_to := p_delivery_to;
END IF;
fnd_file.put_line(fnd_file.log, 'Delivery To: ' || l_delivery_to);
FOR x IN get_confirmed_delivery(p_ou
,l_order_num_from
,l_order_num_to
,l_ship_date_from
,l_ship_date_to
,l_delivery_from
,l_delivery_to)
LOOP
l_burst_flag := 1;
BEGIN
UPDATE wsh_new_deliveries
SET --*<ADTI-269>
/*attribute1 = 'N'*/ attribute3 = g_conc_req_id --setting a flag - so that the ASN Email program would pick these records
WHERE delivery_id = x.delivery_id;
fnd_file.put_line(fnd_file.log
,'Updated wsh_new_deliveries for Delivery ID: ' || x.delivery_id);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log
,'Error updating wsh_new_deliveries in ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END;
END LOOP;
IF l_burst_flag = 1 --Call subsequent program only when there are eligible records from the cursor
THEN
delivery_asn_email_main(x_errbuf, x_retcode, x_burst_req_id);
ELSE
fnd_file.put_line(fnd_file.log
,'****** No eligible records found for the given input parameters. ******');
END IF;
IF x_retcode = 0
THEN
FOR x IN get_confirmed_delivery(p_ou
,l_order_num_from
,l_order_num_to
,l_ship_date_from
,l_ship_date_to
,l_delivery_from
,l_delivery_to)
LOOP
BEGIN
UPDATE wsh_new_deliveries
SET --*<ADTI-269>
/*attribute1*/ attribute3 = g_conc_req_id || ': Y' || '; Req ID; ' ||
x_burst_req_id || '; ' ||
to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') --Processed the record
WHERE delivery_id = x.delivery_id;
fnd_file.put_line(fnd_file.log
,'Updated wsh_new_deliveries after burst program for Delivery ID: ' ||
x.delivery_id);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log
,'Error updating wsh_new_deliveries in ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error in ASN_Email_Main: ' || SQLERRM);
x_retcode := 2;
END asn_email_main;
END xx01_asn_email_pkg;
/
No comments:
Post a Comment