Page 1
/* Formatted on 11/4/2025 12:02:10 PM (QP5 v5.388) */
CREATE OR REPLACE PACKAGE BODY APPS.xxff_general_pkg
IS
FUNCTION get_ou_of_inventory_org (p_organization_id NUMBER)
RETURN NUMBER
IS
/*Tac dung cua ham la lay thong tin ou cua inventory org*/
v_ou NUMBER;
BEGIN
SELECT hoi.org_information3
INTO v_ou
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.organization_id = p_organization_id;
RETURN v_ou;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_ou_of_inventory_org;
----------------------------------------------
-- Tra ve khoi luong ton kho cua vat tu
--
FUNCTION INV_ONHAND (p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_get_onhand_date IN DATE,
p_lot IN VARCHAR2,
p_locator IN VARCHAR2,
p_total IN VARCHAR2 DEFAULT 'N')
RETURN NUMBER
IS
current_qty NUMBER;
trans_qty NUMBER;
onhand_qty NUMBER;
BEGIN
IF p_total = 'Y'
THEN
SELECT NVL (SUM (moq.transaction_quantity), 0)
INTO current_qty
FROM mtl_onhand_quantities moq
WHERE moq.inventory_item_id = p_inventory_item_id;
SELECT NVL (SUM (mmt.primary_quantity), 0)
INTO trans_qty
FROM mtl_material_transactions mmt
WHERE mmt.inventory_item_id = p_inventory_item_id
AND (mmt.transaction_action_id IN (1,
24,
27,
30,
31,
32,
4,
8))
AND TRUNC (mmt.transaction_date) > p_get_onhand_date;
onhand_qty := NVL (current_qty, 0) - NVL (trans_qty, 0);
RETURN onhand_qty;
ELSIF p_total = 'N'
THEN
SELECT NVL (SUM (moq.transaction_quantity), 0)
INTO current_qty
FROM mtl_onhand_quantities moq
WHERE moq.inventory_item_id = p_inventory_item_id
AND (moq.organization_id = p_organization_id)
AND ( moq.subinventory_code = p_subinventory_code
OR p_subinventory_code IS NULL)
AND (moq.locator_id = p_locator OR p_locator IS NULL)
AND (moq.lot_number = p_lot OR p_lot IS NULL);
SELECT NVL (
SUM (NVL (mtl.primary_quantity, mmt.primary_quantity)),
0)
INTO trans_qty
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtl
WHERE mmt.inventory_item_id = p_inventory_item_id
AND (mmt.organization_id = p_organization_id)
AND ( mmt.subinventory_code = p_subinventory_code
OR p_subinventory_code IS NULL)
AND mmt.transaction_id = mtl.transaction_id(+)
AND (mmt.locator_id = p_locator OR p_locator IS NULL)
AND (mtl.lot_number = p_lot OR p_lot IS NULL)
AND mmt.transaction_type_id NOT IN (10008)
AND TRUNC (mmt.transaction_date) >= p_get_onhand_date;
onhand_qty := NVL (current_qty, 0) - NVL (trans_qty, 0);
RETURN onhand_qty;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END INV_ONHAND;
FUNCTION INV_ONHAND (p_INVENTORY_ITEM_ID NUMBER,
p_ORGANIZATION_ID NUMBER,
p_SUBINVENTORY_CODE VARCHAR2,
p_GET_ONHAND_DATE DATE,
p_STATUS VARCHAR2)
RETURN NUMBER
IS
CURRENT_QTY NUMBER;
TRANS_QTY NUMBER;
ONHAND_QTY NUMBER;
BEGIN
-- Lay onhand hien thoi
SELECT NVL (SUM (MOQ.TRANSACTION_QUANTITY), 0)
INTO CURRENT_QTY
FROM MTL_ONHAND_QUANTITIES MOQ
WHERE MOQ.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND MOQ.ORGANIZATION_ID = p_ORGANIZATION_ID
AND ( MOQ.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
OR p_SUBINVENTORY_CODE IS NULL);
IF (p_STATUS = 'TD')
THEN
-- Lay so luong giao dich cho ton dau
SELECT NVL (SUM (MMT.PRIMARY_QUANTITY), 0)
INTO TRANS_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = p_ORGANIZATION_ID
AND ( MMT.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
OR p_SUBINVENTORY_CODE IS NULL)
AND TRUNC (MMT.TRANSACTION_DATE) >=
TRUNC (p_GET_ONHAND_DATE)
AND MMT.Transaction_Action_Id NOT IN (36, 24)--and (mmt.transaction_action_id in (1, 24, 27, 30, 31, 32, 4, 8))
; --Staging transfer
END IF;
IF (p_STATUS = 'TC')
THEN
-- Lay so luong giao dich cho ton cuoi
SELECT NVL (SUM (MMT.PRIMARY_QUANTITY), 0)
INTO TRANS_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = p_ORGANIZATION_ID
AND ( MMT.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
OR p_SUBINVENTORY_CODE IS NULL)
AND TRUNC (MMT.TRANSACTION_DATE) >
TRUNC (p_GET_ONHAND_DATE)
AND MMT.Transaction_Action_Id NOT IN (36, 24)--and (mmt.transaction_action_id in (1, 24, 27, 30, 31, 32, 4, 8))
; --Staging transfer
END IF;
ONHAND_QTY := NVL (CURRENT_QTY, 0) - NVL (TRANS_QTY, 0);
RETURN ONHAND_QTY;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
FUNCTION INV_LOT_ONHAND (p_INVENTORY_ITEM_ID NUMBER,
p_LOT_NUMBER VARCHAR2,
p_ORGANIZATION_ID NUMBER,
p_SUBINVENTORY_CODE VARCHAR2,
p_GET_ONHAND_DATE DATE,
p_STATUS VARCHAR2)
RETURN NUMBER
IS
CURRENT_QTY NUMBER;
TRANS_QTY NUMBER;
ONHAND_QTY NUMBER;
BEGIN
-- Lay onhand hien thoi
SELECT NVL (SUM (MOQ.TRANSACTION_QUANTITY), 0)
INTO CURRENT_QTY
FROM MTL_ONHAND_QUANTITIES MOQ
WHERE MOQ.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND (moq.LOT_NUMBER = p_lot_number OR p_lot_number IS NULL)
AND MOQ.ORGANIZATION_ID = p_ORGANIZATION_ID
AND ( MOQ.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
OR p_SUBINVENTORY_CODE IS NULL);
IF (p_STATUS = 'TD')
THEN
-- Lay so luong giao dich cho ton dau
SELECT NVL (
SUM (NVL (mtl.primary_quantity, mmt.primary_quantity)),
0) --NVL(SUM(MMT.PRIMARY_QUANTITY), 0)
INTO TRANS_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT,
mtl_transaction_lot_numbers mtl
WHERE MMT.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND mmt.transaction_id = mtl.transaction_id(+)
AND (mtl.lot_number = p_lot_number OR p_lot_number IS NULL)
AND MMT.ORGANIZATION_ID = p_ORGANIZATION_ID
AND ( MMT.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
OR p_SUBINVENTORY_CODE IS NULL)
AND MMT.TRANSACTION_DATE >= p_GET_ONHAND_DATE
AND MMT.Transaction_Action_Id NOT IN (36, 24); --Staging transfer
END IF;
IF (p_STATUS = 'TC')
THEN
-- Lay so luong giao dich cho ton cuoi
SELECT NVL (
SUM (NVL (mtl.primary_quantity, mmt.primary_quantity)),
0) --NVL(SUM(MMT.PRIMARY_QUANTITY), 0)
INTO TRANS_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT,
mtl_transaction_lot_numbers mtl
WHERE MMT.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND mmt.transaction_id = mtl.transaction_id(+)
AND (mtl.lot_number = p_lot_number OR p_lot_number IS NULL)
AND MMT.ORGANIZATION_ID = p_ORGANIZATION_ID
AND ( MMT.SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
OR p_SUBINVENTORY_CODE IS NULL)
AND MMT.TRANSACTION_DATE > p_GET_ONHAND_DATE
AND MMT.Transaction_Action_Id NOT IN (36, 24); --Staging transfer
END IF;
ONHAND_QTY := NVL (CURRENT_QTY, 0) - NVL (TRANS_QTY, 0);
RETURN ONHAND_QTY;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
------------------------------------------------------------
--Lay gia
------------------------------------------------------------
FUNCTION OPM_ITEM_COST (X_ORGANIZATION_ID NUMBER,
X_INVENTORY_ITEM_ID NUMBER,
X_CALENDAR_CODE VARCHAR2,
X_PERIOD_CODE VARCHAR2,
X_COST_TYPE VARCHAR2)
RETURN NUMBER
IS
V_ITEM_COST NUMBER;
P_F_DATE DATE; --add by ngocquoc 02032021
P_T_DATE DATE; --add by ngocquoc 02032021
BEGIN
/*begin add by ngocquoc 02032021*/
SELECT t.start_date, t.end_date
INTO P_F_DATE, P_T_DATE
FROM CM_CLDR_DTL t
WHERE t.calendar_code = X_CALENDAR_CODE
AND t.period_code = X_PERIOD_CODE;
/*end add by ngocquoc 02032021*/
SELECT SUM (CMPNT_COST)
INTO V_ITEM_COST
FROM cm_cmpt_mst cmp,
mtl_system_items itm,
cm_cmpt_dtl dtl,
cm_mthd_mst mth,
gmf_period_statuses gmf,
CM_WHSE_ASC T
WHERE 1 = 1
AND itm.inventory_item_id = dtl.inventory_item_id
/*and itm.organization_id in (SELECT T.ORGANIZATION_ID
FROM CM_WHSE_ASC T
WHERE T.COST_ORGANIZATION_ID in (109,110)
and t.organization_id=X_ORGANIZATION_ID) */
AND itm.organization_id = t.organization_id
AND t.cost_organization_id = dtl.organization_id
AND gmf.period_id = dtl.period_id
AND cmp.cost_cmpntcls_id = dtl.cost_cmpntcls_id
AND mth.cost_type_id = dtl.cost_type_id
AND NVL (t.delete_mark, 0) <> 1 -- KhongLayNhungDongBiDeleted
AND P_F_DATE >= t.eff_start_date --add by ngocquoc 02032021
AND P_F_DATE <= t.eff_end_date --add by ngocquoc 02032021
/*and to_date(to_char(to_date(P_F_DATE, 'dd/mm/yyyy'),
'yyyy/mm/dd hh24:mi:ss'),
'yyyy/mm/dd hh24:mi:ss') >= t.eff_start_date --add by ngocquoc 02032021
and to_date(to_char(to_date(P_F_DATE, 'dd/mm/yyyy'),
'yyyy/mm/dd hh24:mi:ss'),
'yyyy/mm/dd hh24:mi:ss') <= t.eff_end_date --add by ngocquoc 02032021*/
AND itm.inventory_item_id = X_INVENTORY_ITEM_ID
AND itm.organization_id = X_ORGANIZATION_ID
AND gmf.calendar_code = X_CALENDAR_CODE
AND gmf.period_code = X_PERIOD_CODE
AND mth.cost_mthd_code = X_COST_TYPE;
RETURN V_ITEM_COST;
END;
-- Lay_Gia_Theo_Cost_Organization
FUNCTION OPM_ITEM_COST (X_COST_ORGANIZATION_ID NUMBER,
X_INVENTORY_ITEM_ID NUMBER,
X_CALENDAR_CODE VARCHAR2,
X_PERIOD_CODE VARCHAR2,
X_COST_TYPE VARCHAR2)
RETURN NUMBER
IS
V_ITEM_COST NUMBER;
BEGIN
SELECT SUM (CMPNT_COST)
INTO V_ITEM_COST
FROM cm_cmpt_mst cmp,
mtl_system_items itm,
cm_cmpt_dtl dtl,
cm_mthd_mst mth,
gmf_period_statuses gmf,
CM_WHSE_ASC T
WHERE 1 = 1
AND itm.inventory_item_id = dtl.inventory_item_id
AND itm.organization_id = t.organization_id
AND t.cost_organization_id = dtl.organization_id
AND gmf.period_id = dtl.period_id
AND cmp.cost_cmpntcls_id = dtl.cost_cmpntcls_id
AND mth.cost_type_id = dtl.cost_type_id
AND NVL (t.delete_mark, 0) <> 1 -- KhongLayNhungDongBiDeleted
AND itm.inventory_item_id = X_INVENTORY_ITEM_ID
AND T.cost_organization_id = X_COST_ORGANIZATION_ID
AND gmf.calendar_code = X_CALENDAR_CODE
AND gmf.period_code = X_PERIOD_CODE
AND mth.cost_mthd_code = X_COST_TYPE;
RETURN V_ITEM_COST;
END OPM_ITEM_COST;
---------------------------------------------------------------------------------------------------
-----Ham so sanh 1 tai khoan full voi 2 tham so tai khoan MAX, MIN gia tri tra ve la Y hoac N -----
---------------------------------------------------------------------------------------------------
FUNCTION CHECK_FULL_ACCT (p_code_combination_id NUMBER,
p_min_flex VARCHAR2,
p_max_flex VARCHAR2)
RETURN VARCHAR2
IS
v_acc_flex VARCHAR2 (500);
v_acc_flex_t VARCHAR2 (500);
v_min_flex_t VARCHAR2 (500) := p_min_flex;
v_max_flex_t VARCHAR2 (500) := p_max_flex;
v_acc_segment VARCHAR2 (25);
v_min_segment VARCHAR2 (25);
v_max_segment VARCHAR2 (25);
BEGIN
--lay tai khoan can so sanh
SELECT gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
|| '.'
|| gcc.segment9
|| '.'
|| gcc.segment10
|| '.'
|| gcc.segment11
|| '.'
|| gcc.segment12
|| '.'
|| gcc.segment13
|| '.'
|| gcc.segment14
|| '.'
|| gcc.segment15
|| '.'
|| gcc.segment16
|| '.'
|| gcc.segment17
|| '.'
|| gcc.segment18
|| '.'
|| gcc.segment19
|| '.'
|| gcc.segment20
|| '.'
|| gcc.segment21
|| '.'
|| gcc.segment22
|| '.'
|| gcc.segment23
|| '.'
|| gcc.segment24
|| '.'
|| gcc.segment25
|| '.'
|| gcc.segment26
|| '.'
|| gcc.segment27
|| '.'
|| gcc.segment28
|| '.'
|| gcc.segment29
|| '.'
|| gcc.segment30
INTO v_acc_flex
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_code_combination_id;
IF v_min_flex_t IS NULL OR v_max_flex_t IS NULL
THEN
RETURN 'N';
END IF;
-----Xu ly so sanh tung segment------
v_acc_flex_t := v_acc_flex;
LOOP
-- dbms_output.put_line(v_min_flex_t);
EXIT WHEN v_min_flex_t IS NULL OR v_max_flex_t IS NULL;
v_min_segment :=
SUBSTR (v_min_flex_t, 1, INSTR (v_min_flex_t, '.', 1) - 1);
v_max_segment :=
SUBSTR (v_max_flex_t, 1, INSTR (v_max_flex_t, '.', 1) - 1);
v_acc_segment :=
SUBSTR (v_acc_flex_t, 1, INSTR (v_acc_flex_t, '.', 1) - 1);
/*dbms_output.put_line(v_min_segment);
dbms_output.put_line(v_max_segment);
dbms_output.put_line(v_acc_segment);*/
IF v_acc_segment < v_min_segment OR v_acc_segment > v_max_segment
THEN
RETURN 'N';
END IF;
v_min_flex_t :=
SUBSTR (v_min_flex_t, INSTR (v_min_flex_t, '.', 1) + 1);
v_max_flex_t :=
SUBSTR (v_max_flex_t, INSTR (v_max_flex_t, '.', 1) + 1);
v_acc_flex_t :=
SUBSTR (v_acc_flex_t, INSTR (v_acc_flex_t, '.', 1) + 1);
IF INSTR (v_min_flex_t, '.', 1) = 0
THEN
v_min_segment := v_min_flex_t;
v_max_segment := v_max_flex_t;
v_acc_segment :=
SUBSTR (v_acc_flex_t,
1,
INSTR (v_acc_flex_t, '.', 1) - 1);
v_min_flex_t := NULL;
v_max_flex_t := NULL;
IF v_acc_segment < v_min_segment
OR v_acc_segment > v_max_segment
THEN
RETURN 'N';
END IF;
END IF;
END LOOP;
/*dbms_output.put_line(p_min_flex);
dbms_output.put_line(v_acc_flex);
dbms_output.put_line(p_max_flex);
dbms_output.put_line('------');*/
RETURN 'Y';
EXCEPTION
WHEN OTHERS
THEN
RETURN 'N';
END CHECK_FULL_ACCT;
--------------------------------------------------
-----Ham lay ten dien giai segment tai khoan------
--------------------------------------------------
FUNCTION GET_COA_SEGMENT_DESC (p_ledger_id NUMBER,
p_segment_name VARCHAR2,
p_value VARCHAR2)
RETURN VARCHAR2
IS
-- v_flex_structure_name varchar2(150) := 'GMD OPS CHART OF ACCOUNTS'; --Initial Name of the COA
v_description VARCHAR2 (200);
BEGIN
SELECT ffv.DESCRIPTION
INTO v_description
FROM GL_LEDGERS gll,
FND_ID_FLEX_STRUCTURES_VL str,
FND_ID_FLEX_SEGMENTS_VL seg,
FND_FLEX_VALUE_SETS ffs,
FND_FLEX_VALUES_VL ffv
WHERE gll.chart_of_accounts_id = str.ID_FLEX_NUM
AND str.APPLICATION_ID = seg.APPLICATION_ID
AND str.ID_FLEX_CODE = seg.ID_FLEX_CODE
AND str.ID_FLEX_NUM = seg.ID_FLEX_NUM
AND seg.FLEX_VALUE_SET_ID = ffs.flex_value_set_id
AND ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
-- and str.ID_FLEX_STRUCTURE_CODE = 'ACCOUNTING_FLEXFIELD'
AND str.ID_FLEX_CODE = 'GL#'
AND gll.ledger_id = p_ledger_id
AND seg.APPLICATION_COLUMN_NAME = p_segment_name
AND ffv.FLEX_VALUE = p_value;
RETURN v_description;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_COA_SEGMENT_DESC;
---------------------------------------------------------
-----Ham lay ten dien giai segment category tai san------
---------------------------------------------------------
FUNCTION GET_CAT_SEGMENT_DESC (p_segment_name VARCHAR2,
p_value VARCHAR2,
p_parent_value VARCHAR2)
RETURN VARCHAR2
IS
-- v_flex_structure_name varchar2(150) := 'GMD OPS CHART OF ACCOUNTS'; --Initial Name of the COA
v_description VARCHAR2 (200);
BEGIN
SELECT ffv.DESCRIPTION
INTO v_description
FROM FND_ID_FLEX_STRUCTURES_VL str,
FND_ID_FLEX_SEGMENTS_VL seg,
FND_FLEX_VALUE_SETS ffs,
FND_FLEX_VALUES_VL ffv
WHERE str.APPLICATION_ID = seg.APPLICATION_ID
AND str.ID_FLEX_CODE = seg.ID_FLEX_CODE
AND str.ID_FLEX_NUM = seg.ID_FLEX_NUM
AND seg.FLEX_VALUE_SET_ID = ffs.flex_value_set_id
AND ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
AND str.ID_FLEX_STRUCTURE_CODE = 'CATEGORY_FLEXFIELD'
AND str.ID_FLEX_CODE = 'CAT#'
AND seg.APPLICATION_COLUMN_NAME = p_segment_name
AND ffv.FLEX_VALUE = p_value
AND ( ffv.PARENT_FLEX_VALUE_LOW = p_parent_value
OR p_parent_value IS NULL);
RETURN v_description;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_CAT_SEGMENT_DESC;
---------------------------------------------------------
-----Ham lay ten dien giai segment key tai san------
---------------------------------------------------------
FUNCTION GET_KEY_SEGMENT_DESC (p_segment_name VARCHAR2, p_value VARCHAR2)
RETURN VARCHAR2
IS
-- v_flex_structure_name varchar2(150) := 'GMD OPS CHART OF ACCOUNTS'; --Initial Name of the COA
v_description VARCHAR2 (200);
BEGIN
SELECT ffv.DESCRIPTION
INTO v_description
FROM FND_ID_FLEX_STRUCTURES_VL str,
FND_ID_FLEX_SEGMENTS_VL seg,
FND_FLEX_VALUE_SETS ffs,
FND_FLEX_VALUES_VL ffv
WHERE str.APPLICATION_ID = seg.APPLICATION_ID
AND str.ID_FLEX_CODE = seg.ID_FLEX_CODE
AND str.ID_FLEX_NUM = seg.ID_FLEX_NUM
AND seg.FLEX_VALUE_SET_ID = ffs.flex_value_set_id
AND ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
AND str.ID_FLEX_STRUCTURE_CODE = 'ASSET_KEY_FLEXFIELD'
AND str.ID_FLEX_CODE = 'KEY#'
AND seg.APPLICATION_COLUMN_NAME = p_segment_name
AND ffv.FLEX_VALUE = p_value;
RETURN v_description;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_KEY_SEGMENT_DESC;
---------------------------------------------------------
-----Ham lay ten dien giai segment LOC tai san------
---------------------------------------------------------
FUNCTION GET_LOC_SEGMENT_DESC (p_segment_name VARCHAR2, p_value VARCHAR2)
RETURN VARCHAR2
IS
v_description VARCHAR2 (200);
BEGIN
SELECT ffv.DESCRIPTION
INTO v_description
FROM FND_ID_FLEX_STRUCTURES_VL str,
FND_ID_FLEX_SEGMENTS_VL seg,
FND_FLEX_VALUE_SETS ffs,
FND_FLEX_VALUES_VL ffv
WHERE str.APPLICATION_ID = seg.APPLICATION_ID
AND str.ID_FLEX_CODE = seg.ID_FLEX_CODE
AND str.ID_FLEX_NUM = seg.ID_FLEX_NUM
AND seg.FLEX_VALUE_SET_ID = ffs.flex_value_set_id
AND ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
AND str.ID_FLEX_STRUCTURE_CODE = 'LOCATION_FLEXFIELD'
AND str.ID_FLEX_CODE = 'LOC#'
AND seg.APPLICATION_COLUMN_NAME = p_segment_name
AND ffv.FLEX_VALUE = p_value;
RETURN v_description;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_LOC_SEGMENT_DESC;
------------------------------------------------
-- GET_ORG_NAME --> OU_NAME
-- Tra ve Ten Oganization Unit
------------------------------------------------
FUNCTION OU_NAME (p_org_id NUMBER)
RETURN CHAR
IS
v_name VARCHAR2 (300);
BEGIN
SELECT NVL (a.attribute1, a.name)
INTO v_name
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END OU_NAME;
--------------------------------------
-- GET_ORG_NAME_LONG --> OU_NAME_LONG
--
--------------------------------------
FUNCTION OU_NAME_LONG (p_org_id NUMBER)
RETURN CHAR
IS
v_name VARCHAR2 (300);
BEGIN
SELECT REPLACE (a.ATTRIBUTE5, ':', ' - ')
INTO v_name
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END OU_NAME_LONG;
----------------------------------
---- Ham lay org attribute5 ------
----------------------------------
FUNCTION GET_ORG_ATTR5 (p_org_id NUMBER)
RETURN CHAR
IS
v_name VARCHAR2 (400);
BEGIN
SELECT REPLACE (NVL (a.attribute5, a.name), ':', ' - ')
INTO v_name
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN UPPER (v_name);
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_ORG_ATTR5;
----------------------------------
---- Ham lay org attribute11 ------
----------------------------------
FUNCTION GET_ORG_ATTR11 (p_org_id NUMBER)
RETURN CHAR
IS
v_name VARCHAR2 (500);
/*
Ham lay ra don vi quan ly cua mot OU
*/
BEGIN
SELECT a.attribute11
INTO v_name
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN UPPER (v_name);
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_ORG_ATTR11;
--------------------------------------
-- GET_ORG_ADDRESS --> OU_ADDRESS
-- Tra ve dia chi Organization Unit
--------------------------------------
FUNCTION OU_ADDRESS (p_org_id NUMBER)
RETURN CHAR
IS
v_add VARCHAR2 (300);
BEGIN
--select nvl(a.attribute4, a.address_line_1 || ', ' || a.town_or_city)--Danh edit 09/06/25 (remove dấu phẩy theo yêu cầu Quế Trâm PM1
SELECT NVL (
a.attribute4,
a.address_line_1
|| CASE
WHEN a.town_or_city IS NOT NULL
THEN
', ' || a.town_or_city
ELSE
NULL
END) --Danh edit 09/06/25 (remove dấu phẩy theo yêu cầu Quế Trâm PM1
INTO v_add
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_add;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END OU_ADDRESS;
------------------------------
-----Ham lay org phone------
-- GET_ORG_PHONE --> OU_TELEPHONE
------------------------------
FUNCTION OU_TELEPHONE (p_org_id NUMBER)
RETURN VARCHAR2
IS
v_phone VARCHAR2 (300);
/*
Ham lay ra attribute1 cua mot OU
*/
BEGIN
SELECT a.telephone_number_1
INTO v_phone
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_phone;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END OU_TELEPHONE;
------------------------------
-----Ham lay org Fax------
-- get_org_fax --> OU_FAX
------------------------------
FUNCTION OU_FAX (p_org_id NUMBER)
RETURN VARCHAR2
IS
v_fax VARCHAR2 (300);
/*
Ham lay ra attribute2 cua mot OU
*/
BEGIN
SELECT a.telephone_number_2
INTO v_fax
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_fax;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END OU_FAX;
FUNCTION OU_WEBSITE (p_org_id NUMBER)
RETURN VARCHAR2
IS
results VARCHAR2 (200);
BEGIN
SELECT oun.attribute3
INTO results
--INTO l_ten_cty, :dia_chi, :dien_thoai, :fax, :mst, :website
FROM HR_ORGANIZATION_UNITS_V oun
WHERE oun.organization_id = p_org_id;
RETURN results;
END;
FUNCTION OU_TAXCODE (p_org_id NUMBER)
RETURN VARCHAR2
IS
results VARCHAR2 (200);
BEGIN
SELECT MAX (oun.attribute2)
INTO results
--INTO l_ten_cty, :dia_chi, :dien_thoai, :fax, :mst, :website
FROM HR_ORGANIZATION_UNITS_V oun
WHERE oun.organization_id = p_org_id;
RETURN results;
END;
-- GetCountryOfOu
FUNCTION OU_COUNTRY (p_org_id NUMBER)
RETURN VARCHAR2
IS
v_result VARCHAR2 (200);
BEGIN
SELECT ct.territory_short_name
INTO v_result
FROM hr_organization_units_v a, fnd_territories_vl ct
WHERE 1 = 1
AND a.country = ct.territory_code
AND organization_id = p_org_id
AND ROWNUM = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END OU_COUNTRY;
------------------------------
-----Ham lay entity name------
------------------------------
FUNCTION GET_ENTITY_NAME (p_org_id NUMBER)
RETURN CHAR
IS
v_name VARCHAR2 (300);
BEGIN
SELECT UPPER (a.name)
INTO v_name
FROM xxff_legal_v a, hr_operating_units b
WHERE a.legal_entity_id = b.default_legal_context_id
AND b.organization_id = p_org_id;
RETURN v_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_ENTITY_NAME;
---------------------------------
-----Ham lay entity address------
---------------------------------
FUNCTION GET_ENTITY_ADDRESS (p_org_id NUMBER)
RETURN CHAR
IS
v_result VARCHAR2 (300);
BEGIN
SELECT a.address_line_1 || ', ' || a.town_or_city
INTO v_result
FROM xxff_legal_v a, hr_operating_units b
WHERE a.legal_entity_id = b.default_legal_context_id
AND b.organization_id = p_org_id;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_ENTITY_ADDRESS;
-- Muc dich dung de convert kieu ngay sang chuoi
-------------------------------------------------------
PROCEDURE PARA_DATE_CONVERSION (DATE_PARA IN OUT VARCHAR2)
IS
v_date VARCHAR2 (11);
BEGIN
SELECT TO_CHAR (TO_DATE (SUBSTR (DATE_PARA, 1, 10), 'RRRR-MM-DD'),
'DD')
|| '-'
|| TO_CHAR (TO_DATE (SUBSTR (DATE_PARA, 1, 10), 'RRRR-MM-DD'),
'MON')
|| '-'
|| TO_CHAR (TO_DATE (SUBSTR (DATE_PARA, 1, 10), 'RRRR-MM-DD'),
'RRRR')
--- || substr(DATE_PARA, 11,20)
INTO v_date
FROM DUAL;
IF v_date = '--'
THEN
v_date := NULL;
END IF;
DATE_PARA := v_date;
END PARA_DATE_CONVERSION;
---------------------------------------------------------------------
--Cat chuoi thanh 2 doan VN va EN, doan dau la VND, doan sau la ENG--
---------------------------------------------------------------------
FUNCTION TRANSLATE (p_string VARCHAR2, v_language VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF INSTR (p_string, '|') = 0
THEN
RETURN p_string;
ELSE
IF v_language = 'VN'
THEN
RETURN TRIM (SUBSTR (p_string, 1, INSTR (p_string, '|') - 1));
ELSIF v_language = 'EN'
THEN
RETURN TRIM (SUBSTR (p_string, INSTR (p_string, '|') + 1));
ELSE
RETURN p_string;
END IF;
END IF;
END TRANSLATE;
--------------------------------------------------
---Ham lay category_id (tam thoi chua dung duoc)--
--------------------------------------------------
/*function GET_FA_CATEGORY_ID(p_asset_id number,p_date date) return number is
v_category_id number;
begin
select fah1.category_id
into v_category_id
from fa_asset_history fah1
where fah1.transaction_header_id_in = (select max(fah.transaction_header_id_in)
from fa_asset_history fah
,fa_transaction_headers fth
where fah.asset_id = fth.asset_id
and fah.transaction_header_id_in = fth.transaction_header_id
and fah.asset_id = fah1.asset_id
and fah.asset_id = p_asset_id
and fth.transaction_date_entered <= p_date or p_date is null);
-- and fah1.asset_id = p_asset_id;
return v_category_id;
exception
when others then
return null;
end GET_FA_CATEGORY_ID;*/
-----------------------------------------------
---Ham lay tai khoan NO_AR cua phieu thu,chi---
-----------------------------------------------
FUNCTION TK_NO_AR (p_cash_receipt_id IN NUMBER)
RETURN VARCHAR2
IS
v_tk VARCHAR2 (100);
BEGIN
FOR rec
IN ( SELECT DISTINCT gcc.segment3
FROM ar_cash_receipt_history_all crh,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE crh.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND crh.cash_receipt_id = p_cash_receipt_id
GROUP BY xal.accounting_class_code, gcc.segment3
HAVING SUM (NVL (xal.entered_dr, 0))
- SUM (NVL (xal.entered_cr, 0)) >
0)
LOOP
IF v_tk IS NULL
THEN
v_tk := rec.segment3;
ELSE
v_tk := v_tk || ',' || rec.segment3;
END IF;
END LOOP;
RETURN v_tk;
END TK_NO_AR;
-----------------------------------------------
---Ham lay tai khoan CO_AR cua phieu thu,chi---
-----------------------------------------------
FUNCTION TK_CO_AR (p_cash_receipt_id IN NUMBER)
RETURN VARCHAR2
IS
v_tk VARCHAR2 (100);
BEGIN
FOR rec
IN ( SELECT DISTINCT gcc.segment3
FROM ar_receivable_applications_all rap,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE rap.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND rap.cash_receipt_id = p_cash_receipt_id
GROUP BY xal.accounting_class_code, gcc.segment3
HAVING SUM (NVL (xal.entered_cr, 0))
- SUM (NVL (xal.entered_dr, 0)) >
0
UNION
SELECT DISTINCT gcc.segment3
FROM ar_misc_cash_distributions_all mcd,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE mcd.event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND mcd.cash_receipt_id = p_cash_receipt_id
GROUP BY xal.accounting_class_code, gcc.segment3
HAVING SUM (NVL (xal.entered_cr, 0))
- SUM (NVL (xal.entered_dr, 0)) >
0)
LOOP
IF v_tk IS NULL
THEN
v_tk := rec.segment3;
ELSE
v_tk := v_tk || ',' || rec.segment3;
END IF;
END LOOP;
RETURN v_tk;
END TK_CO_AR;
-----------------------------------------------
---Ham lay tai khoan NO_AP cua phieu thu,chi---
-----------------------------------------------
FUNCTION TK_NO_AP (p_check_id IN NUMBER)
RETURN VARCHAR2
IS
v_tk VARCHAR2 (100);
BEGIN
FOR rec
IN ( SELECT DISTINCT gcc.segment3
FROM ap_invoice_payments_all aip,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE aip.accounting_event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND aip.check_id = p_check_id
GROUP BY xal.accounting_class_code, gcc.segment3
HAVING SUM (NVL (xal.entered_dr, 0))
- SUM (NVL (xal.entered_cr, 0)) >
0)
LOOP
IF v_tk IS NULL
THEN
v_tk := rec.segment3;
ELSE
v_tk := v_tk || ',' || rec.segment3;
END IF;
END LOOP;
RETURN v_tk;
END TK_NO_AP;
-----------------------------------------------
---Ham lay tai khoan CO_AP cua phieu thu,chi---
-----------------------------------------------
FUNCTION TK_CO_AP (p_check_id IN NUMBER)
RETURN VARCHAR2
IS
v_tk VARCHAR2 (100);
BEGIN
FOR rec
IN ( SELECT DISTINCT gcc.segment3
FROM ap_payment_history_all aph,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE aph.accounting_event_id = xah.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND aph.check_id = p_check_id
GROUP BY xal.accounting_class_code, gcc.segment3
HAVING SUM (NVL (xal.entered_cr, 0))
- SUM (NVL (xal.entered_dr, 0)) >
0)
LOOP
IF v_tk IS NULL
THEN
v_tk := rec.segment3;
ELSE
v_tk := v_tk || ',' || rec.segment3;
END IF;
END LOOP;
RETURN v_tk;
END TK_CO_AP;
------------------------
--Ham doc so thanh chu--
------------------------
FUNCTION no_chr1 (n IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
v_str VARCHAR2 (10);
BEGIN
IF n = '1'
THEN
v_str := ' một';
ELSIF n = '2'
THEN
v_str := ' hai';
ELSIF n = '3'
THEN
v_str := ' ba';
ELSIF n = '4'
THEN
v_str := ' bốn';
ELSIF n = '5'
THEN
v_str := ' năm';
ELSIF n = '6'
THEN
v_str := ' sáu';
ELSIF n = '7'
THEN
v_str := ' bảy';
ELSIF n = '8'
THEN
v_str := ' tám';
ELSIF n = '9'
THEN
v_str := ' chín';
ELSIF n = '0'
THEN
v_str := ' không';
END IF;
RETURN v_str;
END;
END no_chr1;
FUNCTION no_chr3 (n IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
v_length NUMBER (1) := LENGTH (n);
v_tram VARCHAR2 (15) := '';
v_chuc VARCHAR2 (20) := '';
v_dv VARCHAR2 (15) := '';
n_tram VARCHAR2 (1) := '';
n_chuc VARCHAR2 (1) := '';
n_dv VARCHAR2 (1) := '';
v_str VARCHAR2 (50) := '';
n_tr NUMBER (1) := 0;
BEGIN
IF n = '000' OR v_length = 0
THEN
RETURN '';
END IF;
IF v_length = 3
THEN
n_tram := SUBSTR (n, 1, 1);
v_tram := no_chr1 (n_tram) || ' trăm';
n_tr := 1;
END IF;
IF v_length >= 2
THEN
n_chuc := SUBSTR (n, n_tr + 1, 1);
IF n_chuc <= '9' AND n_chuc >= '2'
THEN
v_chuc := no_chr1 (n_chuc) || ' mươi';
END IF;
IF n_chuc = '1'
THEN
v_chuc := ' mười'; --' muoi';
END IF;
IF n_chuc = '0'
THEN
v_chuc := ' lẻ';
END IF;
END IF;
n_dv := SUBSTR (n, v_length, 1);
IF n_dv = '1' AND n_chuc >= '2'
THEN
v_dv := ' mốt';
ELSIF n_dv = '5' AND n_chuc >= '1'
THEN
v_dv := ' lăm';
ELSIF n_dv <> '0'
THEN
v_dv := no_chr1 (n_dv);
END IF;
IF n_chuc = '0' AND n_dv = '0'
THEN
v_chuc := '';
v_dv := '';
END IF;
v_str := v_tram || v_chuc || v_dv;
RETURN v_str;
END;
END no_chr3;
FUNCTION no_chr9 (n IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
v_length NUMBER (2) := LENGTH (n);
v_trieu VARCHAR2 (60) := '';
v_nghin VARCHAR2 (60) := '';
v_dv VARCHAR2 (50) := '';
n_nghin VARCHAR2 (3) := '';
n_trieu VARCHAR2 (3) := '';
n_dv VARCHAR2 (3) := '';
n_tr NUMBER := 0;
n_ng NUMBER := 0;
v_str VARCHAR2 (170);
BEGIN
IF n = '000000000'
THEN
RETURN '';
END IF;
IF v_length >= 7
THEN
n_trieu := SUBSTR (n, 1, v_length - 6);
IF n_trieu = '000'
THEN
v_trieu := '';
ELSE
v_trieu := no_chr3 (n_trieu) || ' triệu';
END IF;
n_tr := LENGTH (n_trieu);
END IF;
IF v_length >= 4
THEN
n_nghin := SUBSTR (n, n_tr + 1, v_length - n_tr - 3);
IF n_nghin = '000'
THEN
v_nghin := '';
ELSE
v_nghin := no_chr3 (n_nghin) || ' nghìn';
END IF;
n_ng := LENGTH (n_nghin);
END IF;
n_dv := SUBSTR (n, n_tr + n_ng + 1, v_length - n_tr - n_ng);
v_dv := no_chr3 (n_dv);
v_str := v_trieu || v_nghin || v_dv;
RETURN v_str;
END;
END no_chr9;
FUNCTION n_no_chr (n_ts IN NUMBER, curcode IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
--n_str VARCHAR2 (1000) := TO_CHAR (n_ts);
n_str_dv VARCHAR2 (18) := '';
--n_str_tp VARCHAR2 (5) := '';
v_ty VARCHAR2 (200) := '';
v_dv VARCHAR2 (170) := '';
v_tp VARCHAR2 (200) := '';
n_ty VARCHAR2 (9) := '';
n_dv VARCHAR2 (9) := '';
v_str VARCHAR2 (250) := '';
v_length NUMBER := 0;
n_tyn NUMBER := 0;
v_str2 VARCHAR2 (250) := '';
chu_hoa VARCHAR2 (10) := '';
unit VARCHAR2 (50) := '';
ydecm VARCHAR2 (20) := '';
n_country VARCHAR2 (20) := '';
--i NUMBER;
n NUMBER;
dau BOOLEAN := TRUE;
tg NUMBER;
BEGIN
IF (n_ts IS NULL OR n_ts = 0)
THEN
RETURN NULL;
END IF;
IF (curcode = 'VND') OR (curcode IS NULL)
THEN
unit := 'đồng.';
ydecm := '';
n := ROUND (n_ts, 0);
ELSIF (curcode = 'USD')
THEN
unit := 'dollars';
ydecm := 'cents';
n := n_ts;
n_country := ' Mỹ.';
ELSE
unit := curcode;
n := n_ts;
END IF;
IF (n < 0)
THEN
dau := FALSE;
n := -n;
END IF;
n_str_dv := TO_CHAR (TRUNC (n));
v_length := LENGTH (n_str_dv);
IF v_length >= 10
THEN
n_ty := SUBSTR (n_str_dv, 1, v_length - 9);
v_ty := no_chr9 (n_ty) || ' tỷ';
n_tyn := LENGTH (n_ty);
END IF;
IF TRUNC (n) = 0
THEN
v_dv := ' không';
ELSE
n_dv := SUBSTR (n_str_dv, n_tyn + 1, v_length - n_tyn);
v_dv := no_chr9 (n_dv);
END IF;
IF (n - TRUNC (n)) <> 0
THEN
-- n_str_tp:=SUBSTR(n_str,v_length+2);
tg := (n - TRUNC (n)) * 100;
IF tg < 10
THEN
v_tp := no_chr1 (0) || no_chr9 (tg);
-- If (to_number(n_str_tp) <10) then--(SUBSTR(n_str_tp, 1, 1)='0') Then
-- v_tp:=no_chr1(SUBSTR(n_str_tp, 1, 1))||' '||no_chr9(SUBSTR(n_str_tp,2));
ELSE
-- v_tp:=no_chr9(rpad(SUBSTR(n_str_tp,1),2,'0'));
v_tp := no_chr9 (tg);
END IF;
v_str :=
v_ty
|| v_dv
|| ' '
|| unit
|| ' và'
|| v_tp
|| ' '
|| ydecm
|| n_country;
ELSE
v_str := v_ty || v_dv || ' ' || unit || n_country;
END IF;
--v_length := LENGTH (v_str);
chu_hoa := SUBSTR (v_str, 2, 1);
v_str2 := SUBSTR (v_str, 3);
v_str := UPPER (chu_hoa) || v_str2;
IF (dau = FALSE)
THEN
v_str := 'âm ' || v_str;
END IF;
RETURN v_str;
END;
END n_no_chr;
FUNCTION NO_ENG_CHR1 (n IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
v_str VARCHAR2 (10);
BEGIN
IF n = '1'
THEN
v_str := ' one';
ELSIF n = '2'
THEN
v_str := ' two';
ELSIF n = '3'
THEN
v_str := ' three';
ELSIF n = '4'
THEN
v_str := ' four';
ELSIF n = '5'
THEN
v_str := ' five';
ELSIF n = '6'
THEN
v_str := ' six';
ELSIF n = '7'
THEN
v_str := ' seven';
ELSIF n = '8'
THEN
v_str := ' eight';
ELSIF n = '9'
THEN
v_str := ' nice';
ELSIF n = '0'
THEN
v_str := ' zero';
END IF;
RETURN v_str;
END;
END NO_ENG_CHR1;
FUNCTION NO_ENG_CHR3 (n IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
v_length NUMBER (1) := LENGTH (n);
v_tram VARCHAR2 (15) := '';
v_chuc VARCHAR2 (20) := '';
v_dv VARCHAR2 (15) := '';
n_tram VARCHAR2 (1) := '';
n_chuc VARCHAR2 (1) := '';
n_dv VARCHAR2 (1) := '';
v_str VARCHAR2 (50) := '';
n_tr NUMBER (1) := 0;
BEGIN
IF n = '000' OR v_length = 0
THEN
RETURN '';
END IF;
IF v_length = 3
THEN
n_tram := SUBSTR (n, 1, 1);
v_tram := no_eng_chr1 (n_tram) || ' hundred';
n_tr := 1;
END IF;
IF v_length >= 2
THEN
n_chuc := SUBSTR (n, n_tr + 1, 1);
IF n_chuc <= '9' AND n_chuc >= '2'
THEN
IF n_chuc = '2'
THEN
v_chuc := ' twenty';
END IF;
IF n_chuc = '3'
THEN
v_chuc := ' thirty';
END IF;
IF n_chuc = '5'
THEN
v_chuc := ' fifty';
END IF;
IF (n_chuc = '4' OR (n_chuc <= '9' AND n_chuc >= '6'))
THEN
v_chuc := no_eng_chr1 (n_chuc) || 'ty';
END IF;
END IF;
END IF;
n_dv := SUBSTR (n, v_length, 1);
IF n_chuc = '1'
THEN
IF n_dv = '0'
THEN
v_chuc := ' ten';
END IF;
IF n_dv = '1'
THEN
v_chuc := ' eleven';
END IF;
IF n_dv = '2'
THEN
v_chuc := ' twelve';
END IF;
IF n_dv = '3'
THEN
v_chuc := ' thirteen';
END IF;
IF n_dv = '5'
THEN
v_chuc := ' fifteen';
END IF;
IF (n_dv = '4' OR (n_dv <= '9' AND n_dv >= '6'))
THEN
v_chuc := no_eng_chr1 (n_dv) || 'teen';
END IF;
END IF;
IF n_dv >= '1' AND n_chuc >= '2'
THEN
v_dv := no_eng_chr1 (n_dv);
/*ELSIF n_dv <> '0'
THEN
v_dv := no_eng_chr1 (n_dv);*/
END IF;
IF n_chuc = '0' AND n_dv = '0'
THEN
v_chuc := '';
v_dv := '';
END IF;
IF v_length = 1
THEN
v_dv := no_eng_chr1 (n_dv);
END IF;
/*n_dv := SUBSTR (n, v_length, 1);
IF (n_dv >= '1' and n_dv <= '9') AND n_chuc >= '2'
THEN
v_dv := no_eng_chr1 (n_dv);
END IF;
IF n_chuc = '0' AND n_dv = '0'
THEN
v_chuc := '';
v_dv := '';
END IF;*/
v_str := v_tram || v_chuc || v_dv;
RETURN v_str;
END;
END NO_ENG_CHR3;
FUNCTION NO_ENG_CHR9 (n IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
v_length NUMBER (2) := LENGTH (n);
v_trieu VARCHAR2 (60) := '';
v_nghin VARCHAR2 (60) := '';
v_dv VARCHAR2 (50) := '';
n_nghin VARCHAR2 (3) := '';
n_trieu VARCHAR2 (3) := '';
n_dv VARCHAR2 (3) := '';
n_tr NUMBER := 0;
n_ng NUMBER := 0;
v_str VARCHAR2 (170);
BEGIN
IF n = '000000000'
THEN
RETURN '';
END IF;
IF v_length >= 7
THEN
n_trieu := SUBSTR (n, 1, v_length - 6);
IF n_trieu = '000'
THEN
v_trieu := '';
ELSE
v_trieu := no_eng_chr3 (n_trieu) || ' million';
END IF;
n_tr := LENGTH (n_trieu);
END IF;
IF v_length >= 4
THEN
n_nghin := SUBSTR (n, n_tr + 1, v_length - n_tr - 3);
IF n_nghin = '000'
THEN
v_nghin := '';
ELSE
v_nghin := no_eng_chr3 (n_nghin) || ' thousand';
END IF;
n_ng := LENGTH (n_nghin);
END IF;
n_dv := SUBSTR (n, n_tr + n_ng + 1, v_length - n_tr - n_ng);
v_dv := no_eng_chr3 (n_dv);
v_str := v_trieu || v_nghin || v_dv;
RETURN v_str;
END;
END NO_ENG_CHR9;
FUNCTION N_NO_ENG_CHR (n_ts IN NUMBER, curcode IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DECLARE
/* n_str VARCHAR2 (21) := TO_CHAR (n_ts);*/
n_str_dv VARCHAR2 (18) := '';
/*n_str_tp VARCHAR2 (5) := '';*/
v_ty VARCHAR2 (200) := '';
v_dv VARCHAR2 (170) := '';
v_tp VARCHAR2 (50) := '';
n_ty VARCHAR2 (9) := '';
n_dv VARCHAR2 (9) := '';
v_str VARCHAR2 (250) := '';
v_length NUMBER := 0;
n_tyn NUMBER := 0;
v_str2 VARCHAR2 (250) := '';
chu_hoa VARCHAR2 (10) := '';
unit VARCHAR2 (20) := '';
ydecm VARCHAR2 (20) := '';
/* i NUMBER;*/
n NUMBER;
/* dau BOOLEAN := TRUE;*/
tg NUMBER;
BEGIN
IF (n_ts IS NULL OR n_ts = 0)
THEN
RETURN NULL;
END IF;
IF (curcode = 'USD')
THEN
unit := 'US dollars';
ydecm := 'cents./';
n := n_ts;
ELSE
unit := curcode;
n := n_ts;
END IF;
/*IF (n < 0)
THEN
dau := FALSE;
n := -n;
END IF;*/
n_str_dv := TO_CHAR (TRUNC (n));
v_length := LENGTH (n_str_dv);
IF v_length >= 10
THEN
n_ty := SUBSTR (n_str_dv, 1, v_length - 9);
v_ty := no_eng_chr9 (n_ty) || ' billion';
n_tyn := LENGTH (n_ty);
END IF;
IF TRUNC (n) = 0
THEN
v_dv := ' zero';
ELSE
n_dv := SUBSTR (n_str_dv, n_tyn + 1, v_length - n_tyn);
v_dv := no_eng_chr9 (n_dv);
END IF;
IF (n - TRUNC (n)) <> 0
THEN
-- n_str_tp:=SUBSTR(n_str,v_length+2);
tg := (n - TRUNC (n)) * 100;
IF tg < 10
THEN
v_tp := no_eng_chr1 (0) || no_eng_chr9 (tg);
-- If (to_number(n_str_tp) <10) then--(SUBSTR(n_str_tp, 1, 1)='0') Then
-- v_tp:=no_chr1(SUBSTR(n_str_tp, 1, 1))||' '||no_chr9(SUBSTR(n_str_tp,2));
ELSE
-- v_tp:=no_chr9(rpad(SUBSTR(n_str_tp,1),2,'0'));
v_tp := no_eng_chr9 (tg);
END IF;
v_str :=
v_ty
|| v_dv
|| ' '
|| unit
|| ' and '
|| v_tp
|| ' '
|| ydecm;
ELSE
v_str := v_ty || v_dv || ' ' || unit;
END IF;
--v_length := LENGTH (v_str);
chu_hoa := SUBSTR (v_str, 2, 1);
v_str2 := SUBSTR (v_str, 3);
v_str := UPPER (chu_hoa) || v_str2;
/*IF (dau = FALSE)
THEN
v_str := 'Am '
|| v_str;
END IF;*/
RETURN v_str;
END;
END N_NO_ENG_CHR;
------------------------------
-----Lay khau hoa luy ke------
------------------------------
FUNCTION KHAUHAO_LUYKE (p_asset_id IN NUMBER, p_date IN DATE)
RETURN NUMBER
IS
v_deprn_reserve NUMBER;
BEGIN
SELECT fds1.deprn_reserve
INTO v_deprn_reserve
FROM ( SELECT /*nvl(*/
fds.deprn_reserve /* + fds.deprn_adjustment_amount,0)*/
deprn_reserve
FROM fa_deprn_summary fds, fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
AND fds.asset_id = p_asset_id
AND fdp.calendar_period_close_date <= p_date
ORDER BY fdp.calendar_period_close_date DESC) fds1
WHERE ROWNUM = 1;
RETURN v_deprn_reserve;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END KHAUHAO_LUYKE;
------- Khau hao trong ky tang + giam
FUNCTION KHAUHAO_TRONGKY_TANG_GIAM (v_asset_id IN NUMBER,
v_period_counter_from IN NUMBER,
v_period_counter_to IN NUMBER,
v_source_type_code IN VARCHAR2)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
IF ('GIAM' = v_source_type_code)
THEN
SELECT d.deprn_reserve
INTO v_result
FROM fa_deprn_detail d
WHERE d.period_counter =
(SELECT DISTINCT TO_NUMBER (a.period_counter_adjusted) - 1
FROM fa_adjustments a
WHERE a.source_type_code = 'RETIREMENT'
AND a.adjustment_type = 'COST'
AND a.asset_id = v_asset_id
AND d.asset_id = a.asset_id);
ELSE
SELECT NVL (SUM (d.deprn_adjustment_amount), 0)
INTO v_result
FROM fa_deprn_detail d
WHERE d.period_counter BETWEEN v_period_counter_from
AND v_period_counter_to
AND d.asset_id = v_asset_id;
END IF;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END KHAUHAO_TRONGKY_TANG_GIAM;
--- --- Lay khau hao trong ky
FUNCTION KHAUHAO_TRONGKY (p_asset_id IN NUMBER,
p_period_counter_from IN NUMBER,
p_period_counter_to IN NUMBER)
RETURN NUMBER
IS
v_deprn_amount NUMBER;
BEGIN
SELECT fds1.deprn_amount
INTO v_deprn_amount
FROM ( SELECT SUM (fds.deprn_amount) deprn_amount
FROM fa_deprn_summary fds, fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
AND fds.asset_id = p_asset_id
--and fdp.period_counter = p_period_counter
AND fdp.period_counter BETWEEN p_period_counter_from
AND p_period_counter_to
ORDER BY fdp.period_counter DESC) fds1
WHERE ROWNUM = 1;
RETURN v_deprn_amount;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END KHAUHAO_TRONGKY;
----------------------------------------------
-----Lay khau hoa luy ke tong hop tu con------
----------------------------------------------
FUNCTION KHAUHAO_LUYKE_CHA (p_asset_id IN NUMBER, p_date IN DATE)
RETURN NUMBER
IS
tg NUMBER := 0;
CURSOR c_asset IS
SELECT fab.asset_id
FROM fa_additions_b fab
WHERE fab.parent_asset_id = p_asset_id;
rec_asset c_asset%ROWTYPE;
BEGIN
OPEN c_asset;
LOOP
FETCH c_asset INTO rec_asset;
EXIT WHEN c_asset%NOTFOUND;
tg := tg + NVL (KHAUHAO_LUYKE (rec_asset.asset_id, p_date), 0);
END LOOP;
CLOSE c_asset;
RETURN (tg);
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END KHAUHAO_LUYKE_CHA;
---------------------------
-----Lay nguyen gia--------
---------------------------
FUNCTION NGUYEN_GIA (p_asset_id IN NUMBER, p_date IN DATE)
RETURN NUMBER
IS
v_nguyen_gia NUMBER;
BEGIN
SELECT fb.recoverable_cost
INTO v_nguyen_gia
FROM fa_books fb
WHERE fb.transaction_header_id_in =
(SELECT MAX (fb1.transaction_header_id_in)
FROM fa_books fb1, fa_transaction_headers fth
WHERE fb1.asset_id = fth.asset_id
AND fb1.transaction_header_id_in =
fth.transaction_header_id
AND fb1.asset_id = fb.asset_id
AND fb1.asset_id = p_asset_id
AND fth.transaction_date_entered <= p_date)
AND fb.transaction_header_id_in >=
(SELECT MIN (fah.transaction_header_id_in)
FROM fa_asset_history fah
WHERE fah.asset_type = 'CAPITALIZED'
AND fah.asset_id = fb.asset_id);
RETURN v_nguyen_gia;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END NGUYEN_GIA;
-----------------------------------------
-----Lay nguyen gia tong hop tu con------
-----------------------------------------
FUNCTION NGUYEN_GIA_CHA (p_asset_id IN NUMBER, p_date IN DATE)
RETURN NUMBER
IS
tg NUMBER := 0;
CURSOR c_asset IS
SELECT fab.asset_id
FROM fa_additions_b fab
WHERE fab.parent_asset_id = p_asset_id;
rec_asset c_asset%ROWTYPE;
BEGIN
OPEN c_asset;
LOOP
FETCH c_asset INTO rec_asset;
EXIT WHEN c_asset%NOTFOUND;
tg := tg + NVL (NGUYEN_GIA (rec_asset.asset_id, p_date), 0);
END LOOP;
CLOSE c_asset;
RETURN (tg);
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END NGUYEN_GIA_CHA;
------------
FUNCTION CHECK_RETIREMENT_ASSET (v_so_taisan IN VARCHAR2,
v_asset_id IN NUMBER,
v_ngay IN DATE)
RETURN NUMBER
IS
v_kt VARCHAR2 (200);
BEGIN
SELECT MAX (fab.asset_id)
INTO v_kt
FROM fa_additions_b fab,
fa_retirements far,
fa_transaction_headers fth
WHERE far.asset_id = fab.asset_id
AND fth.asset_id = fab.asset_id
AND (fth.book_type_code = v_so_taisan OR v_so_taisan IS NULL)
AND far.status = 'PROCESSED'
AND fth.transaction_type_code = 'FULL RETIREMENT'
AND fth.transaction_header_id =
(SELECT MAX (fth1.transaction_header_id)
FROM fa_transaction_headers fth1
WHERE fth1.asset_id = fab.asset_id
AND fth1.book_type_code = v_so_taisan)
AND fab.asset_id = v_asset_id
AND (TRUNC (far.date_retired) <= v_ngay);
IF (v_kt IS NOT NULL)
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END CHECK_RETIREMENT_ASSET;
------------
FUNCTION CHECK_DATE_ADDITION_ASSET (v_so_taisan IN VARCHAR2,
v_asset_id IN NUMBER,
v_from_date IN DATE,
v_to_date IN DATE)
RETURN DATE
IS
v_kt DATE;
BEGIN
SELECT fth.TRANSACTION_DATE_ENTERED
INTO v_kt
FROM fa_additions_v fab,
(SELECT fab2.recoverable_cost,
fab2.prorate_date,
fab2.transaction_header_id_in,
fab2.asset_id,
fab2.cost,
fab2.date_placed_in_service,
fab2.life_in_months,
fab2.book_type_code,
fab2.date_ineffective
FROM fa_books fab2
WHERE fab2.transaction_header_id_in =
( SELECT MAX (fab1.transaction_header_id_in)
FROM fa_books fab1, fa_transaction_headers fth
WHERE fab1.asset_id = fth.asset_id
AND fab1.transaction_header_id_in =
fth.transaction_header_id
AND fab1.asset_id = fab2.asset_id
--and fth.transaction_date_entered >= v_from_date
AND fth.transaction_date_entered <= v_to_date
AND fth.transaction_type_code NOT LIKE 'VOID%'
GROUP BY fab1.asset_id)) fb,
fa_transaction_headers fth
WHERE 1 = 1
AND fb.asset_id = fab.asset_id
AND fth.transaction_header_id = fb.transaction_header_id_in
AND (fb.book_type_code = v_so_taisan)
AND fb.asset_id = v_asset_id -- 20001290
GROUP BY TO_DATE (fab.ATTRIBUTE7, 'RRRR/MM/DD HH24:MI:SS'),
fth.TRANSACTION_DATE_ENTERED;
IF (v_kt IS NOT NULL)
THEN
RETURN v_kt;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END CHECK_DATE_ADDITION_ASSET;
----------
FUNCTION TK_CO_FA (v_Asset_ID IN NUMBER)
RETURN VARCHAR2
IS
v_tk VARCHAR2 (100);
BEGIN
FOR acc
IN (SELECT TK
INTO v_tk
-- TH1: Tai san do tu AP chuyen sang thi lay ben AP
FROM (SELECT DISTINCT gcc.segment3 TK
FROM fa_asset_invoices fai,
gl_code_combinations gcc,
ap_invoices_all aia,
fa_additions_b fad
WHERE fai.invoice_id = aia.invoice_id
AND gcc.segment3 LIKE '33%'
AND aia.accts_pay_code_combination_id =
gcc.code_combination_id
AND fad.asset_id = fai.asset_id
AND fad.asset_id = v_Asset_ID
-- TH2: Tai san nhap thu cong thi lay ben FA asset clearing cost
UNION
SELECT DISTINCT fcb.asset_clearing_acct TK
FROM fa_additions_b fad, fa_category_books fcb
WHERE fad.asset_category_id = fcb.category_id
AND fad.asset_id = v_Asset_ID))
LOOP
IF v_tk IS NULL
THEN
v_tk := acc.TK;
ELSE
v_tk := v_tk || ',' || acc.TK;
END IF;
END LOOP;
RETURN v_tk;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END TK_CO_FA;
FUNCTION TK_NO_FA (v_Asset_ID IN NUMBER)
RETURN VARCHAR2
IS
v_tk VARCHAR2 (100);
BEGIN
FOR acc
IN (SELECT DISTINCT fcb.asset_cost_acct
INTO v_tk
FROM fa_additions_b fad, fa_category_books fcb
WHERE fad.asset_category_id = fcb.category_id
AND fad.asset_id = v_Asset_ID)
LOOP
IF v_tk IS NULL
THEN
v_tk := acc.asset_cost_acct;
ELSE
v_tk := v_tk || ',' || acc.asset_cost_acct;
END IF;
END LOOP;
RETURN v_tk;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END TK_NO_FA;
FUNCTION ASSET_TYPE (p_asset_id NUMBER, p_date DATE)
RETURN VARCHAR2
IS
v_asset_type VARCHAR2 (100);
BEGIN
SELECT fah1.asset_type
INTO v_asset_type
FROM fa_asset_history fah1
WHERE fah1.asset_id = p_asset_id
AND fah1.transaction_header_id_in =
(SELECT MAX (fb.transaction_header_id_in)
FROM fa_books fb, fa_asset_history fah
WHERE fb.asset_id = fah.asset_id
AND fb.transaction_header_id_in =
fah.transaction_header_id_in
AND fb.asset_id = p_asset_id
AND fb.date_placed_in_service <= p_date);
RETURN v_asset_type;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END ASSET_TYPE;
FUNCTION GET_AMOUNT_TK_138890 (v_report_line_id IN NUMBER)
RETURN NUMBER
IS
v_amount NUMBER;
BEGIN
SELECT SUM (NVL (erl.amount, 0))
INTO v_amount
FROM AP_EXPENSE_REPORT_HEADERS_ALL ERH,
Ap_Expense_Report_Lines_All ERL,
gl_code_combinations gcc
WHERE erh.report_header_id = erl.report_header_id
AND gcc.code_combination_id = erl.code_combination_id
AND gcc.segment4 LIKE 'E%'
AND gcc.segment3 LIKE '138%'
AND erl.report_line_id = v_report_line_id;
RETURN v_amount;
END GET_AMOUNT_TK_138890;
FUNCTION get_doc_number (
p_je_source gl_je_headers.je_source%TYPE,
p_header_id xla_ae_headers.ae_header_id%TYPE,
p_je_category gl_je_headers.je_category%TYPE,
default_val gl_je_headers.doc_sequence_value%TYPE)
RETURN VARCHAR2
IS
v_return_val ap_invoices_all.invoice_num%TYPE;
BEGIN
IF UPPER (p_je_source) = 'RECEIVABLES' AND p_header_id IS NOT NULL
THEN
IF UPPER (p_je_category) IN ('SALES INVOICES', 'CHARGEBACKS')
THEN
BEGIN
--
SELECT DISTINCT r.trx_number
INTO v_return_val
FROM ra_customer_trx_all r,
ra_cust_trx_line_gl_dist_all gld,
xla_ae_headers aeh
WHERE r.customer_trx_id = gld.customer_trx_id
AND gld.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category IN ('Credit Memos', 'Debit Memos')
THEN
BEGIN
--
SELECT DISTINCT r.trx_number
INTO v_return_val
FROM ra_customer_trx_all r,
ra_cust_trx_line_gl_dist_all gld,
xla_ae_headers aeh
WHERE r.customer_trx_id = gld.customer_trx_id
AND gld.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id
UNION
SELECT DISTINCT ca.receipt_number
FROM AR_CASH_RECEIPTS_ALL ca,
AR_CASH_RECEIPT_HISTORY_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category = 'Adjustment'
THEN
BEGIN
--
SELECT DISTINCT r.trx_number
INTO v_return_val
FROM ra_customer_trx_all r,
AR_ADJUSTMENTS_ALL adj,
xla_ae_headers aeh
WHERE r.customer_trx_id = adj.customer_trx_id
AND adj.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'CREDIT MEMO APPLICATIONS'
THEN
BEGIN
--
SELECT DISTINCT ca.receipt_number
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_RECEIVABLE_APPLICATIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category IN ('Receipts',
'Trade Receipts',
'Rate Adjustments',
'Cross Currency')
THEN
BEGIN
--
SELECT DISTINCT ca.receipt_number
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_CASH_RECEIPT_HISTORY_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id
UNION
SELECT DISTINCT ca.receipt_number
FROM AR_CASH_RECEIPTS_ALL ca,
AR_RECEIVABLE_APPLICATIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category = 'Misc Receipts'
THEN
BEGIN
--
SELECT DISTINCT ca.receipt_number
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_MISC_CASH_DISTRIBUTIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSE
RETURN default_val;
END IF;
--
ELSIF UPPER (p_je_source) = 'PAYABLES'
THEN
IF UPPER (p_je_category) = 'PURCHASE INVOICES'
THEN
BEGIN
--
SELECT DISTINCT invoice_num
INTO v_return_val
FROM ap_invoices_all inv,
ap_invoice_distributions_all aid,
xla_ae_headers aeh
WHERE inv.invoice_id = aid.invoice_id
AND aid.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'PAYMENTS'
THEN
BEGIN
--
SELECT DISTINCT ch.check_number
INTO v_return_val
FROM AP_CHECKS_ALL ch,
ap_invoice_payments_all aip,
xla_ae_headers aeh
WHERE aip.check_id = ch.check_id
AND aip.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = ' RECONCILED PAYMENTS'
THEN
BEGIN
--
SELECT DISTINCT ch.check_number
INTO v_return_val
FROM AP_CHECKS_ALL ch,
ap_payment_history_all aip,
xla_ae_headers aeh
WHERE aip.check_id = ch.check_id
AND aip.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSE
RETURN default_val;
END IF;
ELSE
RETURN default_val;
END IF;
END get_doc_number;
FUNCTION get_gl_date (
p_je_source gl_je_headers.je_source%TYPE,
p_header_id xla_ae_headers.ae_header_id%TYPE,
p_je_category gl_je_headers.je_category%TYPE,
p_default_gl_date gl_je_headers.default_effective_date%TYPE)
RETURN DATE
IS
v_return_val DATE;
BEGIN
IF UPPER (p_je_source) = 'RECEIVABLES' AND p_header_id IS NOT NULL
THEN
IF UPPER (p_je_category) IN ('SALES INVOICES', 'CHARGEBACKS')
THEN
BEGIN
--
SELECT DISTINCT gld.gl_date
INTO v_return_val
FROM ra_customer_trx_all r,
ra_cust_trx_line_gl_dist_all gld,
xla_ae_headers aeh
WHERE r.customer_trx_id = gld.customer_trx_id
AND gld.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category IN ('Credit Memos', 'Debit Memos')
THEN
BEGIN
--
SELECT DISTINCT gld.gl_date
INTO v_return_val
FROM ra_customer_trx_all r,
ra_cust_trx_line_gl_dist_all gld,
xla_ae_headers aeh
WHERE r.customer_trx_id = gld.customer_trx_id
AND gld.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id
UNION
SELECT DISTINCT h.gl_date
FROM AR_CASH_RECEIPTS_ALL ca,
AR_CASH_RECEIPT_HISTORY_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category = 'Adjustment'
THEN
BEGIN
--
SELECT DISTINCT adj.gl_date
INTO v_return_val
FROM ra_customer_trx_all r,
AR_ADJUSTMENTS_ALL adj,
xla_ae_headers aeh
WHERE r.customer_trx_id = adj.customer_trx_id
AND adj.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'CREDIT MEMO APPLICATIONS'
THEN
BEGIN
--
SELECT DISTINCT h.gl_date
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_RECEIVABLE_APPLICATIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category IN ('Receipts',
'Trade Receipts',
'Rate Adjustments',
'Cross Currency')
THEN
BEGIN
--
SELECT DISTINCT h.gl_date
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_CASH_RECEIPT_HISTORY_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id
UNION
SELECT DISTINCT h.gl_date
FROM AR_CASH_RECEIPTS_ALL ca,
AR_RECEIVABLE_APPLICATIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category = 'Misc Receipts'
THEN
BEGIN
--
SELECT DISTINCT h.gl_date
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_MISC_CASH_DISTRIBUTIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSE
RETURN p_default_gl_date;
END IF;
ELSIF UPPER (p_je_source) = 'PAYABLES'
THEN
IF UPPER (p_je_category) = 'PURCHASE INVOICES'
THEN
BEGIN
--
SELECT DISTINCT aid.accounting_date
INTO v_return_val
FROM ap_invoices_all inv,
ap_invoice_distributions_all aid,
xla_ae_headers aeh
WHERE inv.invoice_id = aid.invoice_id
AND aid.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'PAYMENTS'
THEN
BEGIN
--
SELECT DISTINCT aip.accounting_date
INTO v_return_val
FROM AP_CHECKS_ALL ch,
ap_invoice_payments_all aip,
xla_ae_headers aeh
WHERE aip.check_id = ch.check_id
AND aip.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'RECONCILED PAYMENTS'
THEN
BEGIN
--
SELECT DISTINCT aip.accounting_date
INTO v_return_val
FROM AP_CHECKS_ALL ch,
ap_payment_history_all aip,
xla_ae_headers aeh
WHERE aip.check_id = ch.check_id
AND aip.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSE
RETURN p_default_gl_date;
END IF;
ELSE
RETURN p_default_gl_date;
END IF;
END get_gl_date;
--
FUNCTION get_description (
p_je_source gl_je_headers.je_source%TYPE,
p_header_id xla_ae_headers.ae_header_id%TYPE,
p_je_category gl_je_headers.je_category%TYPE,
p_default_description gl_je_lines.description%TYPE)
RETURN VARCHAR2
IS
/* Cac variable sau la temporary var. chi lay gia tri tra ve */
v_return_val gl_je_lines.description%TYPE;
BEGIN
IF p_je_source = 'Payables'
THEN
---- Tinh toan description: Dien giai
IF UPPER (p_je_category) = 'PURCHASE INVOICES'
THEN
BEGIN
--
SELECT DISTINCT inv.description
INTO v_return_val
FROM ap_invoices_all inv,
ap_invoice_distributions_all aid,
xla_ae_headers aeh
WHERE inv.invoice_id = aid.invoice_id
AND aid.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'PAYMENTS'
THEN
BEGIN
--
SELECT DISTINCT ch.description
INTO v_return_val
FROM AP_CHECKS_ALL ch,
ap_invoice_payments_all aip,
xla_ae_headers aeh
WHERE aip.check_id = ch.check_id
AND aip.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'RECONCILED PAYMENTS'
THEN
BEGIN
--
SELECT DISTINCT ch.description
INTO v_return_val
FROM AP_CHECKS_ALL ch,
ap_payment_history_all aip,
xla_ae_headers aeh
WHERE aip.check_id = ch.check_id
AND aip.accounting_event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN v_return_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSE
RETURN p_default_description;
END IF;
---- Ket thuc phan Payables
ELSIF p_je_source = 'Receivables' AND p_header_id IS NOT NULL
THEN
IF UPPER (p_je_category) IN ('SALES INVOICES', 'CHARGEBACKS')
THEN
BEGIN
--
SELECT DISTINCT r.comments
INTO v_return_val
FROM ra_customer_trx_all r,
ra_cust_trx_line_gl_dist_all gld,
xla_ae_headers aeh
WHERE r.customer_trx_id = gld.customer_trx_id
AND gld.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category IN ('Credit Memos', 'Debit Memos')
THEN
BEGIN
--
SELECT DISTINCT r.comments
INTO v_return_val
FROM ra_customer_trx_all r,
ra_cust_trx_line_gl_dist_all gld,
xla_ae_headers aeh
WHERE r.customer_trx_id = gld.customer_trx_id
AND gld.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id
UNION
SELECT DISTINCT ca.comments
FROM AR_CASH_RECEIPTS_ALL ca,
AR_CASH_RECEIPT_HISTORY_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category = 'Adjustment'
THEN
BEGIN
--
SELECT DISTINCT adj.comments
INTO v_return_val
FROM ra_customer_trx_all r,
AR_ADJUSTMENTS_ALL adj,
xla_ae_headers aeh
WHERE r.customer_trx_id = adj.customer_trx_id
AND adj.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF UPPER (p_je_category) = 'CREDIT MEMO APPLICATIONS'
THEN
BEGIN
--
SELECT DISTINCT ca.comments
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_RECEIVABLE_APPLICATIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category IN ('Receipts',
'Trade Receipts',
'Rate Adjustments',
'Cross Currency')
THEN
BEGIN
--
SELECT DISTINCT ca.comments
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_CASH_RECEIPT_HISTORY_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id
UNION
SELECT DISTINCT ca.comments
FROM AR_CASH_RECEIPTS_ALL ca,
AR_RECEIVABLE_APPLICATIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSIF p_je_category = 'Misc Receipts'
THEN
BEGIN
--
SELECT DISTINCT ca.comments
INTO v_return_val
FROM AR_CASH_RECEIPTS_ALL ca,
AR_MISC_CASH_DISTRIBUTIONS_ALL h,
xla_ae_headers aeh
WHERE ca.cash_receipt_id = h.cash_receipt_id
AND h.event_id = aeh.event_id
AND aeh.ae_header_id = p_header_id;
RETURN (v_return_val);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END; --
ELSE
RETURN p_default_description;
END IF;
ELSE
RETURN (p_default_description);
END IF;
END get_description;
/*function TO_NUMBER(P_CHAR IN VARCHAR2) return NUMBER is
begin
RETURN TO_NUMBER (P_CHAR);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END TO_NUMBER;*/
--- Apply Transaction AR
FUNCTION AR_APPLY_TRANSACTION (v_Payment_Schedule_Id NUMBER, v_date CHAR)
RETURN NUMBER
IS
v_Amount NUMBER;
BEGIN
SELECT SUM (a.AMOUNT_APPLIED)
INTO v_Amount
/* a.Customer_Trx_Id,
a.APPLIED_PAYMENT_SCHEDULE_ID,
a.TRX_NUMBER,
a.APPLY_DATE*/
FROM (SELECT CT.Customer_Trx_Id,
APP.APPLIED_PAYMENT_SCHEDULE_ID,
PS.TRX_NUMBER,
APP.APPLY_DATE,
-APP.AMOUNT_APPLIED AMOUNT_APPLIED,
APP.GL_DATE
FROM RA_CUSTOMER_TRX_ALL CT,
RA_CUSTOMER_TRX_LINES_ALL CTL,
RA_CUST_TRX_TYPES_ALL CTT,
AR_RECEIPT_METHODS RM,
AR_CASH_RECEIPTS_ALL CR,
AR_PAYMENT_SCHEDULES_All PS,
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_CASH_RECEIPT_HISTORY_ALL CRH
WHERE APP.STATUS = 'APP'
AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID(+)
AND APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND APP.APPLIED_CUSTOMER_TRX_ID =
CT.CUSTOMER_TRX_ID(+)
AND APP.APPLIED_CUSTOMER_TRX_LINE_ID =
CTL.CUSTOMER_TRX_LINE_ID(+)
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID(+)
AND CRH.CURRENT_RECORD_FLAG(+) = 'Y'
UNION ALL
SELECT CT.Customer_Trx_Id,
APP.PAYMENT_SCHEDULE_ID,
DECODE (
SIGN (PS.PAYMENT_SCHEDULE_ID),
-1, arpt_sql_func_util.get_lookup_meaning (
'PAYMENT_TYPE',
APP.STATUS),
PS.TRX_NUMBER),
APP.APPLY_DATE,
APP.AMOUNT_APPLIED,
APP.GL_DATE
FROM RA_CUSTOMER_TRX_ALL CT,
RA_CUSTOMER_TRX_LINES_ALL RTL,
RA_CUST_TRX_TYPES_ALL CTT,
AR_PAYMENT_SCHEDULES_ALL PS,
AR_RECEIVABLE_APPLICATIONS_ALL APP
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID =
PS.PAYMENT_SCHEDULE_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND APP.APPLIED_CUSTOMER_TRX_LINE_ID =
RTL.CUSTOMER_TRX_LINE_ID(+)
AND APP.STATUS NOT IN ('ACC', 'ACTIVITY', 'OTHER ACC')
AND APP.APPLIED_CUSTOMER_TRX_ID =
CT.CUSTOMER_TRX_ID(+)
UNION ALL
SELECT ART.Receivables_Trx_Id,
APP.PAYMENT_SCHEDULE_ID,
PSA.TRX_NUMBER,
APP.APPLY_DATE,
APP.AMOUNT_APPLIED,
APP.GL_DATE
FROM Ar_Payment_Schedules_All PS,
Ar_Payment_Schedules_All PSA,
AR_RECEIVABLE_APPLICATIONS_ALL APP,
Ar_Receivables_Trx_All ART
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.APPLIED_PAYMENT_SCHEDULE_ID =
PSA.PAYMENT_SCHEDULE_ID
AND APP.STATUS IN ('ACC', 'OTHER ACC')
AND ART.RECEIVABLES_TRX_ID(+) = APP.RECEIVABLES_TRX_ID
UNION ALL
SELECT CT.Customer_Trx_Id,
ADJ.PAYMENT_SCHEDULE_ID,
CT.Trx_Number,
ADJ.APPLY_DATE,
ADJ.AMOUNT,
ADJ.GL_DATE
FROM AR_ADJUSTMENTS_ALL ADJ, --Ar_Receivables_Trx_All RT,
RA_CUSTOMER_TRX_ALL CT
WHERE ADJ.STATUS NOT IN ('R', 'U')
--AND adj.receivables_trx_id = rt.receivables_trx_id(+)
AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
UNION ALL
SELECT rt.Receivables_Trx_Id,
APP.PAYMENT_SCHEDULE_ID,
NULL trx_rec_number,
APP.APPLY_DATE,
APP.AMOUNT_APPLIED,
APP.GL_DATE
FROM AR_PAYMENT_SCHEDULES_ALL PS,
AR_RECEIVABLE_APPLICATIONS_ALL APP,
Ar_Receivables_Trx_All rt
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.receivables_trx_id = rt.receivables_trx_id
AND APP.STATUS = 'ACTIVITY'
AND SIGN (APP.APPLIED_PAYMENT_SCHEDULE_ID) < 0
UNION ALL
SELECT rt.Receivables_Trx_Id,
APP.PAYMENT_SCHEDULE_ID,
PS_DUMMY.TRX_NUMBER,
APP.APPLY_DATE,
APP.AMOUNT_APPLIED,
APP.GL_DATE
FROM AR_PAYMENT_SCHEDULES_ALL PS,
AR_PAYMENT_SCHEDULES_ALL PS_DUMMY,
AR_RECEIVABLE_APPLICATIONS_ALL APP,
Ar_Receivables_Trx_All rt,
AR_CASH_RECEIPT_HISTORY_ALL CRH
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.APPLIED_PAYMENT_SCHEDULE_ID =
PS_DUMMY.PAYMENT_SCHEDULE_ID
AND PS_DUMMY.cash_receipt_id = CRH.cash_receipt_id
AND CRH.current_recorD_flag = 'Y'
AND APP.receivables_trx_id = rt.receivables_trx_id
AND APP.STATUS = 'ACTIVITY'
AND SIGN (APP.APPLIED_PAYMENT_SCHEDULE_ID) > 0) a
WHERE A.APPLIED_PAYMENT_SCHEDULE_ID = v_Payment_Schedule_Id
AND TRUNC (A.apply_date) <= TO_DATE (v_date, 'DD-MON-RRRR');
RETURN v_Amount;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION MASTER_ORG
RETURN NUMBER
IS
BEGIN
RETURN 87;
END;
FUNCTION pts_to_number (v_varchar2 VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER (v_varchar2);
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/*function GET_ORG_PHONE(p_org_id number) return char is
v_phone varchar2(300);
begin
select a.telephone_number_1
into v_phone
from hr_organization_units_v a
where organization_id = p_org_id ;
return v_phone;
exception
when others then return null;
end GET_ORG_PHONE;*/
/*function GET_ORG_FAX(p_org_id number) return char is
v_fax varchar2(300);
begin
select a.telephone_number_2
into v_fax
from hr_organization_units_v a
where organization_id = p_org_id ;
return v_fax;
exception
when others then return null;
end GET_ORG_FAX;*/
FUNCTION GET_ORG_WEBSITE (p_org_id NUMBER)
RETURN VARCHAR2
IS
v_website VARCHAR2 (300);
BEGIN
SELECT a.telephone_number_3
INTO v_website
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_website;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_ORG_WEBSITE;
FUNCTION GET_ORG_SHORT_NAME (p_org_id NUMBER)
RETURN VARCHAR2
IS
v_org_short_name VARCHAR2 (200);
BEGIN
SELECT a.attribute6
INTO v_org_short_name
FROM hr_organization_units_v a
WHERE organization_id = p_org_id;
RETURN v_org_short_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END GET_ORG_SHORT_NAME;
FUNCTION get_attach_long_text (
p_category_name fnd_document_categories_tl.user_name%TYPE,
p_source fnd_attached_documents.entity_name%TYPE,
p_header_id NUMBER)
RETURN fnd_documents_long_text.long_text%TYPE
IS
v_long_text fnd_documents_long_text.long_text%TYPE;
BEGIN
SELECT long_txt.long_text
INTO v_long_text
FROM fnd_attached_documents attach,
fnd_documents doc,
fnd_documents_tl doc_tl,
fnd_document_categories_tl doc_cate,
fnd_documents_long_text long_txt
WHERE attach.pk1_value = TO_CHAR (p_header_id)
AND attach.entity_name = p_source
AND attach.document_id = doc.document_id
AND doc.category_id = doc_cate.category_id
AND doc.document_id = doc_tl.document_id
AND doc_cate.LANGUAGE = 'US'
AND doc_tl.LANGUAGE = 'US'
AND doc_tl.media_id = long_txt.media_id
AND doc.datatype_id = 2
AND doc_cate.user_name = p_category_name; -- Long Text
RETURN (v_long_text);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END;
FUNCTION get_attach_short_text (
p_category_name fnd_document_categories_tl.user_name%TYPE,
p_source fnd_attached_documents.entity_name%TYPE,
p_header_id NUMBER)
RETURN fnd_documents_short_text.short_text%TYPE
IS
v_short_text fnd_documents_short_text.short_text%TYPE;
BEGIN
SELECT short_txt.short_text
INTO v_short_text
FROM fnd_attached_documents attach,
fnd_documents doc,
fnd_documents_tl doc_tl,
fnd_document_categories_tl doc_cate,
fnd_documents_short_text short_txt
WHERE attach.pk1_value = TO_CHAR (p_header_id)
AND attach.entity_name = p_source
AND attach.document_id = doc.document_id
AND doc.category_id = doc_cate.category_id
AND doc.document_id = doc_tl.document_id
AND doc_cate.LANGUAGE = 'US'
AND doc_tl.LANGUAGE = 'US'
AND doc_tl.media_id = short_txt.media_id
AND doc.datatype_id = 1
AND ( doc_cate.user_name = p_category_name
OR p_category_name IS NULL); -- Long Text
RETURN (v_short_text);
EXCEPTION
WHEN OTHERS
THEN
RETURN (NULL);
END;
FUNCTION get_dept_from_emp (p_person_id NUMBER)
RETURN VARCHAR2
IS
v_dept_name VARCHAR2 (200);
BEGIN
/*select ffv.DESCRIPTION
into v_dept_name
from fnd_flex_value_sets fvs,
fnd_flex_values_vl ffv,
per_position_definitions_kfv ppd,
hr_all_positions_f_vl hap,
PER_ALL_ASSIGNMENTS_F paa
where fvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.FLEX_VALUE = ppd.segment2
and ppd.position_definition_id = hap.POSITION_DEFINITION_ID
and hap.POSITION_ID = paa.position_id
and fvs.flex_value_set_name = 'PVD_Department'
and paa.person_id = p_person_id
and rownum = 1;*/
RETURN v_dept_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_emp_name (p_person_id NUMBER)
RETURN VARCHAR2
IS
v_emp_name VARCHAR2 (200);
BEGIN
SELECT ppf.FULL_NAME
INTO v_emp_name
FROM PER_PEOPLE_F ppf
WHERE ppf.PERSON_ID = p_person_id
AND ppf.EFFECTIVE_END_DATE > SYSDATE;
RETURN v_emp_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_vendor_address (p_vendor_id NUMBER)
RETURN VARCHAR2
IS
v_vend_add VARCHAR2 (200);
BEGIN
SELECT site.address_line1
INTO v_vend_add
FROM po_vendor_sites_all site
WHERE site.vendor_id = p_vendor_id
AND site.vendor_site_id =
(SELECT MAX (site1.vendor_site_id)
FROM po_vendor_sites_all site1
WHERE site1.vendor_id = site.vendor_id
AND ( site1.inactive_date IS NULL
OR site1.inactive_date > SYSDATE));
RETURN v_vend_add;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
-- Tinlv 22/12/2009 --- Lay description cua valueset
FUNCTION GET_FLEX_VALUE (v_value_set_name VARCHAR2, p_value VARCHAR2)
RETURN CHAR
IS
v_results VARCHAR2 (200);
BEGIN
SELECT t.description
INTO v_results
FROM fnd_flex_values_tl t, fnd_flex_values b, fnd_flex_value_sets a
WHERE b.flex_value_id = t.flex_value_id
AND b.flex_value_set_id = a.flex_value_set_id
-- Constants
AND t.language = USERENV ('LANG')
-- Parameters
AND a.flex_value_set_name = v_value_set_name
AND b.flex_value = p_value;
RETURN v_results;
EXCEPTION
WHEN OTHERS
THEN
RETURN ' ';
END;
--Tuanna 20/12/2012 --- Lay logo de gan cho header report
FUNCTION org_logo (p_org_id IN NUMBER)
RETURN VARCHAR2
IS
x VARCHAR2 (32767);
BEGIN
SELECT xxff_mob_getbase64String (logo)
INTO x
FROM XXFF_Operating_Unit_Logo
WHERE org_id = p_org_id;
RETURN x;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END org_logo;
FUNCTION xxff_mob_getbase64String (P_blob BLOB)
RETURN CLOB
IS
l_result CLOB;
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_result, CACHE => FALSE, dur => 0);
Wf_Mail_Util.EncodeBLOB (P_blob, l_result);
RETURN (l_result);
END xxff_mob_getbase64String;
-- Tainv_Get_MetAMetBMetC_07Aug2013
FUNCTION metA_metB_metC (p_metA VARCHAR2,
p_metB VARCHAR2,
p_metC VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER (NVL (p_metA, 0))
+ TO_NUMBER (NVL (p_metB, 0))
+ TO_NUMBER (NVL (p_metC, 0));
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END metA_metB_metC;
-- Tainv_Get_MetAMetBMetC_08Aug2013
FUNCTION Get_metA_metB_metC (p_lot_number VARCHAR2,
p_organization_id VARCHAR2,
p_inventory_item_id VARCHAR2)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
SELECT TO_NUMBER (a.c_attribute14)
INTO v_result
FROM mtl_transaction_lot_numbers a
WHERE 1 = 1
AND a.lot_number = p_lot_number
AND a.inventory_item_id = p_inventory_item_id
AND a.organization_id = p_organization_id
AND a.c_attribute14 IS NOT NULL
AND ROWNUM = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END Get_metA_metB_metC;
-- Tainv_Get_MetA_08Aug2013
FUNCTION Get_metA (p_lot_number VARCHAR2,
p_organization_id VARCHAR2,
p_inventory_item_id VARCHAR2)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
SELECT t.metA
INTO v_result
FROM ( SELECT TO_NUMBER (a.c_attribute1) metA, a.last_update_date
FROM mtl_transaction_lot_numbers a
WHERE 1 = 1
AND a.lot_number = p_lot_number
AND a.organization_id = p_organization_id
AND a.inventory_item_id = p_inventory_item_id
AND a.c_attribute14 IS NOT NULL -- sum(MetA, MetB, MetC)
AND a.c_attribute1 IS NOT NULL -- MetA
ORDER BY a.last_update_date DESC) t
WHERE 1 = 1 AND ROWNUM = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_result;
END Get_metA;
-- Tainv_Get_MetB_08Aug2013
FUNCTION Get_metB (p_lot_number VARCHAR2,
p_organization_id VARCHAR2,
p_inventory_item_id VARCHAR2)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
SELECT t.metB
INTO v_result
FROM ( SELECT TO_NUMBER (a.c_attribute2) metB, a.last_update_date
FROM mtl_transaction_lot_numbers a
WHERE 1 = 1
AND a.lot_number = p_lot_number
AND a.organization_id = p_organization_id
AND a.inventory_item_id = p_inventory_item_id
AND a.c_attribute14 IS NOT NULL -- sum(MetA, MetB, MetC)
AND a.c_attribute2 IS NOT NULL -- MetB
ORDER BY a.last_update_date DESC) t
WHERE 1 = 1 AND ROWNUM = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_result;
END Get_metB;
-- Tainv_Get_MetC_08Aug2013
FUNCTION Get_metC (p_lot_number VARCHAR2,
p_organization_id VARCHAR2,
p_inventory_item_id VARCHAR2)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
SELECT t.metB
INTO v_result
FROM ( SELECT TO_NUMBER (a.c_attribute3) metB, a.last_update_date
FROM mtl_transaction_lot_numbers a
WHERE 1 = 1
AND a.lot_number = p_lot_number
AND a.organization_id = p_organization_id
AND a.inventory_item_id = p_inventory_item_id
AND a.c_attribute14 IS NOT NULL -- sum(MetA, MetB, MetC)
AND a.c_attribute3 IS NOT NULL -- MetC
ORDER BY a.last_update_date DESC) t
WHERE 1 = 1 AND ROWNUM = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_result;
END Get_metC;
-- Tainv_UOM_Conversion_rate
FUNCTION uom_conversion_rate (p_inventory_item_id NUMBER,
p_from_uom_code VARCHAR2,
p_to_uom_code VARCHAR2)
RETURN NUMBER
IS
v_result NUMBER;
v_primary_uom_code VARCHAR2 (100);
BEGIN
IF p_from_uom_code IS NULL
THEN
SELECT msi.primary_uom_code
INTO v_primary_uom_code
FROM mtl_system_items_b msi
WHERE 1 = 1
AND msi.inventory_item_id = p_inventory_item_id
AND ROWNUM = 1;
ELSE
v_primary_uom_code := p_from_uom_code;
END IF;
v_result :=
inv_convert.inv_um_convert (p_inventory_item_id,
v_primary_uom_code,
p_to_uom_code);
IF v_result < 0
THEN
v_result := 1;
END IF;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN 1;
END uom_conversion_rate;
-- Tainv_Translate_Format_Vietnames
FUNCTION Translate_Vietnamese (p_number NUMBER,
p_option NUMBER,
p_always_show_decimal VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2 (300);
BEGIN
IF p_always_show_decimal = 'Y'
THEN
SELECT CASE
WHEN p_option = 0
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999'),
',.',
'.,')
WHEN p_option = 1
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,990.0'),
',.',
'.,')
WHEN p_option = 2
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,990.00'),
',.',
'.,')
WHEN p_option = 3
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,990.000'),
',.',
'.,')
WHEN p_option = 4
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,990.0000'),
',.',
'.,')
WHEN p_option = 5
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,990.00000'),
',.',
'.,')
END
INTO v_result
FROM DUAL;
ELSE
SELECT CASE
WHEN TRUNC (p_number) = p_number
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999'),
',.',
'.,')
WHEN p_option = 0
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999'),
',.',
'.,')
WHEN p_option = 1
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999.9'),
',.',
'.,')
WHEN p_option = 2
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999.99'),
',.',
'.,')
WHEN p_option = 3
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999.999'),
',.',
'.,')
WHEN p_option = 4
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999.9999'),
',.',
'.,')
WHEN p_option = 5
THEN
TRANSLATE (
TO_CHAR (
p_number,
'fm999,999,999,999,999,999,999,999,999.99999'),
',.',
'.,')
END
INTO v_result
FROM DUAL;
END IF;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN p_number;
END Translate_Vietnamese;
-- Tainv_UnitMessure_UnitCode
FUNCTION Translate_UnitMessure_UnitCode (p_unit_of_messure VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2 (100);
BEGIN
SELECT a.uom_code
INTO v_result
FROM mtl_units_of_measure a
WHERE a.unit_of_measure = p_unit_of_messure;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_result;
END Translate_UnitMessure_UnitCode;
-- Tainv_Get_Max_Refund_DocSequenceValue
FUNCTION get_max_doc_sequence_value (p_ledger_id NUMBER,
p_application_id NUMBER,
p_category VARCHAR2,
p_method VARCHAR2)
RETURN NUMBER
IS
CURSOR sql_seq IS
SELECT SEQ.DB_SEQUENCE_NAME,
SEQ.DOC_SEQUENCE_ID,
SEQ.TYPE,
SA.DOC_SEQUENCE_ASSIGNMENT_ID,
SA.CATEGORY_CODE,
NVL (MAX (SEQA.DOC_SEQUENCE_VALUE) + 1, SEQ.INITIAL_VALUE) MAX_DOC_SEQUENCE_VALUE
FROM FND_DOCUMENT_SEQUENCES SEQ,
FND_DOC_SEQUENCE_ASSIGNMENTS SA,
AP_DOC_SEQUENCE_AUDIT SEQA
WHERE 1 = 1
AND SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
AND SEQ.DOC_SEQUENCE_ID = SEQA.DOC_SEQUENCE_ID(+)
-- Constants
AND SA.APPLICATION_ID = p_application_id -- 200
AND SA.CATEGORY_CODE = p_category -- 'SUPPLIER REFUND PAY'
-- Parameters
AND (SA.METHOD_CODE = p_method /*'A'*/
OR SA.METHOD_CODE IS NULL)
AND ( SA.SET_OF_BOOKS_ID = p_ledger_id /*2023*/
OR SA.SET_OF_BOOKS_ID IS NULL)
AND SYSDATE BETWEEN SA.START_DATE
AND NVL (SA.END_DATE + .9999,
SYSDATE + .9999)
GROUP BY SEQ.DB_SEQUENCE_NAME,
SEQ.DOC_SEQUENCE_ID,
SEQ.TYPE,
SA.DOC_SEQUENCE_ASSIGNMENT_ID,
SA.CATEGORY_CODE,
SEQ.INITIAL_VALUE;
v_result VARCHAR2 (100);
BEGIN
FOR i IN sql_seq
LOOP
v_result := i.MAX_DOC_SEQUENCE_VALUE;
END LOOP;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_result;
END get_max_doc_sequence_value;
-- TaiNV get available quantity with lot number
FUNCTION get_available_lot_qty (pp_organization_id NUMBER,
pp_inventory_item_id NUMBER,
pp_lot_number VARCHAR2)
RETURN NUMBER
IS
v_qty_atr NUMBER;
BEGIN
SELECT SUM (aaa.TOTAL_QOH)
INTO v_qty_atr
FROM mtl_onhand_locator_lot_v aaa
WHERE 1 = 1
AND aaa.organization_id = pp_organization_id
AND aaa.inventory_item_id = pp_inventory_item_id
AND aaa.lot = pp_lot_number;
RETURN NVL (v_qty_atr, 0);
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_available_lot_qty;
-- TaiNV get phone, fax number of customer
FUNCTION get_customer_phone_fax (p_party_id NUMBER,
p_party_site_id NUMBER,
p_type VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2 (500);
CURSOR sql_fax_cust_header IS
SELECT hcp.phone_number fax_number
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.phone_line_type = 'FAX'
AND hcp.status = 'A'
AND hcp.owner_table_id = p_party_id;
CURSOR sql_phone_cust_header IS
SELECT hcp.phone_number
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.owner_table_id = p_party_id;
CURSOR sql_fax_cust_site IS
SELECT hcp.phone_number fax_number
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_name = 'HZ_PARTY_SITES'
AND hcp.phone_line_type = 'FAX'
AND hcp.status = 'A'
AND hcp.owner_table_id = p_party_site_id;
CURSOR sql_phone_cust_site IS
SELECT hcp.phone_number
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_name = 'HZ_PARTY_SITES'
AND hcp.phone_line_type = 'GEN'
AND hcp.status = 'A'
AND hcp.owner_table_id = p_party_site_id;
BEGIN
-- Lay so phone
IF p_type = 'PHONE'
THEN
IF p_party_id IS NOT NULL
THEN
-- Lay phone number customer header
FOR i IN sql_phone_cust_header
LOOP
SELECT DECODE (v_result,
NULL, i.phone_number,
v_result || ', ' || i.phone_number)
INTO v_result
FROM DUAL;
END LOOP;
ELSIF p_party_site_id IS NOT NULL
THEN
-- Lay phone number tren customer site
FOR j IN sql_phone_cust_site
LOOP
SELECT DECODE (v_result,
NULL, j.phone_number,
v_result || ', ' || j.phone_number)
INTO v_result
FROM DUAL;
END LOOP;
END IF;
ELSIF p_type = 'FAX'
THEN
IF p_party_id IS NOT NULL
THEN
-- Lay fax number customer header
FOR k IN sql_fax_cust_header
LOOP
SELECT DECODE (v_result,
NULL, k.fax_number,
v_result || ',' || k.fax_number)
INTO v_result
FROM DUAL;
END LOOP;
ELSIF p_party_site_id IS NOT NULL
THEN
-- Lay fax number tren customer site
FOR g IN sql_fax_cust_site
LOOP
SELECT DECODE (v_result,
NULL, g.fax_number,
v_result || ',' || g.fax_number)
INTO v_result
FROM DUAL;
END LOOP;
END IF;
END IF;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_customer_phone_fax;
-- TaiNV get Receipt Number
FUNCTION get_PO_receipt (p_ou_id NUMBER, p_po_number VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2 (100);
BEGIN
SELECT MIN (rsh.receipt_num)
INTO v_result
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_lines_all pol,
po_headers_all poh
WHERE 1 = 1
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.po_line_id = pol.po_line_id
AND pol.po_header_id = poh.po_header_id
AND poh.segment1 = p_po_number
AND poh.org_id = p_ou_id;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_PO_receipt;
-- JASON TRAN 28-05-2018
FUNCTION get_Parent_Customer (customer_id VARCHAR2,
transaction_date DATE,
DEFAULT_VALUE VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2 (1000);
BEGIN
SELECT (group_info.parent_number || ' - ' || group_info.parent_name)
INTO v_result
FROM (SELECT Relationship_code,
hca.account_number
child_number,
hp.party_name
child_name,
(SELECT hca1.account_number
FROM hz_cust_accounts hca1
WHERE hca1.party_id = hr.object_id)
parent_number,
(SELECT party_name
FROM hz_parties
WHERE party_id = hr.object_id)
parent_name
FROM hz_relationships hr,
hz_parties hp,
hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hp.party_id = hr.subject_id
AND hr.object_id <> hr.subject_id
--AND hr.status = 'A'
--AND hp.status = 'A'
-- AND hr.directional_flag = 'F'
AND object_type = 'ORGANIZATION'
AND UPPER (Relationship_code) = 'SUBSIDIARY_OF'
AND TRUNC (hr.end_date) >= TRUNC (SYSDATE)
--AND hca.status = 'A'
AND hca.account_number = customer_id
AND ROWNUM <= 1
AND ( transaction_date IS NULL
OR end_date IS NULL
OR TRUNC (transaction_date) <= TRUNC (end_date)))
group_info; --WHERE group_info.child_number = customer_id AND rownum = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN DEFAULT_VALUE;
--return SQLERRM;
END get_Parent_Customer;
-- Ngoc Son 08/02/2023
FUNCTION get_Parent_Customer_Without_ID (customer_id VARCHAR2,
transaction_date DATE,
DEFAULT_VALUE VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2 (1000);
BEGIN
SELECT group_info.parent_name
INTO v_result
FROM (SELECT Relationship_code,
hca.account_number
child_number,
hp.party_name
child_name,
(SELECT hca1.account_number
FROM hz_cust_accounts hca1
WHERE hca1.party_id = hr.object_id)
parent_number,
(SELECT party_name
FROM hz_parties
WHERE party_id = hr.object_id)
parent_name
FROM hz_relationships hr,
hz_parties hp,
hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hp.party_id = hr.subject_id
AND hr.object_id <> hr.subject_id
--AND hr.status = 'A'
--AND hp.status = 'A'
-- AND hr.directional_flag = 'F'
AND object_type = 'ORGANIZATION'
AND UPPER (Relationship_code) = 'SUBSIDIARY_OF'
AND TRUNC (hr.end_date) >= TRUNC (SYSDATE)
--AND hca.status = 'A'
AND hca.account_number = customer_id
AND ROWNUM <= 1
AND ( transaction_date IS NULL
OR end_date IS NULL
OR TRUNC (transaction_date) <= TRUNC (end_date)))
group_info; --WHERE group_info.child_number = customer_id AND rownum = 1;
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
RETURN DEFAULT_VALUE;
END get_Parent_Customer_Without_ID;
--added by Linh Tran 12-09-2018
FUNCTION CHECK_SO_TO_RELEASE (p_order_header_id NUMBER)
RETURN VARCHAR2
IS
v_result VARCHAR2 (5000);
CURSOR c_data IS
SELECT h.order_number
lxh,
msi.segment1
ma_hang,
CASE
WHEN mr.reservation_uom_code = 'Tan'
THEN
SUM (NVL (mr.reservation_quantity, 0)) * 1000
ELSE
SUM (NVL (mr.reservation_quantity, 0))
END
sl_dvt1_reserved,
mr.reservation_uom_code
dvt1_reserved,
CASE
WHEN l.order_quantity_uom = 'Tan'
THEN
NVL (l.ordered_quantity, 0) * 1000
ELSE
NVL (l.ordered_quantity, 0)
END
sl_dvt1_ordered,
l.order_quantity_uom
dvt1_ordered,
SUM (ROUND (NVL (mr.secondary_reservation_quantity, 0)))
sl_dvt2_reserved,
mr.secondary_uom_code
dvt2_reserved,
ROUND (NVL (l.ordered_quantity2, 0))
sl_dvt2_ordered,
l.ordered_quantity_uom2
dvt2_ordered,
l.line_number
order_line_number,
l.ship_from_org_id ---add by ngocquoc 25032021
FROM oe_order_lines_all l,
oe_order_headers_all h,
mtl_system_items_b msi,
mtl_reservations mr
WHERE 1 = 1
AND h.header_id = l.header_id
AND h.org_id = l.org_id
AND l.line_id = mr.demand_source_line_id(+)
AND l.inventory_item_id = msi.inventory_item_id
AND l.ship_from_org_id = msi.organization_id
-- Parameters
AND ( h.header_id = p_order_header_id
OR p_order_header_id IS NULL)
/* and h.order_type_id IN(1041 \*TDA.Standard Order (TP_ND)*\
,1043\*TDA.Standard Order (TP_XK)*\
,1347\*TDA.ST3.Standard Order (TP_ND)*\
,1348\*TDA.ST3.Standard Order (TP_XK)*\
,1147\*TDA.TS1.Standard Order (TP_ND)*\
,1145\*TDA.TS1.Standard Order (TP_XK)*\
)*/
AND mr.lot_number IS NOT NULL
AND mr.reservation_quantity IS NOT NULL
AND mr.secondary_reservation_quantity IS NOT NULL
GROUP BY h.order_number,
msi.segment1,
NVL (l.ordered_quantity, 0),
ROUND (NVL (l.ordered_quantity2, 0)),
l.line_number,
mr.reservation_uom_code,
l.order_quantity_uom,
mr.secondary_uom_code,
l.ordered_quantity_uom2,
l.ship_from_org_id ---add by ngocquoc 25032021
ORDER BY l.line_number;
BEGIN
--dbms_output.put_line('begin');
FOR i IN c_data
LOOP
BEGIN
/*begin add by ngocquoc 25032021*/
IF i.ship_from_org_id IN (83, 241)
THEN --neu kho RM1, RM3 kiem tra so luong dvt1
IF i.sl_dvt1_ordered != i.sl_dvt1_reserved
THEN
v_result :=
v_result
|| '- Line '
|| i.order_line_number
|| ', Item: '
|| i.ma_hang
|| ', đặt: '
|| i.sl_dvt1_ordered
|| ', reserver: '
|| i.sl_dvt1_reserved
|| ' kg.'
|| CHR (10);
END IF;
ELSIF i.sl_dvt1_ordered != i.sl_dvt1_reserved
THEN
v_result :=
v_result
|| '- Line '
|| i.order_line_number
|| ', Item: '
|| i.ma_hang
|| ', đặt: '
|| i.sl_dvt1_ordered
|| ', reserver: '
|| i.sl_dvt1_reserved
|| ' kg.'
|| CHR (10);
ELSIF i.sl_dvt2_reserved != i.sl_dvt2_ordered
THEN
v_result :=
v_result
|| '- Line '
|| i.order_line_number
|| ', Item: '
|| i.ma_hang
|| ', đặt: '
|| i.sl_dvt2_ordered
|| ', reserver: '
|| i.sl_dvt2_reserved
|| ' met.'
|| CHR (10);
END IF;
/*end add by ngocquoc 25032021*/
/*
IF i.sl_dvt1_ordered != i.sl_dvt1_reserved THEN
v_result :=v_result|| '- Line ' || i.order_line_number
||', Item: ' ||i.ma_hang
||', đặt: ' ||i.sl_dvt1_ordered
||', reserver: '||i.sl_dvt1_reserved || ' kg.' || chr(10);
ELSIF i.sl_dvt2_reserved != i.sl_dvt2_ordered THEN
v_result := v_result|| '- Line ' || i.order_line_number
||', Item: ' ||i.ma_hang
||', đặt: ' ||i.sl_dvt2_ordered
||', reserver: '||i.sl_dvt2_reserved || ' met.' || chr(10);
END IF;
*/
END;
END LOOP;
IF v_result IS NOT NULL
THEN
v_result :=
'Số lượng đặt không bằng với số lượng reserver:'
|| CHR (10)
|| v_result;
END IF;
RETURN v_result;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 'Khong tim thay SO';
WHEN OTHERS
THEN
RETURN SQLERRM;
END CHECK_SO_TO_RELEASE;
--added by Linh Tran 06-03-2019
FUNCTION GET_SALE_DEPARTMENT (p_id NUMBER)
RETURN VARCHAR2
IS
v_result VARCHAR2 (5000);
BEGIN
SELECT DESCRIPTION
INTO v_result
FROM fnd_flex_values_vl
WHERE FLEX_VALUE_SET_ID = 1015349 /*CUS_SALE*/
--AND FLEX_VALUE not in ('KHXNK','AB')
AND FLEX_VALUE_ID = p_id
AND ROWNUM = 1;
RETURN v_result;
END GET_SALE_DEPARTMENT;
--added by Linh Tran 29-03-2019
FUNCTION GET_ERROR_DEPARTMENT (p_id NUMBER)
RETURN VARCHAR2
IS
v_result VARCHAR2 (5000);
BEGIN
SELECT ffv.DESCRIPTION
INTO v_result --, ffs.flex_value_set_name
FROM FND_FLEX_VALUES_VL ffv
WHERE FLEX_VALUE_SET_ID = 1015035 /*'ERROR_DEPT_CUS'*/
AND FLEX_VALUE_ID = p_id
AND ROWNUM = 1;
RETURN v_result;
END GET_ERROR_DEPARTMENT;
--added by Linh Tran 20-05-2020
FUNCTION CHECK_SO_TO_SHIP (p_delivery_id NUMBER)
RETURN VARCHAR2
IS
v_result VARCHAR2 (5000);
CURSOR c_data IS
SELECT v.SOURCE_HEADER_NUMBER,
v.source_line_number,
v.REQUESTED_QUANTITY,
v.PICKED_QUANTITY
FROM WSH_DELIVERABLES_V v
WHERE v.REQUESTED_QUANTITY != v.PICKED_QUANTITY
AND v.delivery_id = p_delivery_id; --2381776;
BEGIN
FOR i IN c_data
LOOP
BEGIN
v_result :=
CHR (10)
|| 'SO: '
|| i.SOURCE_HEADER_NUMBER
|| ' , Line:'
|| i.source_line_number
|| ' , Ðặt:'
|| i.REQUESTED_QUANTITY
|| ' , Release: '
|| i.PICKED_QUANTITY;
--dbms_output.put_line(i.SOURCE_HEADER_NUMBER);
END;
END LOOP;
-- dbms_output.put_line(v_result);
IF v_result IS NOT NULL
THEN
v_result :=
'Lỗi Ship hàng, SL đặt không bằng SL Release:'
|| v_result;
END IF;
RETURN v_result;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 'Co loi xay ra';
WHEN OTHERS
THEN
RETURN SQLERRM;
END CHECK_SO_TO_SHIP;
--added by Linh Tran 01-05-2022
FUNCTION CHECK_ITEM_ACCOUNT (p_category VARCHAR2,
p_account VARCHAR2,
p_org_id NUMBER)
RETURN VARCHAR2
IS
v_result VARCHAR2 (5000); -- 1 là thoa dieu kien rule
ou_id NUMBER;
BEGIN
-- check OU, chi app dung tai TDA
SELECT xxff_secure_erp.check_org_ou_mtv (p_org_id)
INTO ou_id
FROM DUAL;
IF (ou_id != 0)
THEN -- ko phai TDA
RETURN '1';
END IF;
---
IF (p_category = 'DEFAULT.DEFAULT')
THEN
RETURN 'Vui lòng chọn Item Category';
END IF;
---
SELECT d.category
INTO v_result
FROM xxff_default_item_category d
WHERE d.category = p_category
AND d.account LIKE '%' || p_account || '%';
IF v_result IS NOT NULL
THEN
v_result := '1';
END IF;
RETURN v_result;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
SELECT d.account
INTO v_result
FROM xxff_default_item_category d
WHERE d.category = p_category;
RETURN p_category
|| ' chỉ duợc chọn tài khoản:'
|| v_result;
WHEN OTHERS
THEN
RETURN SQLERRM;
END CHECK_ITEM_ACCOUNT;
FUNCTION CHECK_USER_BELONG_DEPARTMENT (p_user_id NUMBER,
p_department VARCHAR2)
RETURN NUMBER
IS -- added by Linh 05/01/2024
ket_qua NUMBER;
BEGIN
SELECT 1
INTO ket_qua
FROM fnd_user a
WHERE (a.description LIKE '%' || p_department || '%')
AND (a.end_date IS NULL OR a.end_date >= SYSDATE)
AND a.user_id = p_user_id;
IF (ket_qua IS NULL)
THEN
ket_qua := 0;
ELSE
ket_qua := 1;
END IF;
RETURN ket_qua;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN -1;
END CHECK_USER_BELONG_DEPARTMENT;
--return 1: OK
END xxff_general_pkg;
/
Last updated