[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
# Fetches the account mapping for AR update Package.
# GMFARUPD
# HISTORY
# 11-Sep-2001 Uday Moogala Bug 2031374 - New Item Attributes
# Added two new item attributes - GL Business Class and GL Product Line
# as input parameters to get_account_mappings procedures.
# Also made other changes required to incorporate this feature at
# various places. Search with bug# for changes made
# 11-Oct-2001 Uday Moogala Bug 2468912 - New Attribute
# Added a new attribute Line Type as input parameters to
# get_account_mappings procedures. Search with bug# for changes made
# 22-Oct-2001 Uday Moogala Bug 2423983 - New Attribute
# Added a new attribute AR Trans Type as input parameters to
# get_account_mappings procedures. Search with bug# for changes made
################################################### */
PROCEDURE get_account_mappings (
v_co_code IN OUT NOCOPY VARCHAR2,
v_orgn_code VARCHAR2,
v_whse_code VARCHAR2,
v_item_id NUMBER,
v_vendor_id NUMBER,
v_cust_id NUMBER,
v_reason_code VARCHAR2,
v_icgl_class VARCHAR2,
v_vendgl_class VARCHAR2,
v_custgl_class VARCHAR2,
v_currency_code VARCHAR2,
v_routing_id NUMBER,
v_charge_id NUMBER,
v_taxauth_id NUMBER,
v_aqui_cost_id NUMBER,
v_resources VARCHAR2,
v_cost_cmpntcls_id NUMBER,
v_cost_analysis_code VARCHAR2,
v_order_type NUMBER,
v_sub_event_type NUMBER,
v_acct_ttl_type NUMBER,
v_acct_id IN OUT NOCOPY NUMBER,
v_acctg_unit_id IN OUT NOCOPY NUMBER,
v_source NUMBER DEFAULT 0,
v_business_class_cat_id NUMBER DEFAULT 0, -- Bug 2031374 - umoogala
v_product_line_cat_id NUMBER DEFAULT 0, -- Bug 2031374 - umoogala
v_line_type NUMBER DEFAULT NULL, -- Bug 2468912 - umoogala
v_ar_trx_type_id NUMBER DEFAULT 0 -- Bug 2423983 - umoogala
)
IS
X_sqlstmt VARCHAR2(2000);
SELECT co_code
FROM sy_orgn_mst
WHERE orgn_code = v_org_code;
SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = v_whs_code;
SELECT map_orgn_ind, acct_ttl_type
FROM gl_sevt_ttl
WHERE sub_event_type = v_sub_event_type
AND acct_ttl_type = v_acct_ttl_type)
LOOP
/* VC - Bug 1498503 */
IF (Cur_subevtacct_ttl.map_orgn_ind = 1) THEN
X_map_orgn_code := V_orgn_code;
X_sqlstmt := 'SELECT NVL(co_code,'' ''),orgn_code_pri, whse_code_pri,icgl_class_pri, custgl_class_pri,vendgl_class_pri ,item_pri,customer_pri, vendor_pri,tax_auth_pri,';
X_sqlstmt2 := ' WHERE acct_ttl_type = :pacct_ttl_type AND (co_code IS NULL OR co_code = :pco_code) AND delete_mark = 0 ORDER BY 1 desc';
/* selecting the acct_id in gl_acct_map
create the order_by based on the priority retrieved above.
company is always the first column selected */
-- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
-- Bug 2423983: 22 to 23
FOR z IN 1..23 LOOP
X_order_by(z) := 0;
' AND delete_mark = 0 '; */
' AND delete_mark = 0 ';
/* Changed the selection of acct_id to be the last column to be
selected. If not since the order by is by field position no the
correct acct_id doesnot get picked up if there are more than 1 record */
X_sqlcolumns:= ' SELECT co_code,'||
'nvl(orgn_code,'' ''),'||
'nvl(whse_code,'' ''),'||
'nvl(icgl_class,'' ''),'||
'nvl(custgl_class,'' ''),'||
'nvl(vendgl_class,'' ''),';
/* FOR crec IN (SELECT acctg_unit_id
FROM gl_accu_map
WHERE co_code = X_co_code
AND gmf_get_mappings.fstrcmp(orgn_code, X_map_orgn_code) = 1
AND gmf_get_mappings.fstrcmp(whse_code, v_whse_code) = 1
AND delete_mark = 0
ORDER BY nvl(orgn_code,' ') DESC, nvl(whse_code,' ') DESC
) */
FOR crec IN (SELECT acctg_unit_id
FROM gl_accu_map
WHERE co_code = X_co_code
AND (orgn_code IS NULL OR orgn_code = X_map_orgn_code)
AND (whse_code IS NULL OR whse_code = v_whse_code)
AND delete_mark = 0
ORDER BY nvl(orgn_code,' ') DESC, nvl(whse_code,' ') DESC
)
LOOP
v_acctg_unit_id := crec.acctg_unit_id;
FOR crec IN (SELECT
/* B1043070: Changed "substrb" to "substr", we want
REM everything from 8th character and not byte */
/* B2227050: select f.segment_num instead of f.application_column_name
REM order by f.segment_num instead of p.segment_no */
f.segment_num, p.segment_no, p.length
FROM
gl_plcy_seg p,
gl_plcy_mst pm,
fnd_id_flex_segments f,
gl_sets_of_books s
WHERE
p.co_code = p_co_code
AND p.delete_mark = 0
AND p.co_code = pm.co_code
AND pm.sob_id = s.set_of_books_id
AND s.chart_of_accounts_id = f.id_flex_num
AND f.application_id = 101
AND f.id_flex_code = 'GL#'
/* B1043070 Changed upper to lower */
AND LOWER(f.segment_name) = LOWER(p.short_name)
AND f.enabled_flag = 'Y'
ORDER BY f.segment_num)
LOOP
Gn_of_seg := Gn_of_seg + 1;
SELECT acctg_unit_no INTO v_acctg_unit_no
FROM gl_accu_mst WHERE acctg_unit_id = p_acctg_unit_id;
SELECT acct_no INTO v_acct_no
FROM gl_acct_mst
WHERE acct_id = p_acct_id;
SELECT segment_delimiter INTO v_segment_delimiter
FROM gl_plcy_mst
WHERE co_code = p_co_code
AND delete_mark = 0;
SELECT map_orgn_ind,
acct_ttl_type
FROM gl_sevt_ttl
WHERE sub_event_type = v_sub_event_type;
SELECT co_code
FROM sy_orgn_mst
WHERE orgn_code = v_org_code;
SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = v_whs_code;
X_sqlstmt := 'SELECT '||'NVL(co_code,'||''''||' '||''''||')'||',orgn_code_pri,
whse_code_pri,icgl_class_pri,
custgl_class_pri,vendgl_class_pri
,item_pri,customer_pri,
vendor_pri,tax_auth_pri,';
' AND delete_mark = 0 ORDER BY 1 desc'; */
' AND (co_code = :pco_code OR co_code IS NULL) AND delete_mark = 0 ORDER BY 1 desc';
/*selecting the acct_id in gl_acct_map
create the order_by based on the priority retrieved above.
company is always the first column selected */
-- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
-- Bug 2423983: 22 to 23
FOR z IN 1..23 LOOP
X_order_by(z) := 0;
X_sqlwhere7:= ' AND delete_mark = 0 ';
/* Changed the selection of acct_id to be the last column to be
selected. If not since the order by is by field position no the
correct acct_id doesnot get picked up if there are more than 1 record */
X_sqlcolumns:= ' SELECT co_code,'||
'nvl(orgn_code,'||''''||' '||''''||'),'||
'nvl(whse_code,'||''''||' '||''''||'),'||
'nvl(icgl_class,'||''''||' '||''''||'),'||
'nvl(custgl_class,'||''''||' '||''''||'),'||
'nvl(vendgl_class,'||''''||' '||''''||'),';
SELECT segment_delimiter
FROM gl_plcy_mst
WHERE co_code = p_co_code
AND delete_mark = 0;
SELECT COUNT(*)
FROM gl_plcy_seg
WHERE co_code = pco_code
AND type = ptype
AND delete_mark = 0;
SELECT segment_delimiter
FROM gl_plcy_mst
WHERE co_code = v_co_code
AND delete_mark = 0;
SELECT COUNT(*)
FROM gl_plcy_seg
WHERE co_code = pco_code
AND delete_mark = 0;
# uom (non-null segment uom) and insert into account master
# Update accu-desc, acct-desc, acct-uom if needed.
################################################################# */
FUNCTION parse_ccid(
pi_co_code IN gl_plcy_mst.co_code%TYPE,
pi_code_combination_id IN NUMBER,
pi_create_acct IN NUMBER DEFAULT 1)
RETURN opm_account
AS
-- Dependencies:
--
-- segment_num order returned by FND_FLEX_EXT api
-- is in ascending order of segment_num in GL
--
-- OPM does not allow account to be defined before accounting units
-- segment_no and segment_ref are display only fields in the application
-- and the segment_no in gl_plcy_seg is always put in ascending order
--
-- Can OPM have a subset of GL segments and others in GL might be
-- disabled?
--
l_code_combination_id NUMBER;
SELECT
segment_no,
short_name,
type,
nvl(segment_ref, 0) segment_ref
FROM gl_plcy_seg
WHERE
co_code = p_opm_company
ORDER BY segment_ref;
SELECT segment_num
FROM fnd_id_flex_segments
WHERE
application_id = p_app_id
AND id_flex_code = p_flex_code
AND id_flex_num = p_chart_of_accounts_id
AND segment_name = p_seg_name;
SELECT
acctg_unit_id, acctg_unit_desc
FROM
gl_accu_mst
WHERE
co_code = p_co_code
AND acctg_unit_no = p_acctg_unit_no;
SELECT
acct_id, acct_desc, quantity_um
FROM
gl_acct_mst
WHERE
co_code = p_co_code
AND acct_no = p_acct_no;
SELECT
sob.chart_of_accounts_id
INTO
l_chart_of_accounts_id
FROM
gl_sets_of_books sob,
gl_plcy_mst plc
WHERE
sob.set_of_books_id = plc.sob_id
AND plc.co_code = l_opm_company;
SELECT segment_delimiter, set_of_books_name
INTO l_opm_delimiter, l_sobname
FROM gl_plcy_mst
WHERE co_code = l_opm_company;
SELECT count(*) INTO l_opm_seg_count
FROM gl_plcy_seg
WHERE co_code = l_opm_company;
SELECT gem5_acctg_id_s.NEXTVAL
INTO l_opm_accu_id
FROM dual;
-- insert the accounting unit into OPM
INSERT INTO gl_accu_mst(
ACCTG_UNIT_ID,
ACCTG_UNIT_NO,
CO_CODE,
ACCTG_UNIT_DESC,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TRANS_CNT,
TEXT_CODE,
DELETE_MARK
)
VALUES
(
l_opm_accu_id,
l_opm_accu,
l_opm_company,
l_opm_accu_desc,
NULL, -- start_date
NULL, -- end_date
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
NULL, -- last_update_login
0, -- trans_cnt
NULL, -- text_code
0 -- delete_mark
);
-- accu found, update desc if necessary
IF( (l_opm_db_accu_desc <> l_opm_accu_desc) OR
(l_opm_db_accu_desc IS NULL AND l_opm_accu_desc IS NOT NULL) OR
(l_opm_db_accu_desc IS NOT NULL AND l_opm_accu_desc IS NULL) )
THEN
UPDATE gl_accu_mst
SET
acctg_unit_desc = l_opm_accu_desc
WHERE
co_code = l_opm_company AND
acctg_unit_id = l_opm_accu_id
;
SELECT gem5_acct_id_s.NEXTVAL
INTO l_opm_acct_id
FROM dual;
-- insert the account into OPM
INSERT INTO gl_acct_mst(
ACCT_ID,
ACCT_NO,
CO_CODE,
ACCT_DESC,
ACCT_TYPE_CODE,
ACCT_CLASS_CODE,
ACCT_USAGE_CODE,
ACCT_BAL_TYPE,
SUMMARY_IND,
QTY_IND,
QUANTITY_UM,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TRANS_CNT,
TEXT_CODE,
DELETE_MARK
)
VALUES
(
l_opm_acct_id,
l_opm_acct,
l_opm_company,
l_opm_acct_desc,
NULL, -- type
NULL, -- class
NULL, -- usage
0, -- acct_bal_type
0, -- summary_ind
0, -- qty_ind
l_acct_uom, -- qty_um
NULL, -- start_date
NULL, -- end_date
SYSDATE, -- creation date
l_user_id, -- created by
SYSDATE, -- last update date
l_user_id, -- last updated by
NULL, -- last update login
0, -- trans cnt
NULL, -- text code
0 -- delete_mark
);
-- acct found. check desc and uom and update if necessary
IF( (l_opm_db_acct_uom <> l_acct_uom) OR
(l_opm_db_acct_uom IS NOT NULL AND l_acct_uom IS NULL) OR
(l_opm_db_acct_uom IS NULL AND l_acct_uom IS NOT NULL) OR
(l_opm_db_acct_desc <> l_opm_acct_desc) OR
(l_opm_db_acct_desc IS NOT NULL AND l_opm_acct_desc IS NULL) OR
(l_opm_db_acct_desc IS NULL AND l_opm_acct_desc IS NOT NULL) )
THEN
UPDATE gl_acct_mst
SET
acct_desc = l_opm_acct_desc,
quantity_um = l_acct_uom
WHERE
co_code = l_opm_company AND
acct_id = l_opm_acct_id
;