This Blog is used to automate PO Promise Date/Need By Date Based on Date Entered in PO Line level/Shipment level by using Form Personalization
Please follow the steps as per documents attached
oracle-ebs-po-needby_promise-date-automation-form-personalization
— Package Spec—-
CREATE OR REPLACE PACKAGE APPS.xdmc_po_date_calc_pkg
IS
FUNCTION get_date_fnc (
p_date IN VARCHAR2,
p_vendor_site_id IN NUMBER,
p_type IN VARCHAR2
)
RETURN DATE;
FUNCTION get_propose_need_by_lease_day (p_country VARCHAR2)
RETURN NUMBER;
PROCEDURE DEBUG (p_msg VARCHAR2);
END xdmc_po_date_calc_pkg;
/
— Package Body ——
CREATE OR REPLACE PACKAGE BODY APPS.xdmc_po_date_calc_pkg
IS
*/– Calculate need by date and Promise date based on vendor site and lead days against country of origin enterd in PO
FUNCTION get_date_fnc (
p_date IN VARCHAR2,
p_vendor_site_id IN NUMBER,
p_type IN VARCHAR2
)
RETURN DATE
AS
l_country_of_origin_code ap_supplier_sites_all.country_of_origin_code%TYPE
:= NULL;
l_days NUMBER := NULL;
l_date DATE := NULL;
BEGIN
BEGIN
SELECT country_of_origin_code
INTO l_country_of_origin_code
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_vendor_site_id;
EXCEPTION
WHEN OTHERS
THEN
l_country_of_origin_code := NULL;
END;
IF l_country_of_origin_code IS NOT NULL
THEN
IF l_country_of_origin_code IN (‘US’, ‘MX’)
THEN
l_days := 0;
ELSE
l_days :=
NVL (get_propose_need_by_lease_day (l_country_of_origin_code),
0
);
END IF;
IF p_type = ‘N’
THEN
l_date :=
TO_DATE (p_date, ‘DD-MM-RRRR HH24:MI:SS’)
+ NVL (l_days, 0);
END IF;
IF p_type = ‘P’
THEN
l_date :=
TO_DATE (p_date, ‘DD-MM-RRRR HH24:MI:SS’)
– NVL (l_days, 0);
END IF;
END IF;
RETURN l_date;
EXCEPTION
WHEN OTHERS
THEN
RETURN l_date;
END get_date_fnc;
— calaculate lead days based on country
FUNCTION get_propose_need_by_lease_day (p_country VARCHAR2)
RETURN NUMBER
IS
l_lead_day NUMBER;
BEGIN
SELECT cd_grand_total
INTO l_lead_day
FROM apps.xdmc_path_to_eta
WHERE ship_date = (SELECT MAX (ship_date)
FROM apps.xdmc_path_to_eta
WHERE t_ship_from_loc = p_country)
AND t_ship_from_loc = p_country;
RETURN l_lead_day;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
DEBUG (‘ No data derive the lead days’);
WHEN OTHERS
THEN
RETURN 0;
DEBUG (‘ Eror derive the lead days’);
END get_propose_need_by_lease_day;
— Print debug message to get logs
PROCEDURE DEBUG (p_msg VARCHAR2)
AS
BEGIN
DBMS_OUTPUT.put_line (p_msg);
fnd_file.put_line (fnd_file.LOG, p_msg);
END DEBUG;
END xdmc_po_date_calc_pkg;
/
