Master OracleApps

Pick Confirm using API:

INV_PICK_WAVE_PICK_CONFIRM_PUB.PICK_CONFIRM

CREATE OR REPLACE PROCEDURE xxmto_oe_pick_confirm_prc (

p_out_chr_errbuf OUT NOCOPY VARCHAR2,

p_out_num_retcode OUT NOCOPY VARCHAR2,

p_in_chr_ou IN VARCHAR2,

p_in_num_ordernum IN NUMBER,

p_in_num_linenum IN NUMBER,

p_in_chr_errorflag IN VARCHAR2

)

/*******************************************************************************

* Filename : XXMTO_OE_PICK_CONFIRM_PRC.prc *

* *

* Type : PACKAGE SPEC *

* *

* Author : XXXX *

* *

* NAME : XXMTO_OE_PICK_CONFIRM_PRC *

* *

* PURPOSE : This procedure will call pick confirm api *

* *

* PARAMETERS : p_out_chr_errbuf OUT -- Standard SRS *

* p_out_num_retcode OUT -- Standard SRS *

* p_in_chr_ou IN *

* p_in_num_ordernum IN *

* p_in_num_linenum IN *

* p_in_chr_errorflag IN *

* *

* AUTHOR DATE VERSION SC# DESCRIPTION *

********************************************************************************

* XXXX XX-XXX-XXXX 1.0 Initial Version *

*******************************************************************************/

-- Local Variables

l_num_organization_id hr_all_organization_units.organization_id%TYPE

:= NULL;

l_num_header_id oe_order_headers_all.header_id%TYPE := NULL;

l_num_line_number oe_order_lines_all.line_number%TYPE := NULL;

x_return_status VARCHAR2 (10);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (2000);

l_trohdr_rec inv_move_order_pub.trohdr_rec_type;

l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;

l_trolin_tbl inv_move_order_pub.trolin_tbl_type;

l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;

x_trolin_tbl inv_move_order_pub.trolin_tbl_type;

x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;

x_trohdr_rec inv_move_order_pub.trohdr_rec_type;

x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;

l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;

l_chr_error_flag VARCHAR2 (2) := 'N';

l_chr_error_msg VARCHAR2 (2000) := NULL;

l_chr_errbuf VARCHAR2 (3000) := NULL;

l_num_retcode NUMBER := NULL;

l_num_user_id NUMBER;

l_num_resp_id NUMBER;

l_num_resp_appl_id NUMBER;

l_num_record_count NUMBER;

l_num_global_user_id NUMBER := fnd_global.user_id;

l_num_last_updated NUMBER;

-- Cursor to fetch Staging table details

CURSOR cur_pick_confirm_details (

c_num_ord_num NUMBER,

c_num_line_num NUMBER,

c_chr_operating_unit VARCHAR2,

c_chr_error_flag VARCHAR2

)

SELECT operating_unit, order_num, line_number, error_flag, ROWID

FROM xxmto.xxmto_oe_pick_confirm_stg

WHERE ( operating_unit = NVL (c_chr_operating_unit, operating_unit)

OR operating_unit IS NULL

)

AND order_num = NVL (c_num_ord_num, order_num)

AND ( line_number = NVL (c_num_line_num, line_number)

OR line_number IS NULL

)

AND error_flag = NVL (c_chr_error_flag, error_flag)

AND process_flag <> 'PS';

-- Cursor to fetch move order details

CURSOR cur_sales_order_details (

c_num_ord_num NUMBER,

c_num_line_num NUMBER,

c_num_org_id NUMBER

)

SELECT ooha.order_number order_number, ooha.orig_sys_document_ref,

ooha.header_id order_header_id, oola.line_id order_line_id,

oola.line_number line_number, mtrlv.request_number mo_number,

mtrlv.header_id mo_header_id, mtrlv.line_id mo_line_id,

mtrlv.move_order_type, mtrlv.quantity

FROM apps.oe_order_headers_all ooha,

apps.oe_order_lines_all oola,

apps.mtl_txn_request_lines_v mtrlv

WHERE 1 = 1

AND ooha.header_id = oola.header_id

AND mtrlv.txn_source_line_id = oola.line_id

AND mtrlv.line_status IN (3, 7)

AND ooha.order_number = c_num_ord_num

AND oola.line_number = NVL (c_num_line_num, oola.line_number)

AND ooha.org_id = NVL (c_num_org_id, ooha.org_id);

/*******************************************************************************

* NAME : xxmto_email_prc *

* *

* PURPOSE : This procedure will pass subject, body and send email *

* *

* PARAMETERS : p_out_chr_errbuf OUT -- Standard SRS *

* p_out_num_retcode OUT -- Standard SRS *

* p_in_chr_subject IN *

* p_in_lon_bodytext IN *

* *

*******************************************************************************/

PROCEDURE xxmto_email_prc (

p_out_chr_errbuf OUT NOCOPY VARCHAR2,

p_out_num_retcode OUT NOCOPY VARCHAR2,

p_in_chr_subject IN VARCHAR2,

p_in_chr_bodytext IN VARCHAR2

)

--Local Variables

l_chr_error_mesasge VARCHAR2 (2000);

l_num_retcode NUMBER;

l_chr_from_email VARCHAR2 (200);

l_chr_to_email VARCHAR2 (200);

l_chr_cc_email VARCHAR2 (200);

l_chr_bcc_email VARCHAR2 (200);

BEGIN

-- Fetching the email id's to send email when program failed

BEGIN

SELECT attribute1, attribute2, attribute3,

attribute4

INTO l_chr_from_email, l_chr_to_email, l_chr_cc_email,

l_chr_bcc_email

FROM fnd_lookup_values

WHERE lookup_type = 'XXMTO_ALL_INTERFACE_DETAILS'

AND meaning = 'Order Pick Confirm Interface'

AND LANGUAGE = 'US'

AND enabled_flag = 'Y'

AND attribute_category = 'Email';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'Error in Email Lookup Setup');

p_out_num_retcode := 1;

p_out_chr_errbuf := 'Error in Email Lookup Setup';

END;

BEGIN

xxmto_utl_smtp_email_pkg.xxmto_send_email_prc

(p_out_chr_errbuf => l_chr_error_mesasge,

p_out_num_retcode => l_num_retcode,

p_in_chr_sender => l_chr_from_email,

p_in_chr_recipients => l_chr_to_email,

p_in_chr_subject => p_in_chr_subject,

p_in_chr_cc => l_chr_cc_email,

p_in_chr_bcc => l_chr_bcc_email,

p_in_clb_message => p_in_chr_bodytext

);

IF l_num_retcode != 0 OR l_num_retcode IS NOT NULL

THEN

fnd_file.put_line

(fnd_file.LOG,

'Error in Sending Mail Program Completion : '

|| l_chr_error_mesasge

);

p_out_chr_errbuf :=

'Error in Sending Mail Program Completion : '

|| l_chr_error_mesasge;

p_out_num_retcode := l_num_retcode;

END IF;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Unexpected Error in Sending Email : '

|| SQLERRM

);

p_out_chr_errbuf :=

'Unexpected Error in Sending Email : ' || SQLERRM;

p_out_num_retcode := 1;

END;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Unexpected Error in xxmto_email_prc: ' || SQLERRM

);

p_out_num_retcode := 1;

p_out_chr_errbuf := 'WARNING';

END xxmto_email_prc;

BEGIN

/*BEGIN

l_num_user_id:= NULL;

SELECT user_id

INTO l_num_user_id

FROM fnd_user

WHERE user_id = fnd_global.user_id;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Unexpected Error in fetching user id: '

|| SQLERRM

);

END;

IF (l_num_user_id < 0 OR l_num_user_id IS NULL)

THEN

BEGIN

l_num_user_id:= NULL;

SELECT user_id

INTO l_num_user_id

FROM fnd_user

WHERE user_name = 'XXMTO_INTERFACE_USER';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Unexpected Error in fetching user id: '

|| SQLERRM

);

END;

END IF;*/

BEGIN

SELECT TO_NUMBER (attribute1),

TO_NUMBER (attribute2),

TO_NUMBER (attribute3)

INTO l_num_user_id,

l_num_resp_id,

l_num_resp_appl_id

FROM fnd_lookup_values

WHERE enabled_flag = 'Y'

AND lookup_type = 'XXMTO_ALL_INTF_APPS_DETAILS'

AND attribute_category = 'Apps'

AND meaning = 'Order Pick Confirm Interface';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,'Apps Initialize is not defined');

xxmto_email_prc

(l_chr_errbuf,

l_num_retcode,

'ATTENTION: Action Required for Error in Order Pick Confirm Interface for Order Number - '

|| p_in_num_ordernum,

'Hello,'

|| ' '

|| 'Error occured for Order Number : '

|| p_in_num_ordernum

|| ' '

|| 'Error Message :'

|| CHR (9)

|| 'Apps Initialize is not defined'

|| ' '

|| '**This is system generated mail. Please do not reply to the mail.'

);

p_out_chr_errbuf := 'Warning';

p_out_num_retcode := 1;

END;

IF l_num_global_user_id < 0

THEN

l_num_last_updated := l_num_user_id;

ELSE

l_num_last_updated := l_num_global_user_id;

END IF;

-- INITIALIZATION REQUIRED FOR R12

BEGIN

IF fnd_global.session_id = 0 OR fnd_global.session_id = -1

THEN

fnd_global.apps_initialize (l_num_last_updated,

l_num_resp_id,

l_num_resp_appl_id);

END IF;

END;

FOR rec_pick_confirm_details IN

cur_pick_confirm_details (p_in_num_ordernum,

p_in_num_linenum,

p_in_chr_ou,

p_in_chr_errorflag

)

LOOP

--fnd_file.put_line(fnd_file.log,'In cursor') ;

-- Validation of Operating Unit

IF rec_pick_confirm_details.operating_unit IS NOT NULL

THEN

BEGIN

SELECT organization_id

INTO l_num_organization_id

FROM hr_all_organization_units

WHERE UPPER (NAME) =

UPPER (rec_pick_confirm_details.operating_unit)

AND TRUNC (SYSDATE) BETWEEN TRUNC (date_from)

AND TRUNC (NVL (date_to, SYSDATE));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_chr_error_flag := 'E';

l_chr_error_msg :=

'Validation failed at Operating unit, '

|| 'Operating doesnot exist: '

|| '-'

|| rec_pick_confirm_details.operating_unit;

fnd_file.put_line (fnd_file.LOG, l_chr_error_msg);

WHEN OTHERS

THEN

l_chr_error_flag := 'E';

l_chr_error_msg :=

'Unexpected Error in Validation of Operating unit : '

|| rec_pick_confirm_details.operating_unit

|| SQLERRM;

fnd_file.put_line (fnd_file.LOG, l_chr_error_msg);

END;

END IF;

--fnd_file.put_line(fnd_file.log,'In l_num_organization_id') ;

-- IF l_num_organization_id IS NOT NULL

-- THEN

-- Validation of Order Number

BEGIN

SELECT header_id

INTO l_num_header_id

FROM oe_order_headers_all

WHERE order_number = rec_pick_confirm_details.order_num

AND org_id = NVL (l_num_organization_id, org_id);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_chr_error_flag := 'E';

l_chr_error_msg :=

'Validation failed at Order Number,'

|| 'Order Number doesnot exist: '

|| rec_pick_confirm_details.operating_unit

|| ' - '

|| rec_pick_confirm_details.order_num;

fnd_file.put_line (fnd_file.LOG, l_chr_error_msg);

WHEN OTHERS

THEN

l_chr_error_flag := 'E';

l_chr_error_msg :=

'Unexpected Error in Validation of Order Number: '

|| rec_pick_confirm_details.order_num

|| SQLERRM;

fnd_file.put_line (fnd_file.LOG, l_chr_error_msg);

END;

IF l_num_header_id IS NOT NULL

THEN

IF rec_pick_confirm_details.line_number IS NOT NULL

THEN

-- Validation of Order Line Number

BEGIN

SELECT line_number

INTO l_num_line_number

FROM oe_order_lines_all

WHERE header_id = l_num_header_id

AND org_id = NVL (l_num_organization_id, org_id)

AND line_number = rec_pick_confirm_details.line_number;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_chr_error_flag := 'E';

l_chr_error_msg :=

'Validation failed at Order line Number, '

|| 'Order line Number doesnot exist :'

|| rec_pick_confirm_details.operating_unit

|| ' - '

|| rec_pick_confirm_details.order_num

|| ' - '

|| rec_pick_confirm_details.line_number;

fnd_file.put_line (fnd_file.LOG, l_chr_error_msg);

WHEN OTHERS

THEN

l_chr_error_flag := 'E';

l_chr_error_msg :=

'Validation failed at Order line Number:'

|| rec_pick_confirm_details.line_number

|| SQLERRM;

END;

END IF;

-- END IF;

IF l_chr_error_flag = 'E'

THEN

fnd_file.put_line (fnd_file.LOG,

'Paramter Validation failed '

|| l_chr_error_flag

|| l_chr_error_msg

);

UPDATE xxmto.xxmto_oe_pick_confirm_stg

SET process_flag = 'VE',

error_flag = 'Y',

error_msg = l_chr_error_msg,

last_updated_by = l_num_last_updated,

last_update_date = SYSDATE,

last_updated_login = fnd_global.login_id,

request_id = fnd_global.conc_request_id

WHERE ROWID = rec_pick_confirm_details.ROWID;

fnd_file.put_line (fnd_file.LOG, 'Calling xxmto_email_prc');

xxmto_email_prc

(l_chr_errbuf,

l_num_retcode,

'ATTENTION: Action Required for Error in Order Pick Confirm Interface for Ebs Order Number - '

|| rec_pick_confirm_details.order_num,

'Hello,'

|| ' '

|| 'Error occured for Order Number : '

|| rec_pick_confirm_details.order_num

|| ' '

|| 'Error Message :'

|| CHR (9)

|| l_chr_error_msg

|| ' '

|| '**This is system generated mail. Please do not reply to the mail.'

);

fnd_file.put_line (fnd_file.LOG,

'xxmto_email_prc completed'

|| l_num_retcode

|| '-'

|| l_chr_errbuf

);

p_out_chr_errbuf := 'ERROR';

p_out_num_retcode := 2;

END IF;

IF l_chr_error_flag <> 'E'

THEN

l_num_record_count := 0;

FOR rec_sales_order_details IN

cur_sales_order_details (rec_pick_confirm_details.order_num,

rec_pick_confirm_details.line_number,

l_num_organization_id

)

LOOP

l_num_record_count := l_num_record_count + 1;

l_trolin_tbl (1).header_id :=

rec_sales_order_details.mo_header_id;

l_trolin_tbl (1).line_id := rec_sales_order_details.mo_line_id;

l_trolin_tbl (1).created_by := l_num_user_id;

l_trolin_tbl (1).creation_date := SYSDATE;

l_trolin_tbl (1).last_updated_by := l_num_user_id;

l_trolin_tbl (1).last_update_date := SYSDATE;

l_trolin_tbl (1).last_update_login := fnd_global.login_id;

inv_pick_wave_pick_confirm_pub.pick_confirm

(p_api_version_number => 1.0,

p_init_msg_list => fnd_api.g_true,

p_commit => fnd_api.g_true,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_move_order_type => 3,

p_transaction_mode => 2,

p_trolin_tbl => l_trolin_tbl,

p_mold_tbl => l_mold_tbl,

x_mmtt_tbl => l_mold_tbl,

x_trolin_tbl => l_trolin_tbl

);

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

fnd_file.put_line

(fnd_file.LOG,

'Pick Confirm API return status and log:'

|| x_return_status

|| ':'

|| x_msg_data

);

UPDATE xxmto.xxmto_oe_pick_confirm_stg

SET process_flag = 'PE',

error_flag = 'Y',

error_msg = x_msg_data,

last_updated_by = l_num_last_updated,

last_update_date = SYSDATE,

last_updated_login = fnd_global.login_id,

request_id = fnd_global.conc_request_id

WHERE ROWID = rec_pick_confirm_details.ROWID;

fnd_file.put_line (fnd_file.LOG, 'Calling xxmto_email_prc');

xxmto_email_prc

(l_chr_errbuf,

l_num_retcode,

'ATTENTION: Action Required for Error in Order Pick Confirm Interface for Order Number - '

|| rec_sales_order_details.order_number,

'Hello,'

|| ' '

|| 'Error occured for Order Number : '

|| rec_sales_order_details.order_number

|| ' '

|| 'Error Message :'

|| CHR (9)

|| x_msg_data

|| ' '

|| '**This is system generated mail. Please do not reply to the mail.'

);

fnd_file.put_line (fnd_file.LOG,

'xxmto_email_prc completed'

|| l_num_retcode

|| '-'

|| l_chr_errbuf

);

p_out_chr_errbuf := 'ERROR';

p_out_num_retcode := 2;

ELSE

fnd_file.put_line

(fnd_file.LOG,

'Pick Confirm API return status and log:'

|| x_return_status

|| ':'

|| x_msg_data

);

UPDATE xxmto.xxmto_oe_pick_confirm_stg

SET process_flag = 'PS',

error_flag = 'N',

error_msg = NULL,

last_updated_by = l_num_last_updated,

last_update_date = SYSDATE,

last_updated_login = fnd_global.login_id,

request_id = fnd_global.conc_request_id

WHERE ROWID = rec_pick_confirm_details.ROWID;

p_out_chr_errbuf := 'SUCCESS';

p_out_num_retcode := 0;

END IF;

END LOOP;

IF l_num_record_count = 0

THEN

UPDATE xxmto.xxmto_oe_pick_confirm_stg

SET process_flag = 'PE',

error_flag = 'Y',

error_msg =

'Move order details not found for this order number',

last_updated_by = l_num_last_updated,

last_update_date = SYSDATE,

last_updated_login = fnd_global.login_id,

request_id = fnd_global.conc_request_id

WHERE ROWID = rec_pick_confirm_details.ROWID;

fnd_file.put_line

(fnd_file.LOG,

'Move order details not found for this order number'

|| rec_pick_confirm_details.order_num

);

xxmto_email_prc

(l_chr_errbuf,

l_num_retcode,

'ATTENTION: Action Required for Error in Order Pick Confirm Interface for Order Number - '

|| rec_pick_confirm_details.order_num,

'Hello,'

|| ' '

|| 'Move order details not found for this order number'

|| rec_pick_confirm_details.order_num

|| ' '

|| 'Error Message :'

|| 'Move order details not found'

|| ' '

|| '**This is system generated mail. Please do not reply to the mail.'

);

fnd_file.put_line (fnd_file.LOG,

'xxmto_email_prc completed'

|| l_num_retcode

|| '-'

|| l_chr_errbuf

);

p_out_chr_errbuf := 'WARNING';

p_out_num_retcode := 1;

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Unexpected Error in Order Pick Confirm Interface: '

|| SQLERRM

);

l_chr_error_msg := SQLERRM;

UPDATE xxmto.xxmto_oe_pick_confirm_stg

SET process_flag = 'VE',

error_flag = 'Y',

error_msg =

'Unexpected Error in Order Pick Confirm Interface: '

|| l_chr_error_msg,

last_updated_by = l_num_last_updated,

last_update_date = SYSDATE,

last_updated_login = fnd_global.login_id,

request_id = fnd_global.conc_request_id

WHERE order_num = NVL (p_in_num_ordernum, order_num)

AND UPPER (operating_unit) =

UPPER (NVL (p_in_chr_ou, operating_unit))

AND NVL (line_number, 1) = NVL (p_in_num_linenum, 1)

AND process_flag <> 'PS'

AND error_flag = NVL (p_in_chr_errorflag, error_flag);

fnd_file.put_line (fnd_file.LOG, 'Calling xxmto_email_prc');

xxmto_email_prc

(l_chr_errbuf,

l_num_retcode,

'ATTENTION: Action Required for Error in Order Pick Confirm Interface- ',

'Hello,'

|| ' '

|| 'Unexpected Error in Order Pick Confirm Interface: '

|| ' '

|| 'Error Message :'

|| CHR (9)

|| l_chr_error_msg

|| ' '

|| '**This is system generated mail. Please do not reply to the mail.'

);

fnd_file.put_line (fnd_file.LOG,

'xxmto_email_prc completed'

|| l_num_retcode

|| '-'

|| l_chr_errbuf

);

p_out_chr_errbuf := 'ERROR';

p_out_num_retcode := 2;

COMMIT;

END xxmto_oe_pick_confirm_prc;

SHOW ERRORS;

GRANT EXECUTE ON xxmto_oe_pick_confirm_prc TO xxmtowsp;

For Email Program please see below link:

http://masteroracleapps.blogspot.in/2015/04/email-sending-program-by-using-utlsmtp.html

Master OracleApps

Pages

Thursday, April 2, 2015

Pick Confirm using API:

INV_PICK_WAVE_PICK_CONFIRM_PUB.PICK_CONFIRM

For Email Program please see below link:

No comments:

Post a Comment