Master OracleApps: April 2015

Thursday, April 2, 2015

AR Invoice Information:

SELECT rct.trx_number,
rct.trx_date,
rct.invoice_currency_code,
DECODE(avt.tax_code,'GST 0%', 0, 'GST Exempt', 0, rct.exchange_rate) "Exchange Rate",
rct.purchase_order,
rctl.line_type,
rcld.customer_trx_line_id,
rct.internal_notes,
rct.comments,
DECODE(rctl.line_type,'TAX',avt.tax_code,rctl.description) "Description",
rct.internal_notes||' '||rct.comments "Project_Comm",
rctl.unit_selling_price,
TO_NUMBER(DECODE(sign(rcld.amount),'-1',rcld.amount*(-1),rcld.amount )) amount ,
rctt.name "INVOICE_TYPE",
rctt.type,
NVL(rct.term_id,0) term_id,
hp.party_name "customer_name",
hl.address1||hl.address2||hl.address3||hl.address4 "Customer_Address",
hl.city,
hl.state,
ft.nls_territory "Country",
hl.postal_code,
avt.tax_code,
hp.party_id,
rct.customer_trx_id
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rcld,
ra_cust_trx_types_all rctt,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_locations hl,
ar_vat_tax_all_b avt,
fnd_territories ft
WHERE rct.org_id = fnd_global.org_id
AND rct.complete_flag = 'Y'
AND rct.org_id = rctl.org_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rct.org_id = rcld.org_id
AND rctl.customer_trx_id = rcld.customer_trx_id
AND rctl.customer_trx_line_id = rcld.customer_trx_line_id
AND rcld.gl_posted_date IS NOT NULL
AND rct.org_id = rctt.org_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.org_id = rct.org_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND hcsu.status = 'A'
AND rctl.vat_tax_id = avt.vat_tax_id(+)
AND hl.country = ft.territory_code
AND TRUNC (rcld.gl_date) = NVL (TRUNC (TO_DATE (:P_GL_DATE, 'YYYY/MM/DD HH24:MI:SS')),TRUNC (rcld.gl_date))
AND rct.trx_number = NVL(:P_TRX_NUMBER,rct.trx_number)
AND hp.party_name = NVL(:P_CUSTOMER_NAME,hp.party_name)
ORDER BY customer_trx_line_id

ATTACHMENTS AT FORM Level:

SELECT fds.short_text
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_documents_short_text fds
WHERE fad.pk1_value = :customer_trx_id or invoice_id
AND fad.entity_name = 'RA_CUSTOMER_TRX'
AND fad.document_id = fdt.document_id
AND fdt.language = 'US'
AND fdt.media_id = fds.media_id;

DECLARE

l_doc_category_id NUMBER;

l_document_id NUMBER;

l_attached_document_id NUMBER;

l_media_id NUMBER;

l_fnd_user_id NUMBER;

l_short_datatype_id NUMBER;

l_pk2_value VARCHAR2 (50) := '9023';

l_seq_num NUMBER;

BEGIN

-- Select User_id

SELECT user_id

INTO l_fnd_user_id

FROM apps.fnd_user

WHERE user_name = 'A14380';

-- Get Data type id for Short Text types of attachments

SELECT datatype_id

INTO l_short_datatype_id

FROM apps.fnd_document_datatypes

WHERE name = 'SHORT_TEXT';

-- Select Category id for "Vendor/To Supplier" Attachments

SELECT category_id

INTO l_doc_category_id

FROM apps.fnd_document_categories

WHERE name = 'Buyer';

-- Select nexvalues of document id, attached document id and

SELECT apps.fnd_documents_s.NEXTVAL,

apps.fnd_attached_documents_s.NEXTVAL,

apps.fnd_documents_short_text_s.NEXTVAL

INTO l_document_id, l_attached_document_id, l_media_id

FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10

INTO l_seq_num

FROM fnd_attached_documents

WHERE pk2_value = l_pk2_value AND entity_name = 'MTL_SYSTEM_ITEMS';

INSERT INTO apps.fnd_documents (document_id,

creation_date,

created_by,

last_update_date,

last_updated_by,

datatype_id,

category_id,

security_type,

publish_flag,

usage_type,

media_id)

VALUES (l_document_id,

SYSDATE,

l_fnd_user_id,

SYSDATE,

l_fnd_user_id,

l_short_datatype_id, -- Datatype for 'SHORT_TEXT'

l_doc_category_id, -- Category_id

2, -- 'Organization' Level Security

'Y', -- Publish_flag

'O' , -- Usage_type of 'One Time'

l_media_id);

INSERT INTO apps.fnd_documents_tl (document_id,

creation_date,

created_by,

last_update_date,

last_updated_by,

language,

source_lang,

description,

media_id)

VALUES (l_document_id,

SYSDATE,

l_fnd_user_id,

SYSDATE,

l_fnd_user_id,

'US', -- language

'DESCRIPTION', -- description

l_media_id -- media_id

);

INSERT INTO apps.fnd_attached_documents (attached_document_id,

document_id,

creation_date,

created_by,

last_update_date,

last_updated_by,

seq_num,

entity_name,

pk1_value,

pk2_value,

automatically_added_flag)

VALUES (l_attached_document_id,

l_document_id,

SYSDATE,

l_fnd_user_id,

SYSDATE,

l_fnd_user_id,

l_seq_num, -- Sequence Number of attachment.

'MTL_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment

576, -- Organization id for Inventory Item Master Org

l_pk2_value, -- Inventory Item Id

'N' -- Automatically_added_flag

);

INSERT INTO apps.fnd_documents_short_text (media_id, short_text)

VALUES (l_media_id, 'Sending details to buyer' );

COMMIT;

END;

UPDATE Person Details Through API:

/********************************UPDATE PERSON*****************************************/

DECLARE

l_person_rec_type HZ_PARTY_V2PUB.PERSON_REC_TYPE;

l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;

l_party_obj_version NUMBER := 1;

x_profile_id NUMBER;

x_return_status VARCHAR2(1);

x_msg_count NUMBER;

x_msg_data VARCHAR2(4000);

BEGIN

l_party_rec.party_id := 149707;

l_party_rec.status := 'I';

l_person_rec_type.person_first_name := 'TEST';

l_person_rec_type.person_last_name := 'PERSON';

l_person_rec_type.party_rec := l_party_rec;

HZ_PARTY_V2PUB.UPDATE_PERSON(p_init_msg_list => FND_API.G_TRUE,

p_person_rec => l_person_rec_type,

p_party_object_version_number => l_party_obj_version,

x_profile_id => x_profile_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

DBMS_OUTPUT.PUT_LINE('API Status: '||x_return_status);

IF (x_return_status <> 'S')

THEN

DBMS_OUTPUT.PUT_LINE('ERROR :'|| x_msg_data );

END IF;

DBMS_OUTPUT.PUT_LINE('update_person is completed');

--COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);

ROLLBACK;

END;

/*********************UPDATE ALL PERSONS***************************************/

CREATE OR REPLACE PROCEDURE xxlr_update_per

l_person_rec_type HZ_PARTY_V2PUB.PERSON_REC_TYPE;

l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;

l_party_obj_version NUMBER := 1;

x_profile_id NUMBER;

x_return_status VARCHAR2(1);

x_msg_count NUMBER;

x_msg_data VARCHAR2(4000);

l_success_count NUMBER := 0;

l_failure_count NUMBER := 0;

l_all_count NUMBER := 0;

BEGIN

FOR i IN (SELECT party_id

FROM hz_parties

WHERE 1=1

AND party_type = 'PERSON'

AND created_by_module = 'TCA_V2_API'

AND status = 'A'

AND PARTY_NAME <> 'TEST PERSON'

)

LOOP

l_all_count := l_all_count + 1;

--DBMS_OUTPUT.PUT_LINE('Party_id='||i.party_id);

l_party_rec.party_id := i.party_id;

l_party_rec.status := 'I';

l_person_rec_type.person_first_name := 'TEST';

l_person_rec_type.person_last_name := 'PERSON';

l_person_rec_type.party_rec := l_party_rec;

SELECT NVL(MAX(object_version_number),1)

INTO l_party_obj_version

FROM hz_organization_profiles

WHERE party_id = i.party_id

AND effective_end_date IS NULL;

HZ_PARTY_V2PUB.UPDATE_PERSON(p_init_msg_list => FND_API.G_TRUE,

p_person_rec => l_person_rec_type,

p_party_object_version_number => l_party_obj_version,

x_profile_id => x_profile_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

--DBMS_OUTPUT.PUT_LINE(x_return_status);

IF (x_return_status <> 'S')

THEN

l_failure_count := l_failure_count + 1;

fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,

p_count => x_msg_count,

p_data => x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.PUT_LINE('ERROR Party_id = '||i.party_id ||';'|| x_msg_data );

ELSE

FOR j IN 1 .. x_msg_count

LOOP

x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);

DBMS_OUTPUT.PUT_LINE('ERROR Party_id = '||i.party_id ||';'|| x_msg_data );

END LOOP;

END IF;

ELSE

l_success_count := l_success_count + 1;

IF MOD(l_success_count,100)=0

THEN

COMMIT;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');

DBMS_OUTPUT.PUT_LINE(' Total Record count : '||L_ALL_COUNT);

DBMS_OUTPUT.PUT_LINE(' Total Success Record Count : '||L_SUCCESS_COUNT);

DBMS_OUTPUT.PUT_LINE(' Total Fail Record Count :'||L_FAILURE_COUNT);

DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);

ROLLBACK;

END xxlr_update_per;

UPDATE Organization Details Through API:

/********************************UPDATE ORGANIZATION*****************************************/

DECLARE
l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
l_party_obj_version NUMBER := 1;
x_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
BEGIN

l_party_rec.party_id := 5297;
l_party_rec.status := 'I';
l_organization_rec.organization_name := 'TEST_ORG';
l_organization_rec.party_rec := l_party_rec;
HZ_PARTY_V2PUB.update_organization(p_init_msg_list => FND_API.G_TRUE,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_party_obj_version,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);

DBMS_OUTPUT.PUT_LINE('API Status: '||x_return_status);

IF (x_return_status <> 'S')
THEN
DBMS_OUTPUT.PUT_LINE('ERROR :'|| x_msg_data );
END IF;

DBMS_OUTPUT.PUT_LINE('update_organization is completed');

--COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);
ROLLBACK;
END;

/*********************UPDATE ALL ORGANIZATIONS***************************************/

===================================================================================================================
CREATE OR REPLACE PROCEDURE APPS.xxlr_update_org
IS
l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
l_party_obj_version NUMBER := 1;
x_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
l_success_count NUMBER := 0;
l_failure_count NUMBER := 0;
l_all_count NUMBER := 0;

BEGIN

FOR i IN (SELECT party_id
FROM hz_parties
WHERE party_type = 'ORGANIZATION'
AND party_name <> 'TEST_ORG'
AND status ='A'
)
LOOP

l_all_count := l_all_count + 1;

--DBMS_OUTPUT.PUT_LINE('Party_id='||i.party_id);

l_party_rec.party_id := i.party_id;
l_party_rec.status := 'I';
l_organization_rec.organization_name := 'TEST_ORG';
l_organization_rec.party_rec := l_party_rec;

SELECT max(object_version_number)
INTO l_party_obj_version
FROM hz_parties
WHERE party_id = i.party_id
AND status ='A';

HZ_PARTY_V2PUB.update_organization(p_init_msg_list => FND_API.G_TRUE,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_party_obj_version,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);

--DBMS_OUTPUT.PUT_LINE(x_return_status);

IF (x_return_status <> 'S')
THEN

DBMS_OUTPUT.PUT_LINE('Object Version: '||l_party_obj_version );

l_failure_count := l_failure_count + 1;
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => x_msg_count,
p_data => x_msg_data);

IF x_msg_count = 1
THEN

DBMS_OUTPUT.PUT_LINE('ERROR Party_id = '||i.party_id ||';'|| x_msg_data );

ELSE
FOR j IN 1 .. x_msg_count
LOOP

x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
DBMS_OUTPUT.PUT_LINE('ERROR Party_id = '||i.party_id ||';'|| x_msg_data );

END LOOP;
END IF;
ELSE
l_success_count := l_success_count + 1;

IF MOD(l_success_count,100)=0
THEN
COMMIT;
END IF;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');
DBMS_OUTPUT.PUT_LINE(' Total Record count : '||L_ALL_COUNT);
DBMS_OUTPUT.PUT_LINE(' Total Success Record Count : '||L_SUCCESS_COUNT);
DBMS_OUTPUT.PUT_LINE(' Total Fail Record Count :'||L_FAILURE_COUNT);
DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);
ROLLBACK;
END xxlr_update_org;
/

UPDATE Customer Account Details Through API:

DECLARE
l_cust_account_rec_type HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_object_version_number NUMBER := 1;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
BEGIN

l_cust_account_rec_type.cust_account_id := 4153;
l_cust_account_rec_type.account_name := fnd_api.g_miss_char;
l_cust_account_rec_type.status := 'I';
HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT(p_init_msg_list => FND_API.G_TRUE,
p_cust_account_rec => l_cust_account_rec_type,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);

DBMS_OUTPUT.PUT_LINE('API Status: '||x_return_status);

IF (x_return_status <> 'S')
THEN
DBMS_OUTPUT.PUT_LINE('ERROR :'|| x_msg_data );
END IF;

DBMS_OUTPUT.PUT_LINE('update_person is completed');

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);
ROLLBACK;
END;

/*********************UPDATE ALL CUST ACCOUNT***************************************/

CREATE OR REPLACE PROCEDURE xxlr_update_cust_acct
IS
l_cust_account_rec_type HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_object_version_number NUMBER := 1;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
l_success_count NUMBER := 0;
l_failure_count NUMBER := 0;
l_all_count NUMBER := 0;

BEGIN

FOR i IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE 1=1
AND status ='A'
)
LOOP

l_all_count := l_all_count + 1;

--DBMS_OUTPUT.PUT_LINE('cust_account_id='||i.cust_account_id);

l_cust_account_rec_type.cust_account_id := i.cust_account_id;
l_cust_account_rec_type.account_name := fnd_api.g_miss_char;
l_cust_account_rec_type.status := 'I';

SELECT max(object_version_number)
INTO l_object_version_number
FROM hz_cust_accounts
WHERE cust_account_id = i.cust_account_id;

HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT(p_init_msg_list => FND_API.G_TRUE,
p_cust_account_rec => l_cust_account_rec_type,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);

--DBMS_OUTPUT.PUT_LINE(x_return_status);

IF (x_return_status <> 'S')
THEN

l_failure_count := l_failure_count + 1;
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => x_msg_count,
p_data => x_msg_data);

IF x_msg_count = 1
THEN

DBMS_OUTPUT.PUT_LINE('ERROR cust_account_id = '||i.cust_account_id ||';'|| x_msg_data );

ELSE
FOR j IN 1 .. x_msg_count
LOOP

x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
DBMS_OUTPUT.PUT_LINE('ERROR cust_account_id = '||i.cust_account_id ||';'|| x_msg_data );

END LOOP;
END IF;
ELSE

l_success_count := l_success_count + 1;

IF MOD(l_success_count,100)=0
THEN
COMMIT;
END IF ;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');
DBMS_OUTPUT.PUT_LINE(' Total Record count : '||L_ALL_COUNT);
DBMS_OUTPUT.PUT_LINE(' Total Success Record Count : '||L_SUCCESS_COUNT);
DBMS_OUTPUT.PUT_LINE(' Total Fail Record Count :'||L_FAILURE_COUNT);
DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);
ROLLBACK;

END xxlr_update_cust_acct;

KILL THE SESSIONS:

SELECT * FROM v$access
WHERE UPPER(object) like 'TABLE_NAME'

select sid, serial#, command, taddr, 'alter system kill session '||''''||sid||', '||serial#||''''
from v$session where sid in (5)

alter system kill session '5, 25869'

Organization and Classifications Through API:

declare
l_validate_mode BOOLEAN := TRUE;
l_begin_date DATE := SYSDATE;
l_end_date DATE := hr_general.end_of_time;
l_business_group_id NUMBER := 81;
l_orgname hr_all_organization_units.name%TYPE := 'MTODEV TEST1';
l_intl_extl fnd_lookup_values.meaning%TYPE :='INT';
l_organization_id hr_all_organization_units.organization_id%TYPE;
l_object_version_number hr_all_organization_units.object_version_number%TYPE;
l_duplicate_org_warning BOOLEAN;
begin
hr_organization_api.create_organization
(
p_effective_date => l_begin_date --Reference date for validating lookup values
,p_business_group_id => l_business_group_id
,p_date_from => l_begin_date --Date the organization takes effect
,p_name => l_orgname
,p_location_id => 142
,p_date_to => l_end_date
,p_internal_external_flag => l_intl_extl--'INT' --Internal/External org flag
,p_internal_address_line => null
,p_type => null --Org type -- Lookup Type ORG_TYPE
,p_comments => null
,p_attribute_category => null
,p_attribute1 => null
,p_attribute2 => null
,p_attribute3 => null
,p_attribute4 => null
,p_attribute5 => null
--Out Variables
,p_organization_id => l_organization_id
,p_object_version_number => l_object_version_number
,p_duplicate_org_warning => l_duplicate_org_warning
);

if l_organization_id is null or l_object_version_number is null then
dbms_output.put_line('hr_organization_api.update_organization API Error: '||sqlerrm);
rollback;
elsif l_duplicate_org_warning then
dbms_output.put_line('Warning: Duplicate Organization');
rollback;
else
commit;
end if;
--
exception
when others then
dbms_output.put_line('hr_organization_api.create_organization API failed with error :'||sqlerrm);
rollback;
end;

========================================================================================================

DECLARE
l_validate_mode BOOLEAN := TRUE;
l_begin_date DATE := SYSDATE;
-- Output Variables
V_ORG_INFORMATION_ID NUMBER;
V_OBJECT_VERSION_NUMBER NUMBER;
BEGIN
-- Calling API HR_ORGANIZATION_API.CREATE_ORG_CLASSIFICATION
hr_organization_api.create_org_classification(P_EFFECTIVE_DATE => l_begin_date
,P_ORGANIZATION_ID => 169 --173
,P_ORG_CLASSIF_CODE => 'HR_ORG' --'INV'
,P_ORG_INFORMATION_ID => V_ORG_INFORMATION_ID
,P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER
);

dbms_output.put_line(V_ORG_INFORMATION_ID);
COMMIT;
exception when others then
dbms_output.put_line('error : ' || sqlerrm);
END;

P_ORG_CLASSIF_CODE we can get values from

HR_LOOKUPS WHERE LOOKUP_TYPE ='ORG_CLASS'

TCA Queries

The SQL queries involving following TCA tables are explained in the next section.

1. HZ_PARTIES

2. HZ_ORGANIZATION_PROFILES

3. HZ_PERSON_PROFILES

4. HZ_CUSTOMER_PROFILES

5. HZ_LOCATIONS

6. HZ_PARTY_SITES

7. HZ_PARTY_SITE_USES

8. HZ_CUST_ACCOUNTS

9. HZ_CUST_ACCT_SITES

10. HZ_CUST_SITE_USES

11. HZ_CONTACT_POINTS

12. HZ_ORG_CONTACTS

13. HZ_ORG_CONTACT_ROLES

14. HZ_RELATIONSHIPS

15. HZ_CLASS_CATEGORIES

16. HZ_CLASS_CODE_DENORM

17. HZ_CODE_ASSIGNMENTS

18. HZ_CLASS_CATEGORY_USES

19. HZ_RELATIONSHIP_TYPES

Query to fetch Organization party details.

When a new Organization is created, a record is created in the HZ_PARTIES table with PARTY_TYPE = ORGANIZATION. The HZ_PARTIES table holds the basic information about the party like party name, party number, party type etc.
Query based on party id. Party id is primary key in HZ_PARTIES.

SELECT *

FROM hz_parties

WHERE party_id = <enter party id here>

AND party_type = 'ORGANIZATION'

Query based on party number. Party number is stored in a VARCHAR type column. So, it should always be included in single quotes. Party number is unique for all the parties and will return a single row for one party number.

SELECT *

FROM hz_parties

WHERE party_number = '<enter party number here>'

AND party_type = 'ORGANIZATION'

Query based on party name. Multiple parties can have same name. Below query may return multiple rows depending on the party name.

SELECT *

FROM hz_parties

WHERE party_name = '<enter party name here>'

AND party_type = 'ORGANIZATION'

To find active parties, add join

AND status = 'A'

To find inactive parties, add join

AND status = 'I'

When a new Organization is created, a record is also created in HZ_ORGANIZATION_PROFILES table. The table holds more detailed and specific information about the organization like organization‟s finance history, bank, employees, etc. The primary key is ORGANIZATION_PROFILE_ID

SELECT *

FROM hz_organization_profiles

WHERE party_id = <enter party id here>

When a new Person is created, a record is created in the HZ_PARTIES table with PARTY_TYPE = PERSON. The HZ_PARTIES table holds the basic information about the party like party name, party number, party type etc.

SELECT *

FROM hz_parties

WHERE party_name = '<enter party name here>'

AND party_type = 'PERSON'

HZ_PARTY_SITES table relates an existing party from the HZ_PARTIES table with an address location from the HZ_LOCATIONS table. The table stores location-specific party information such as MAILSTOP and ADDRESSEE.

SELECT hps.*

FROM hz_parties hp ,

hz_party_sites hps

WHERE hp.party_id = hps.party_id

AND hp.party_id = <enter party id here>

Query to find addresses for a party.

SELECT hl.*

FROM hz_parties hp ,

hz_party_sites hps ,

hz_locations hl

WHERE hp.party_id = hps.party_id

AND hp.party_id = <enter party id here>

AND hl.location_id = hps.location_id

Query to find accounts for a party.

SELECT * FROM hz_cust_accounts WHERE party_id = <enter party id here>

CUST_ACCOUNT_ID is primary key of the table.

Query to find customer account sites for a party.

CUST_ACCT_SITE_ID is the primary key of the table.

SELECT hcas.*

FROM hz_cust_accounts hca ,

hz_cust_acct_sites_all hcas ,

hz_parties hp

WHERE hp.party_id = <enter party id here>

AND hca.cust_account_id = hcas.cust_account_id

AND hp.party_id = hca.party_id

SELECT hcas.*

FROM hz_cust_acct_sites_all hcas ,

hz_parties hp ,

hz_party_sites hps

WHERE hp.party_id = hps.party_id

AND hp.party_id = <enter party id here>

AND hcas.party_site_id = hps.party_site_id

Query to find business purposes (ship to, bill to etc) of account sites.

SELECT hcsu.*

FROM hz_cust_accounts hca ,

hz_cust_acct_sites_all hcas ,

hz_cust_site_uses_all hcsu ,

hz_parties hp

WHERE hp.party_id = <enter party id here>

AND hp.party_id = hca.party_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

AND hcsu.site_use_code = '<enter site use here>'

Site use code can be SHIP_TO, BILL_TO, SOLD_TO etc.

SITE_USE_ID is primary key of the table.

Query to find contact points for a party/party site.

SELECT *

FROM hz_contact_points

WHERE owner_table_name = 'HZ_PARTIES'

AND owner_table_id = <enter party id here>

Query to find relationship between two parties.

SELECT *

FROM hz_relationships

WHERE subject_table_name = 'HZ_PARTIES'

AND object_table_name = 'HZ_PARTIES'

AND relationship_type = <enter relationship type here>

Query to find Organization contacts.

SELECT hoc.*

FROM hz_org_contacts hoc ,

hz_relationships hr

WHERE hoc.party_relationship_id = hr.relationship_id

AND hr.subject_id = <enter party id of party A>

AND hr.object_id = <enter party id of party B>

FND_PROFILE AND FND_GLOBAL:

Following are the FND_PROFILE values that can be used in the PL/SQL code:

fnd_profile.value('PROFILEOPTION');

fnd_profile.value('MFG_ORGANIZATION_ID');

fnd_profile.value('ORG_ID');

fnd_profile.value('LOGIN_ID');

fnd_profile.value('USER_ID');

fnd_profile.value('USERNAME');

fnd_profile.value('CONCURRENT_REQUEST_ID');

fnd_profile.value('GL_SET_OF_BKS_ID');

fnd_profile.value('SO_ORGANIZATION_ID');

fnd_profile.value('APPL_SHRT_NAME');

fnd_profile.value('RESP_NAME');

fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

FND_GLOBAL.USER_ID;

FND_GLOBAL.APPS_INTIALIZE;

FND_GLOBAL.LOGIN_ID;

FND_GLOBAL.CONC_LOGIN_ID;

FND_GLOBAL.PROG_APPL_ID;

FND_GLOBAL.CONC_PROGRAM_ID;

FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

g_user_id PLS_INTEGER := fnd_global.user_id;

g_login_id PLS_INTEGER := fnd_global.login_id;

g_conc_req_id PLS_INTEGER := fnd_global.conc_request_id;

g_org_id PLS_INTEGER := fnd_profile.value('ORG_ID');

g_sob_id PLS_INTEGER := fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

v_resp_appl_id := fnd_global.resp_appl_id;

v_resp_id := fnd_global.resp_id;

v_user_id := fnd_global.user_id;

FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

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

AR Invoice Information:

ATTACHMENTS AT FORM Level:

DB LINK Creation

UPDATE Person Details Through API:

KILL THE SESSIONS:

Organization and Classifications Through API:

TCA Queries

The SQL queries involving following TCA tables are explained in the next section.

Query to fetch Organization party details.

FND_PROFILE AND FND_GLOBAL:

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

And initialize the application environment as follows:

Pick Confirm using API:

INV_PICK_WAVE_PICK_CONFIRM_PUB.PICK_CONFIRM

For Email Program please see below link: