--DELETE FROM xx_vendor_api_gl WHERE NUM IS NULL
SET SERVEROUTPUT ON;
DECLARE
c_value_set_name CONSTANT VARCHAR2(100) := 'XX_Sub Account';
c_parent_value CONSTANT VARCHAR2(100) := '2210000';
l_storage_value VARCHAR2(32000);
l_exists NUMBER;
l_err_msg VARCHAR2(2000);
CURSOR fetch_details
IS
SELECT num,
vendor_no,
vendor_name,
liability_account,
/* Segment 5: 353 becomes 0353 */
LPAD(
REGEXP_SUBSTR(
TRIM(liability_account),
'[^.]+',
1,
5
),
4,
'0'
) sub_account
FROM xx_vendor_api_gl
WHERE --vendor_no = 'XX-0354' AND
NVL(process_flag, 'N') = 'N';
BEGIN
FOR r IN fetch_details
LOOP
SAVEPOINT vendor_flex_value;
BEGIN
IF r.sub_account IS NULL THEN
RAISE_APPLICATION_ERROR(
-20001,
'Unable to derive sub-account from: '
|| r.liability_account
);
END IF;
/*
* A dependent value is uniquely identified by:
* value set + parent value + child value
*/
SELECT COUNT(*)
INTO l_exists
FROM apps.fnd_flex_values_vl fv
JOIN apps.fnd_flex_value_sets fvs
ON fvs.flex_value_set_id = fv.flex_value_set_id
WHERE fvs.flex_value_set_name = c_value_set_name
AND fv.parent_flex_value_low = c_parent_value
AND fv.flex_value = r.sub_account;
IF l_exists = 0 THEN
apps.fnd_flex_val_api.create_dependent_vset_value(
p_flex_value_set_name => c_value_set_name,
p_parent_flex_value => c_parent_value,
p_flex_value => r.sub_account,
p_description => r.vendor_name,
p_enabled_flag => 'Y',
p_start_date_active => NULL,
p_end_date_active => NULL,
p_hierarchy_level => NULL,
x_storage_value => l_storage_value
);
DBMS_OUTPUT.put_line(
'Created: parent=' || c_parent_value
|| ', child=' || r.sub_account
|| ', stored value=' || l_storage_value
);
ELSE
/*
* Record already exists. Update the description/vendor name
* so rerunning this upload is safe.
*/
apps.fnd_flex_val_api.update_dependent_vset_value(
p_flex_value_set_name => c_value_set_name,
p_parent_flex_value => c_parent_value,
p_flex_value => r.sub_account,
p_description => r.vendor_name,
p_enabled_flag => 'Y',
x_storage_value => l_storage_value
);
DBMS_OUTPUT.put_line(
'Updated existing value: parent=' || c_parent_value
|| ', child=' || r.sub_account
);
END IF;
UPDATE xx_vendor_api_gl
SET process_flag = 'Y',
remarks = NULL
WHERE num = r.num;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_err_msg :=
SUBSTR(
SQLERRM
|| ' | API: '
|| apps.fnd_flex_val_api.message,
1,
2000
);
ROLLBACK TO vendor_flex_value;
UPDATE xx_vendor_api_gl
SET process_flag = 'N',
remarks = l_err_msg
WHERE num = r.num;
COMMIT;
DBMS_OUTPUT.put_line(
'Failed vendor ' || r.vendor_no
|| ': ' || l_err_msg
);
END;
END LOOP;
END;
/
No comments:
Post a Comment