Oracle EBS Practical Approaches: Pl/SQL

Showing posts with label Pl/SQL. Show all posts

Wednesday, June 03, 2020

Delete Attachment in EBS

Hi,

If you are looking for a code that can favor you to Delete multiple/Single attachments, here is an oracle provided API that do fortunes with Attachments.

API Name:
fnd_attached_documents2_pkg

If you want to delete all the attachments from specific transaction for example, Attachments from Sales order Headers or say GL Journal Header , do Use

Procedure "DELETE_ATTACHMENTS"

This procedure attracts 3 mandatory parameters and those are

ENTITY_NAME-->TABLE_NAME
PK1_VALUE --> Transaction_Id (can be OrdeR_Header_id, Journal_Header_id)
Delete_document_Flag --> 'Y'

This will delete all the available attachments from the Transaction.

2. If you want to delete Single attachments from specific transaction for example, Sequence No 10 from Attachments from Sales order Headers or say GL Journal Header , do Use

Procedure "DELETE_ATTACHMENT"

This procedure attracts 2 mandatory parameters and those are

attachment_document_id--> fnd_attached_docs_form_vl%attachment_document_id
Delete_document_Flag --> 'Y'

This will delete Specific attachment (corresponding to the specific attachment_document_id) from fnd_attached_docs_form_vl

Tuesday, April 23, 2019

UTL_SMTP Package to send Mails from PL/SQL

CREATE OR REPLACE procedure proc_utl_smtp_mail (p_from IN varchar2,
p_to IN varchar2,
p_subject IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_mailhost VARCHAR2)
IS
l_message Long;
l_return_status VARCHAR2(10);
l_return_msg VARCHAR2(1000);
l_reply UTL_SMTP.reply;
l_smtp_port NUMBER := 25;
l_mail_con UTL_SMTP.CONNECTION;
l_crlf VARCHAR2(100):= chr(13) || chr(10);
l_cnt NUMBER;
BEGIN
l_message := 'Testing Procedure for UTL_SMTP Mail Package.';
l_mail_con := SYS.utl_smtp.open_connection (p_mailhost,l_smtp_port);
UTL_SMTP.helo (l_mail_con,p_mailhost);
utl_smtp.mail (l_mail_con,p_from);
utl_smtp.rcpt (l_mail_con,p_to);
utl_smtp.rcpt (l_mail_con,p_cc);
utl_smtp.open_data (l_mail_con);
utl_smtp.write_data (l_mail_con, 'TO:' ||p_from || l_CRLF);
utl_smtp.write_data (l_mail_con, 'SUBJECT:' ||p_subject || l_CRLF);
utl_smtp.write_data (l_mail_con , 'X-Priority :' || '1' || l_crlf); --- To Send Mails as High Priority

utl_smtp.write_data (l_mail_con, l_crlf
|| 'Mr. XXXX'
|| l_crlf
|| l_crlf
|| 'Subject: '
|| p_subject
|| l_crlf
|| l_crlf
|| 'Message : '
|| l_message
|| l_crlf
|| l_crlf
|| ' Do Not reply this message.'
|| l_crlf
|| 'Thanks & Rgards'
|| l_crlf
|| 'Oracle Team'
);
utl_smtp.close_data (l_mail_con);
l_reply := UTL_SMTP.QUIT (l_mail_con);

dbms_output.put_line ('l_reply :' || l_reply.text);
END;
/

Thursday, December 14, 2017

Create ASN in 'EXPECTED' or 'FULLY_RECEIVED' Status.

Create ASN in 'EXPECTED' or 'FULLY_RECEIVED' Status.

We can create an ASN in Oracle apps in either 'EXPECTED' or 'FULLY RECEIVED' Status.

This can be achieved or bifurcated using 'AUTO_TRANSACT_CODE' Column.

A)- In case of ASN to be created in 'EXPECTED' Status then

Insert in RHI and RTI with below values.

AUTO_TRANSACT_CODE = 'SHIP'

INTO rcv_headers_interface (header_interface_id,
GROUP_ID,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
shipped_date,
vendor_id,
employee_id,
validation_flag,
ship_to_organization_code,
expected_receipt_date,
shipment_num,
asn_type,
auto_transact_code)
VALUES (v_header_interface_id,
v_group_id,
'PENDING',
'VENDOR',
'NEW',
gd_date,
gn_user_id,
gd_date,
gn_user_id,
gd_date,
v_vendor_id,
v_agent_id,
'Y',
v_organization_code,
gd_date,
'REFERENCE_VALUE'--- user specified value
'ASN',
'SHIP');

--------------------------------------------------------------------------------

INSERT
INTO rcv_transactions_interface (
interface_transaction_id,
header_interface_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
auto_transact_code,
receipt_source_code,
source_document_code,
po_header_id,
po_line_id,
po_line_location_id,
ship_to_location_id,
validation_flag,
to_organization_code,
item_num)
VALUES (v_interface_transaction_id,
v_header_interface_id,
v_group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
'SHIP',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
a_detail.shipped_quantity,
v_unit_of_measure,
'SHIP',
'VENDOR',
'PO',
a_detail.PO_HEADER_ID,
a_detail.PO_LINE_ID,
v_line_location_id,
v_ship_to_location_id,
'Y',
v_org_code,
v_item_num);
----------------------------------------------------------------------------------

INSERT
INTO mtl_transaction_lots_interface (
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
lot_number,
lot_expiration_date,
transaction_quantity,
primary_quantity,
product_code,
product_transaction_id)
VALUES (mtl_material_transactions_s.NEXTVAL,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
v_lot_number,
v_exp_date,
a_detail.shipped_quantity,
v_primary_quantity,
'RCV',
v_interface_transaction_id);

----- *******************************************************************------

B)- In case of ASN to be created in 'FULLY_RECEIVED' Status then

Insert in RHI and RTI with below values.

AUTO_TRANSACT_CODE = 'RECEIVE'

INTO rcv_headers_interface (header_interface_id,
GROUP_ID,
  processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
shipped_date,
vendor_id,
employee_id,
validation_flag,
ship_to_organization_code,
expected_receipt_date,
shipment_num,
  asn_type,
auto_transact_code)
VALUES (v_header_interface_id,
v_group_id,
    'PENDING',
'VENDOR',
'NEW',
gd_date,
gn_user_id,
gd_date,
gn_user_id,
gd_date,
v_vendor_id,
v_agent_id,
'Y',
v_organization_code,
gd_date,
'REFERENCE_VALUE'--- user specified value
'ASN',
'RECEIVE');

--------------------------------------------------------------------------------

INSERT
INTO rcv_transactions_interface (
interface_transaction_id,
header_interface_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
auto_transact_code,
receipt_source_code,
source_document_code,
po_header_id,
po_line_id,
po_line_location_id,
ship_to_location_id,
validation_flag,
to_organization_code,
item_num)
VALUES (v_interface_transaction_id,
v_header_interface_id,
v_group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
a_detail.shipped_quantity,
v_unit_of_measure,
   'RECEIVE',
'VENDOR',
'PO',
a_detail.PO_HEADER_ID,
a_detail.PO_LINE_ID,
v_line_location_id,
v_ship_to_location_id,
'Y',
v_org_code,
v_item_num);
----------------------------------------------------------------------------------

INSERT
INTO mtl_transaction_lots_interface (
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
lot_number,
lot_expiration_date,
transaction_quantity,
primary_quantity,
product_code,
product_transaction_id)
VALUES (mtl_material_transactions_s.NEXTVAL,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
v_lot_number,
v_exp_date,
a_detail.shipped_quantity,
v_primary_quantity,
'RCV',
v_interface_transaction_id);

C)- Then Call receiving Transaction processor

fnd_request.submit_request (application => 'PO',
program => 'RVCTP',
start_time => SYSDATE,
description => NULL,
sub_request => FALSE,
argument1 => 'BATCH',
argument2 => v_group_id);

Friday, September 01, 2017

XMLP Report Generated through PL SQL

Generate a XML Report from PL SQL Package.

CREATE OR REPLACE PACKAGE BODY XXORT_TEST_USER

PROCEDURE XXORT_USER (ERRBUF VARCHAR2,

RETCODE NUMBER,

XORDER_NUM IN VARCHAR2)

CURSOR C_HEAD IS

SELECT ORDER_NUMBER,

HEADER_ID,

FLOW_STATUS_CODE

FROM OE_ORDER_HEADERS_ALL

WHERE ORDER_NUMBER=XORDER_NUM;

CURSOR C_LINES (P_HEADER_ID IN VARCHAR2)

SELECT ORDERED_ITEM, ORDERED_QUANTITY , LINE_ID

FROM OE_ORDER_LINES_ALL

WHERE HEADER_ID= P_HEADER_ID;

LV_LOT_NUMBER VARCHAR2(100);

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<?xml version="1.0" encoding="UTF-8"?>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDER_INFO>');

FOR RC_HEAD IN C_HEAD

LOOP

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<P_ORDER_HEADER>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDER_NUMBER>' || RC_HEAD.ORDER_NUMBER || '</ORDER_NUMBER>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<HEADER_ID>' || RC_HEAD.HEADER_ID || '</HEADER_ID>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<FLOW_STATUS_CODE>' || RC_HEAD.FLOW_STATUS_CODE || '</FLOW_STATUS_CODE>');

FOR RC_LINES IN C_LINES (RC_HEAD.HEADER_ID)

LOOP

BEGIN

SELECT LOT_NUMBER

INTO LV_LOT_NUMBER

FROM WSH_DELIVERY_DETAILS

WHERE SOURCE_LINE_ID = RC_LINES.LINE_ID;

EXCEPTION

WHEN OTHERS

THEN NULL;

END ;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<P_ORDER_LINES>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDERED_ITEM>' || RC_LINES.ORDERED_ITEM || '</ORDERED_ITEM>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDERED_QUANTITY>' || RC_LINES.ORDERED_QUANTITY || '</ORDERED_QUANTITY>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<LOT_NUMBER>' || LV_LOT_NUMBER || '</LOT_NUMBER>');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</P_ORDER_LINES>');

END LOOP;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</P_ORDER_HEADER>');

END LOOP;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</ORDER_INFO>');

END ;

END XXORT_TEST_USER;

How to open wrapped Package Source Code in Oracle Apps

This link help you to see the base code in wrapped Package

http://www.codecrete.net/UnwrapIt/