Help--Diagnostics--Examine gives function not available error
- Log in as System Administrator
- Choose Profile/System
- Find profile "Utilities:Diagnostics" and change it's value to "Yes".
How to Password the Examine Function [ID 1041652.6]
How to Make Help Diagnostics Examine be Password Protected OR Not? [ID 160151.1]
Creating a blog is not just a hobby it’s a partnership to grow together.
Wednesday, June 18, 2014
Sunday, June 15, 2014
cst_match_date_period Period Close Error
Symptoms
When attempting to cost Sales Order Pick and Internal Order Pick the following error occurs:
ERROR
Error Code =CST_MATCH_DATE_PERIOD
Error Explanation = The transaction date is not within the accounting period specified.
The issue can be reproduced at will with the following steps:
1. Oracle cost Management
2. Inventory
3. Transactions
4. View Transactions
5. select costed_flag option error
Cause
The cause of the issue is invalid / incorrect data in MTL_MATERIAL_TRANSACTIONS, the acct_period_id is getting stamped with the accounting is of the previous period.
Solution
To identify the issue, please obtain the following scripts output:
1)
SELECT mmt.transaction_id txn_id,
mmt.transaction_date txn_date,
mmt.acct_period_id txn_period_id,
o.acct_period_id org_period_id,
o.period_start_date st_date,
o.schedule_close_date cl_date
FROM mtl_material_transactions mmt,
org_acct_periods o
WHERE mmt.organization_id = o.organization_id
AND TRUNC(o.schedule_close_date) >= TRUNC(mmt.transaction_date)
AND TRUNC(o.period_start_date) <= TRUNC(mmt.transaction_date)
and mmt.transaction_date is not NULL
and mmt.acct_period_id <> o.acct_period_id ;
2) select *
from org_acct_periods
where organization_id = '&org_id_where_issue_exists';
-- To implement the solution, please execute the following steps:
1) Make a backup of MTL_MATERIAL_TRANSACTIONS before running the update script.
2) Shut down the cost manager before the update.
3) Run the following update to correct the acct_period_id:
Update mtl_material_transactions
set acct_period_id =
, costed_flag = 'N'
, transaction_group_id = NULL
, transaction_set_id = NULL
, error_code = NULL
, error_explanation = NULL
where costed_flag = 'E'
and acct_period_id =
and error_code = 'CST_MATCH_DATE_PERIOD'
and organization_id = ''
Note: The transaction_id could also be used as the basis for the update, but in this case the number of records made that difficult and uniqueness could be achieved with the four where clauses parameters above.
3) Restart the Cost Manager.
When attempting to cost Sales Order Pick and Internal Order Pick the following error occurs:
ERROR
Error Code =CST_MATCH_DATE_PERIOD
Error Explanation = The transaction date is not within the accounting period specified.
The issue can be reproduced at will with the following steps:
1. Oracle cost Management
2. Inventory
3. Transactions
4. View Transactions
5. select costed_flag option error
Cause
The cause of the issue is invalid / incorrect data in MTL_MATERIAL_TRANSACTIONS, the acct_period_id is getting stamped with the accounting is of the previous period.
Solution
To identify the issue, please obtain the following scripts output:
1)
SELECT mmt.transaction_id txn_id,
mmt.transaction_date txn_date,
mmt.acct_period_id txn_period_id,
o.acct_period_id org_period_id,
o.period_start_date st_date,
o.schedule_close_date cl_date
FROM mtl_material_transactions mmt,
org_acct_periods o
WHERE mmt.organization_id = o.organization_id
AND TRUNC(o.schedule_close_date) >= TRUNC(mmt.transaction_date)
AND TRUNC(o.period_start_date) <= TRUNC(mmt.transaction_date)
and mmt.transaction_date is not NULL
and mmt.acct_period_id <> o.acct_period_id ;
2) select *
from org_acct_periods
where organization_id = '&org_id_where_issue_exists';
-- To implement the solution, please execute the following steps:
1) Make a backup of MTL_MATERIAL_TRANSACTIONS before running the update script.
2) Shut down the cost manager before the update.
3) Run the following update to correct the acct_period_id:
Update mtl_material_transactions
set acct_period_id =
, costed_flag = 'N'
, transaction_group_id = NULL
, transaction_set_id = NULL
, error_code = NULL
, error_explanation = NULL
where costed_flag = 'E'
and acct_period_id =
and error_code = 'CST_MATCH_DATE_PERIOD'
and organization_id = '
Note: The transaction_id could also be used as the basis for the update, but in this case the number of records made that difficult and uniqueness could be achieved with the four where clauses parameters above.
3) Restart the Cost Manager.
Tuesday, May 27, 2014
Customer Profile Credit Amount Update API
/* Formatted on 25-May-14 5:56:03 PM (QP5 v5.114.809.3010) */
DECLARE
p_customer_profile_rec_type HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_cust_account_profile_id NUMBER;
p_object_version_number NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_user_id number;
l_resp_id number;
l_respid_id number;
in_out_version_no number;
in_out_version_no_hcpa number;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
v_cust_act_prof_amt_id number;
v_cust_account_profile_id NUMBER;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
p_create_profile_amt VARCHAR2 (2000);
v_msg_dummy VARCHAR2 (5000);
t_output VARCHAR2 (5000);
mTrx number;
mOverall number;
BEGIN
FOR i
IN (SELECT hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hca.cust_account_id,
hcp.site_use_id,
account_number,
hcas.ORG_ID org_id,
CUST_ACCT_PROFILE_AMT_ID
FROM hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcua,
Hz_Customer_Profiles hcp,
HZ_CUST_PROFILE_AMTS hcpa
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hcua.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
AND hcp.cust_account_id = hca.cust_account_id
AND hcp.site_use_id = hcua.site_use_id
AND HCP.CUST_ACCOUNT_PROFILE_ID =
HCPA.CUST_ACCOUNT_PROFILE_ID
AND account_number IN
(SELECT DISTINCT CUSTOMER_NUMBER
FROM ncc_po_credit)
AND hcua.SITE_USE_CODE = 'BILL_TO'
AND hcas.ORG_ID IN (612, 613, 614, 615, 616, 617))
LOOP
p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;
DBMS_OUTPUT.put_line ('profile_id = ' || i.profile_id);
MO_GLOBAL.INIT ('AR');
mo_global.set_policy_context ('S', '612');
l_user_id := 1295;
l_resp_id := 52514;
l_respid_id := 222;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_respid_id);
SELECT CREDIT_LIMIT_AMOUNT, CREDIT_LIMIT_AMOUNT
INTO mTrx, mOverall
FROM ncc_po_credit
WHERE CUSTOMER_NUMBER = i.account_number AND ORG_ID = i.org_id;
DBMS_OUTPUT.put_line ('Credit Limit = ' || mTrx || ' ' || mOverall);
DBMS_OUTPUT.put_line (
'CUST_ACCT_PROFILE_AMT_ID = ' || i.CUST_ACCT_PROFILE_AMT_ID
);
v_customer_profile_amt.cust_account_profile_id := i.profile_id;
v_customer_profile_amt.cust_acct_profile_amt_id :=
i.CUST_ACCT_PROFILE_AMT_ID;
v_customer_profile_amt.cust_account_id := i.cust_account_id;
v_customer_profile_amt.SITE_USE_id := i.site_use_id;
v_customer_profile_amt.currency_code := 'BHD';
v_customer_profile_amt.trx_credit_limit := mTrx;
v_customer_profile_amt.overall_credit_limit := mOverall;
-- v_customer_profile_amt.created_by_module := 'HZ_CPUI'; -- commented by Rushi
SELECT HCPA.object_version_number
INTO in_out_version_no_hcpa
FROM hz_customer_profiles hcp, HZ_CUST_PROFILE_AMTS HCPA
WHERE hcp.cust_account_profile_id = i.profile_id
AND HCP.CUST_ACCOUNT_PROFILE_ID = HCPA.CUST_ACCOUNT_PROFILE_ID;
DBMS_OUTPUT.put_line (
'Profile amt version = ' || in_out_version_no_hcpa
);
p_object_version_number := in_out_version_no_hcpa;
hz_customer_profile_v2pub.update_cust_profile_amt (
p_init_msg_list => 'T',
p_cust_profile_amt_rec => v_customer_profile_amt,
p_object_version_number => p_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 = ' || SUBSTR (x_return_status, 1, 255)
);
DBMS_OUTPUT.put_line (
'Object Version Number = ' || TO_CHAR (p_object_version_number)
);
DBMS_OUTPUT.put_line (
'Credit Rating = ' || p_customer_profile_rec_type.credit_rating
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR I IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line(I || '.'
|| SUBSTR (
FND_MSG_PUB.Get (
p_encoded => FND_API.G_FALSE
),
1,
255
));
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
DECLARE
p_customer_profile_rec_type HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_cust_account_profile_id NUMBER;
p_object_version_number NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_user_id number;
l_resp_id number;
l_respid_id number;
in_out_version_no number;
in_out_version_no_hcpa number;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
v_cust_act_prof_amt_id number;
v_cust_account_profile_id NUMBER;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
p_create_profile_amt VARCHAR2 (2000);
v_msg_dummy VARCHAR2 (5000);
t_output VARCHAR2 (5000);
mTrx number;
mOverall number;
BEGIN
FOR i
IN (SELECT hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hca.cust_account_id,
hcp.site_use_id,
account_number,
hcas.ORG_ID org_id,
CUST_ACCT_PROFILE_AMT_ID
FROM hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcua,
Hz_Customer_Profiles hcp,
HZ_CUST_PROFILE_AMTS hcpa
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hcua.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
AND hcp.cust_account_id = hca.cust_account_id
AND hcp.site_use_id = hcua.site_use_id
AND HCP.CUST_ACCOUNT_PROFILE_ID =
HCPA.CUST_ACCOUNT_PROFILE_ID
AND account_number IN
(SELECT DISTINCT CUSTOMER_NUMBER
FROM ncc_po_credit)
AND hcua.SITE_USE_CODE = 'BILL_TO'
AND hcas.ORG_ID IN (612, 613, 614, 615, 616, 617))
LOOP
p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;
DBMS_OUTPUT.put_line ('profile_id = ' || i.profile_id);
MO_GLOBAL.INIT ('AR');
mo_global.set_policy_context ('S', '612');
l_user_id := 1295;
l_resp_id := 52514;
l_respid_id := 222;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_respid_id);
SELECT CREDIT_LIMIT_AMOUNT, CREDIT_LIMIT_AMOUNT
INTO mTrx, mOverall
FROM ncc_po_credit
WHERE CUSTOMER_NUMBER = i.account_number AND ORG_ID = i.org_id;
DBMS_OUTPUT.put_line ('Credit Limit = ' || mTrx || ' ' || mOverall);
DBMS_OUTPUT.put_line (
'CUST_ACCT_PROFILE_AMT_ID = ' || i.CUST_ACCT_PROFILE_AMT_ID
);
v_customer_profile_amt.cust_account_profile_id := i.profile_id;
v_customer_profile_amt.cust_acct_profile_amt_id :=
i.CUST_ACCT_PROFILE_AMT_ID;
v_customer_profile_amt.cust_account_id := i.cust_account_id;
v_customer_profile_amt.SITE_USE_id := i.site_use_id;
v_customer_profile_amt.currency_code := 'BHD';
v_customer_profile_amt.trx_credit_limit := mTrx;
v_customer_profile_amt.overall_credit_limit := mOverall;
-- v_customer_profile_amt.created_by_module := 'HZ_CPUI'; -- commented by Rushi
SELECT HCPA.object_version_number
INTO in_out_version_no_hcpa
FROM hz_customer_profiles hcp, HZ_CUST_PROFILE_AMTS HCPA
WHERE hcp.cust_account_profile_id = i.profile_id
AND HCP.CUST_ACCOUNT_PROFILE_ID = HCPA.CUST_ACCOUNT_PROFILE_ID;
DBMS_OUTPUT.put_line (
'Profile amt version = ' || in_out_version_no_hcpa
);
p_object_version_number := in_out_version_no_hcpa;
hz_customer_profile_v2pub.update_cust_profile_amt (
p_init_msg_list => 'T',
p_cust_profile_amt_rec => v_customer_profile_amt,
p_object_version_number => p_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 = ' || SUBSTR (x_return_status, 1, 255)
);
DBMS_OUTPUT.put_line (
'Object Version Number = ' || TO_CHAR (p_object_version_number)
);
DBMS_OUTPUT.put_line (
'Credit Rating = ' || p_customer_profile_rec_type.credit_rating
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR I IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line(I || '.'
|| SUBSTR (
FND_MSG_PUB.Get (
p_encoded => FND_API.G_FALSE
),
1,
255
));
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
Customer Profile Update API
/* Formatted on 25-May-14 5:44:31 PM (QP5 v5.114.809.3010) */
DECLARE
p_customer_profile_rec_type HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_cust_account_profile_id NUMBER;
p_object_version_number NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_user_id number;
l_resp_id number;
l_respid_id number;
in_out_version_no number;
in_out_version_no_hcpa number;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
v_cust_act_prof_amt_id number;
v_cust_account_profile_id NUMBER;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
p_create_profile_amt VARCHAR2 (2000);
v_msg_dummy VARCHAR2 (5000);
t_output VARCHAR2 (5000);
mTrx number;
mOverall number;
BEGIN
FOR i
IN (SELECT hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hca.cust_account_id,
hcp.site_use_id,
account_number,
hcas.ORG_ID org_id,
CUST_ACCT_PROFILE_AMT_ID
FROM hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcua,
Hz_Customer_Profiles hcp,
HZ_CUST_PROFILE_AMTS hcpa
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hcua.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
AND hcp.cust_account_id = hca.cust_account_id
AND hcp.site_use_id = hcua.site_use_id
AND HCP.CUST_ACCOUNT_PROFILE_ID =
HCPA.CUST_ACCOUNT_PROFILE_ID
AND account_number IN
(SELECT DISTINCT CUSTOMER_NUMBER
FROM ncc_po_credit )
AND hcua.SITE_USE_CODE = 'BILL_TO'
AND hcas.ORG_ID IN (612, 613, 614, 615, 616, 617))
LOOP
p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;
p_customer_profile_rec_type.credit_checking := 'Y';
-- p_customer_profile_rec_type.credit_hold := 'Y';
SELECT object_version_number
INTO in_out_version_no
FROM hz_customer_profiles
WHERE cust_account_profile_id = i.profile_id;
p_object_version_number := in_out_version_no;
MO_GLOBAL.INIT ('AR');
mo_global.set_policy_context ('S', '612');
l_user_id := 1295;
l_resp_id := 52514;
l_respid_id := 222;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_respid_id);
hz_customer_profile_v2pub.update_customer_profile (
p_init_msg_list => 'T',
p_customer_profile_rec => p_customer_profile_rec_type,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT CREDIT_LIMIT_AMOUNT, CREDIT_LIMIT_AMOUNT
INTO mTrx, mOverall
FROM ncc_po_credit
WHERE CUSTOMER_NUMBER = i.account_number AND ORG_ID = i.org_id;
v_customer_profile_amt.cust_account_profile_id := i.profile_id;
v_customer_profile_amt.cust_acct_profile_amt_id :=
i.CUST_ACCT_PROFILE_AMT_ID;
v_customer_profile_amt.cust_account_id := i.cust_account_id;
v_customer_profile_amt.SITE_USE_id := i.site_use_id;
v_customer_profile_amt.currency_code := 'BHD';
v_customer_profile_amt.trx_credit_limit := mTrx;
v_customer_profile_amt.overall_credit_limit := mOverall;
v_customer_profile_amt.created_by_module := 'HZ_CPUI';
DBMS_OUTPUT.put_line (
'x_return_status = ' || SUBSTR (x_return_status, 1, 255)
);
DBMS_OUTPUT.put_line (
'Object Version Number = ' || TO_CHAR (p_object_version_number)
);
DBMS_OUTPUT.put_line (
'Credit Rating = ' || p_customer_profile_rec_type.credit_rating
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR I IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line(I || '.'
|| SUBSTR (
FND_MSG_PUB.Get (
p_encoded => FND_API.G_FALSE
),
1,
255
));
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
DECLARE
p_customer_profile_rec_type HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_cust_account_profile_id NUMBER;
p_object_version_number NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_user_id number;
l_resp_id number;
l_respid_id number;
in_out_version_no number;
in_out_version_no_hcpa number;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
v_cust_act_prof_amt_id number;
v_cust_account_profile_id NUMBER;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
p_create_profile_amt VARCHAR2 (2000);
v_msg_dummy VARCHAR2 (5000);
t_output VARCHAR2 (5000);
mTrx number;
mOverall number;
BEGIN
FOR i
IN (SELECT hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
hca.cust_account_id,
hcp.site_use_id,
account_number,
hcas.ORG_ID org_id,
CUST_ACCT_PROFILE_AMT_ID
FROM hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcua,
Hz_Customer_Profiles hcp,
HZ_CUST_PROFILE_AMTS hcpa
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hcua.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
AND hcp.cust_account_id = hca.cust_account_id
AND hcp.site_use_id = hcua.site_use_id
AND HCP.CUST_ACCOUNT_PROFILE_ID =
HCPA.CUST_ACCOUNT_PROFILE_ID
AND account_number IN
(SELECT DISTINCT CUSTOMER_NUMBER
FROM ncc_po_credit )
AND hcua.SITE_USE_CODE = 'BILL_TO'
AND hcas.ORG_ID IN (612, 613, 614, 615, 616, 617))
LOOP
p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;
p_customer_profile_rec_type.credit_checking := 'Y';
-- p_customer_profile_rec_type.credit_hold := 'Y';
SELECT object_version_number
INTO in_out_version_no
FROM hz_customer_profiles
WHERE cust_account_profile_id = i.profile_id;
p_object_version_number := in_out_version_no;
MO_GLOBAL.INIT ('AR');
mo_global.set_policy_context ('S', '612');
l_user_id := 1295;
l_resp_id := 52514;
l_respid_id := 222;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_respid_id);
hz_customer_profile_v2pub.update_customer_profile (
p_init_msg_list => 'T',
p_customer_profile_rec => p_customer_profile_rec_type,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT CREDIT_LIMIT_AMOUNT, CREDIT_LIMIT_AMOUNT
INTO mTrx, mOverall
FROM ncc_po_credit
WHERE CUSTOMER_NUMBER = i.account_number AND ORG_ID = i.org_id;
v_customer_profile_amt.cust_account_profile_id := i.profile_id;
v_customer_profile_amt.cust_acct_profile_amt_id :=
i.CUST_ACCT_PROFILE_AMT_ID;
v_customer_profile_amt.cust_account_id := i.cust_account_id;
v_customer_profile_amt.SITE_USE_id := i.site_use_id;
v_customer_profile_amt.currency_code := 'BHD';
v_customer_profile_amt.trx_credit_limit := mTrx;
v_customer_profile_amt.overall_credit_limit := mOverall;
v_customer_profile_amt.created_by_module := 'HZ_CPUI';
DBMS_OUTPUT.put_line (
'x_return_status = ' || SUBSTR (x_return_status, 1, 255)
);
DBMS_OUTPUT.put_line (
'Object Version Number = ' || TO_CHAR (p_object_version_number)
);
DBMS_OUTPUT.put_line (
'Credit Rating = ' || p_customer_profile_rec_type.credit_rating
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR I IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line(I || '.'
|| SUBSTR (
FND_MSG_PUB.Get (
p_encoded => FND_API.G_FALSE
),
1,
255
));
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
Order Hold
SELECT ooha.header_id,
order_number,
hold_lines.line_id,
oe_order_misc_pub.get_concat_line_number (hold_lines.line_id) so_line_number,
/* Uncomment below to get the concatenated line number if you are running from non apps user */
ohd.name "Hold Name",
holds.released_flag,
holds.creation_date "Hold Applied Date",
ohr.creation_date "Hold Released date",
ohr.release_reason_code,
ohr.release_comment
FROM oe_order_lines_all hold_lines,
oe_order_headers_all ooha,
oe_order_holds_all holds,
oe_hold_sources_all ohsa,
oe_hold_releases ohr,
oe_hold_definitions ohd
WHERE holds.line_id = hold_lines.line_id(+)
AND holds.header_id = hold_lines.header_id(+)
AND holds.hold_release_id = ohr.hold_release_id(+)
AND holds.hold_source_id = ohsa.hold_source_id
AND ohsa.hold_id = ohd.hold_id
AND holds.header_id = ooha.header_id
AND ooha.order_number = :P_Order_Number
order_number,
hold_lines.line_id,
oe_order_misc_pub.get_concat_line_number (hold_lines.line_id) so_line_number,
/* Uncomment below to get the concatenated line number if you are running from non apps user */
/*hold_lines.line_number || '.' || hold_lines.shipment_number
|| DECODE (hold_lines.option_number,
NULL, NULL,
'.' || hold_lines.option_number)
|| DECODE (
hold_lines.component_number,
NULL,
NULL,
DECODE (hold_lines.option_number, NULL, '.')
|| '.'
|| hold_lines.component_number
)
|| DECODE (
hold_lines.service_number,
NULL,
NULL,
DECODE (hold_lines.option_number, NULL, '.')
|| DECODE (hold_lines.component_number, NULL, '.')
|| '.'
|| hold_lines.service_number
)
so_line_number, */ohd.name "Hold Name",
holds.creation_date "Hold Applied Date",
ohr.creation_date "Hold Released date",
ohr.release_reason_code,
ohr.release_comment
FROM oe_order_lines_all hold_lines,
oe_order_headers_all ooha,
oe_order_holds_all holds,
oe_hold_sources_all ohsa,
oe_hold_releases ohr,
oe_hold_definitions ohd
WHERE holds.line_id = hold_lines.line_id(+)
AND holds.header_id = hold_lines.header_id(+)
AND holds.hold_release_id = ohr.hold_release_id(+)
AND holds.hold_source_id = ohsa.hold_source_id
AND ohsa.hold_id = ohd.hold_id
AND holds.header_id = ooha.header_id
AND ooha.order_number = :P_Order_Number
ORDER BY NVL(hold_lines.line_id,0), ohsa.hold_source_id
Tuesday, February 18, 2014
AR Customer Master Query
SELECT hca.account_number --,cust.party_id
-- (select ORGANIZATION_NAME from org_organization_definitions where OPERATING_UNIT = hcsu.org_id) org
,customer_name,
( hl.address1
|| ' '
|| hl.address2
|| ' '
|| hl.address3
|| ' '
|| hl.address4)
ship_to,
hl.city,
hl.country,
(SELECT hcp.raw_phone_number
FROM hz_contact_points hcp, hz_relationships hr, hz_parties hzp
WHERE hcp.owner_table_id = hr.party_id
AND hr.object_id = NVL (cust.party_id, hr.object_id)
AND hr.status = 'A'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hzp.party_id = hr.party_id
AND hcp.phone_line_type = 'EMAIL'
AND hcp.owner_table_name = 'HZ_PARTIES')
email,
(SELECT description
FROM ar_customer_profiles_v a, ra_terms b
WHERE customer_id = NVL (f.customer_id, customer_id)
AND status = 'A'
AND site_use_id IS NULL
AND a.standard_terms = b.term_id)
cust_profile,
-- hcsu.org_id,
xyka_custom_yka.get_ar_balance_yka (customer_id) balance,
xyka_custom_yka.get_ar_overall_cr_limit (customer_id) cr_limit,
xyka_custom_yka.get_ar_customer_category_name (customer_id)
customer_category,
xyka_custom_yka.get_ar_credit_check (customer_id, 126) credit_check, -- org_id
xyka_custom_yka.get_ar_collector_code (customer_id, 126) collector,
(SELECT MAX (name) collector_id
--into l_collector_code
FROM ar_customers a, hz_customer_profiles b, ar_collectors c
WHERE a.customer_id = b.cust_account_id
AND a.customer_id = b.cust_account_id
AND b.site_use_id IS NULL
AND b.collector_id = c.collector_id
AND a.customer_id = f.customer_id)
collector_name,
(SELECT MAX (credit_checking)
FROM hz_customer_profiles hcp
WHERE hcp.cust_account_id = f.customer_id) cr_chk ,
(select segment5 from gl_code_combinations where code_combination_id = hcsu.GL_ID_REC ) gcc
FROM hz_parties cust,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_locations hl,
ar_customers f
WHERE 1 = 1
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.location_id(+) = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND cust.party_id = hca.party_id
AND f.customer_id = hca.cust_account_id
--and hcsu.primary_flag = 'y'
AND hcsu.status = 'A'
AND hcsu.site_use_code = 'BILL_TO'
-- AND hca.cust_account_id = 7168
AND hcsu.org_id = NVL (:org_id, hcsu.org_id)
and hca.account_number like 'S%'
order by 1
-- (select ORGANIZATION_NAME from org_organization_definitions where OPERATING_UNIT = hcsu.org_id) org
,customer_name,
( hl.address1
|| ' '
|| hl.address2
|| ' '
|| hl.address3
|| ' '
|| hl.address4)
ship_to,
hl.city,
hl.country,
(SELECT hcp.raw_phone_number
FROM hz_contact_points hcp, hz_relationships hr, hz_parties hzp
WHERE hcp.owner_table_id = hr.party_id
AND hr.object_id = NVL (cust.party_id, hr.object_id)
AND hr.status = 'A'
AND hcp.status = 'A'
AND hcp.primary_flag = 'Y'
AND hzp.party_id = hr.party_id
AND hcp.phone_line_type = 'EMAIL'
AND hcp.owner_table_name = 'HZ_PARTIES')
email,
(SELECT description
FROM ar_customer_profiles_v a, ra_terms b
WHERE customer_id = NVL (f.customer_id, customer_id)
AND status = 'A'
AND site_use_id IS NULL
AND a.standard_terms = b.term_id)
cust_profile,
-- hcsu.org_id,
xyka_custom_yka.get_ar_balance_yka (customer_id) balance,
xyka_custom_yka.get_ar_overall_cr_limit (customer_id) cr_limit,
xyka_custom_yka.get_ar_customer_category_name (customer_id)
customer_category,
xyka_custom_yka.get_ar_credit_check (customer_id, 126) credit_check, -- org_id
xyka_custom_yka.get_ar_collector_code (customer_id, 126) collector,
(SELECT MAX (name) collector_id
--into l_collector_code
FROM ar_customers a, hz_customer_profiles b, ar_collectors c
WHERE a.customer_id = b.cust_account_id
AND a.customer_id = b.cust_account_id
AND b.site_use_id IS NULL
AND b.collector_id = c.collector_id
AND a.customer_id = f.customer_id)
collector_name,
(SELECT MAX (credit_checking)
FROM hz_customer_profiles hcp
WHERE hcp.cust_account_id = f.customer_id) cr_chk ,
(select segment5 from gl_code_combinations where code_combination_id = hcsu.GL_ID_REC ) gcc
FROM hz_parties cust,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_locations hl,
ar_customers f
WHERE 1 = 1
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.location_id(+) = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND cust.party_id = hca.party_id
AND f.customer_id = hca.cust_account_id
--and hcsu.primary_flag = 'y'
AND hcsu.status = 'A'
AND hcsu.site_use_code = 'BILL_TO'
-- AND hca.cust_account_id = 7168
AND hcsu.org_id = NVL (:org_id, hcsu.org_id)
and hca.account_number like 'S%'
order by 1
Saturday, February 15, 2014
AP Trial Balance Report Redinition
The R12 Accounts Payable Trial Balance has 4 templates available that control the output that is
displayed. The four templates available are:
Accounts Payable Trial Balance - Group by Account, Summary
Accounts Payable Trial Balance - Group by Account, Detail
Accounts Payable Trial Balance - Group by Third Party, Summary
Accounts Payable Trial Balance - Group by Third Party, Detail
Do the following to change the template that is used when the report is submitted:
1. Navigate: Payables Responsibility>Other>Request>Run
2. Select the Accounts Payable Trial Balance
3. Enter all of the parameters for the report. DO NOT SUBMIT THE REPORT.
4. Click on the Options button in the Upon Completion section of the Submit Request screen.
5. Select the desired template name.
6. Submit the Trial balance.
Do the following to change the default template used when the report is submitted:
1. Navigate: System Administration Responsibility>Concurrent>Programs
2. Search for:
Application = Payables
Program = Accounts Payable Trial Balance
Click on Go
3. Click on Update for the Accounts Payable Trial Balance program
4. Select the Onsite Setting tab
5. Select the default template from the Template dropdown in the General section.
6. Click on Apply to save the changes.
--------------------------- IMPORTANT --------------------------------
Navigate to AP->Setup->Accounting Setups->Subledger Accounting Setup->Open Account Balances Listing Definitions
Click on Create and give in the details.
Save and try to run the Report. It should show LOV now.
displayed. The four templates available are:
Accounts Payable Trial Balance - Group by Account, Summary
Accounts Payable Trial Balance - Group by Account, Detail
Accounts Payable Trial Balance - Group by Third Party, Summary
Accounts Payable Trial Balance - Group by Third Party, Detail
Do the following to change the template that is used when the report is submitted:
1. Navigate: Payables Responsibility>Other>Request>Run
2. Select the Accounts Payable Trial Balance
3. Enter all of the parameters for the report. DO NOT SUBMIT THE REPORT.
4. Click on the Options button in the Upon Completion section of the Submit Request screen.
5. Select the desired template name.
6. Submit the Trial balance.
Do the following to change the default template used when the report is submitted:
1. Navigate: System Administration Responsibility>Concurrent>Programs
2. Search for:
Application = Payables
Program = Accounts Payable Trial Balance
Click on Go
3. Click on Update for the Accounts Payable Trial Balance program
4. Select the Onsite Setting tab
5. Select the default template from the Template dropdown in the General section.
6. Click on Apply to save the changes.
--------------------------- IMPORTANT --------------------------------
Navigate to AP->Setup->Accounting Setups->Subledger Accounting Setup->Open Account Balances Listing Definitions
Click on Create and give in the details.
Save and try to run the Report. It should show LOV now.
Subscribe to:
Posts (Atom)