/* 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;
Creating a blog is not just a hobby it’s a partnership to grow together.
Tuesday, May 27, 2014
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
Subscribe to:
Posts (Atom)