[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
# before inserting into po_dist_dtl table
# HISTORY
# 2/17/99 T.Ricci increment var GML_PO_GLDIST.P_tot_amount_aap_aqui
# when calculating an AAP account (was only doing it for AAC)
# Bug820997
##########################################################################*/
FUNCTION calc_dist_amount_aqui RETURN NUMBER AS
/* Cursor for getting orgn. for a particular whse_code.*/
CURSOR Cur_orgn_for_whse IS
SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = GML_PO_GLDIST.P_to_whse;
SELECT std_actual_ind , exp_booked_ind ,
acquis_cost_ind
FROM gl_evnt_plc e, gl_srce_mst s, gl_evnt_mst m
WHERE e.co_code = GML_PO_GLDIST.P_co_code and
e.trans_source_type = s.trans_source_type and e.event_type = m.event_type
and e.trans_source_type = m.trans_source_type
and s.trans_source_code = 'PO' and m.event_code = 'RCPT'
and e.delete_mark = 0;
SELECT cmpntcls_id , analysis_code
FROM po_cost_mst
WHERE aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id;
SELECT incl_ind,cost_amount
FROM po_cost_dtl
WHERE doc_type = GML_PO_GLDIST.P_doc_type and
pos_id = GML_PO_GLDIST.P_pos_id and
line_id = GML_PO_GLDIST.P_line_id and
aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id;
# before inserting into po_dist_dtl table
#
##########################################################################*/
PROCEDURE calc_dist_amount AS
CURSOR Cur_orgn_for_whse IS
SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = GML_PO_GLDIST.P_to_whse;
SELECT std_actual_ind , exp_booked_ind ,
acquis_cost_ind
FROM gl_evnt_plc e, gl_srce_mst s, gl_evnt_mst m
WHERE e.co_code = GML_PO_GLDIST.P_co_code and
e.trans_source_type = s.trans_source_type and e.event_type = m.event_type
and e.trans_source_type = m.trans_source_type
and s.trans_source_code = 'PO' and m.event_code = 'RCPT'
and e.delete_mark = 0;
# 21-JUN-1999 Tony Ricci change order by in select from gl_accu_map
# B931936
# B1377089 RVK 31-Aug-2000 Some of the PO's were not getting
# updated with proper AAP and PPV accts as P_acqui_cost_id was not getting
# initialized. Also subledger update was failing due to uninitialized id
#
############################################################################*/
PROCEDURE receive_data (V_doc_type VARCHAR2, V_pos_id NUMBER,
V_line_id NUMBER, V_orgn_code VARCHAR2,
V_po_date DATE, V_shipvend_id NUMBER,
V_base_currency VARCHAR2,
V_billing_currency VARCHAR2,
V_to_whse VARCHAR2, V_line_no NUMBER,
V_item_no VARCHAR2, V_extended_price NUMBER,
V_project VARCHAR2, V_order_qty1 NUMBER,
V_order_um1 VARCHAR2, V_gl_item_id NUMBER,
V_mul_div_sign NUMBER, V_exchange_rate NUMBER,
V_price NUMBER,V_action NUMBER,
V_Single_aqui BOOLEAN,
retcode IN OUT NOCOPY NUMBER,
V_transaction_type IN VARCHAR2) AS
X_co_code VARCHAR2(5);
SELECT plcy.base_currency_code
FROM sy_orgn_mst orgn, gl_plcy_mst plcy
WHERE orgn.orgn_code = V_orgn_code and orgn.co_code = plcy.co_code;
SELECT co_code
FROM sy_orgn_mst
WHERE orgn_code = V_orgn_code;
SELECT mst.co_code, mst.orgn_code
FROM sy_orgn_mst mst, ic_whse_mst ic
WHERE ic.whse_code = v_to_whse and
mst.orgn_code = ic.orgn_code;
SELECT acctg_unit_id
FROM gl_accu_map
WHERE co_code = X_co_code and
(orgn_code = V_orgn_code or orgn_code IS NULL) and
(whse_code = V_to_whse or whse_code IS NULL) and
delete_mark = 0
order by nvl(orgn_code, ' ') desc, nvl(whse_code, ' ') desc;
SELECT noninv_ind,gl_class
FROM ic_item_mst
WHERE item_id = V_gl_item_id;
SELECT vendgl_class gl_vendorgl_class
FROM po_vend_mst
WHERE vendor_id = V_shipvend_id;
SELECT cmpntcls_id , analysis_code
FROM po_cost_mst
WHERE aqui_cost_id = P_aqui_cost_id;
SELECT aqui_cost_id , cost_amount , incl_ind
FROM po_cost_dtl
WHERE doc_type = P_doc_type and pos_id = P_pos_id and
line_id = P_line_id;
SELECT fiscal_year,period
FROM gl_cldr_dtl
WHERE co_code = P_co_code and
period_end_date >= P_po_date
and delete_mark = 0;
SELECT ledger_code
FROM gl_ledg_map
WHERE co_code = P_co_code and
(orgn_code = P_orgn_code or orgn_code IS NULL) and
delete_mark = 0;
SELECT decimal_precision
FROM gl_curr_mst
WHERE currency_code = V_billing_currency;
/* Each time delete the distributions and recreate them. */
/* B1409258*/
IF V_action = 4
THEN
DELETE po_dist_dtl
WHERE doc_type = P_doc_type
AND DOC_ID = P_pos_id
AND line_id = P_line_id;
/* Select proper acctg_unit_id for each warehouse.*/
OPEN Cur_acctg_unit_id;
/*Added select of gl_class to be passed to mapping PCR 9475*/
OPEN Cur_item_mst;
/* Added select of vendor gl_class to be passed to mapping*/
OPEN Cur_po_vend_mst;
# 21-JUN-1999 Tony Ricci change order by in select from gl_accu_map
# B931936
# 21-JUN-1999 Tony Ricci add login to select and check map_orgn_ind
# B931936
############################################################################*/
FUNCTION get_acctg_unit_no RETURN VARCHAR2 AS
CURSOR Cur_map_orgn_ind IS
SELECT map_orgn_ind
FROM gl_sevt_ttl
WHERE sub_event_type = P_sub_event_type and
acct_ttl_type = P_acct_ttl_num;
SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = P_to_whse;
SELECT acctg_unit_id , orgn_code , whse_code
FROM gl_accu_map
WHERE co_code = P_whse_co_code and
(orgn_code = vc_orgn_code or orgn_code IS NULL) and
(whse_code = P_to_whse or whse_code IS NULL) and
delete_mark = 0
order by nvl(orgn_code, ' ') desc, nvl(whse_code, ' ') desc;
SELECT acctg_unit_no
FROM gl_accu_mst
WHERE acctg_unit_id = P_acctg_unit_id;
SELECT acct_no, acct_desc
FROM gl_acct_mst
WHERE acct_id= P_acct_id;
# pto_currency - the to currency code to select by
# pfrom_currency - the from currency code to select by
# Description
# Retrieves the exchange rate and mul_div_sign based on the parameters
# send in, psource_type _date, pto_currency and pfrom_currency, from
# gl_xchg_rte table. the row selected should be the latest dated
# row.
#
##############################################################################*/
FUNCTION get_exchg_rate(V_psource_type NUMBER, V_po_date DATE,
V_default_currency VARCHAR2 ,V_billing_currency VARCHAR2 )
RETURN NUMBER AS
CURSOR Cur_get_exch_rate IS
SELECT ex.exchange_rate , ex.mul_div_sign,
ex.exchange_rate_date
FROM gl_xchg_rte ex, gl_srce_mst src
WHERE ex.to_currency_code = V_default_currency and
ex.from_currency_code= V_billing_currency and
ex.exchange_rate_date <= V_po_date and
ex.rate_type_code = src.rate_type_code and
src.trans_source_type = V_psource_type and ex.delete_mark=0
order by 3 desc;
SELECT ex.exchange_rate , ex.mul_div_sign,
ex.exchange_rate_date
FROM gl_xchg_rte ex, gl_srce_mst src
WHERE ex.to_currency_code = V_billing_currency and
ex.from_currency_code= V_default_currency and
ex.exchange_rate_date <= V_po_date and
ex.rate_type_code = src.rate_type_code and
src.trans_source_type = V_psource_type and ex.delete_mark=0
order by 3 desc;
X_last_update_date DATE;
X_last_updated_by NUMBER;
X_last_update_login NUMBER;
X_delete_mark NUMBER;
SELECT count (*) from po_dist_dtl
WHERE doc_id = GML_PO_GLDIST.P_pos_id and
line_id = GML_PO_GLDIST.P_line_id and
doc_type = GML_PO_GLDIST.P_doc_type;
X_last_update_date := SYSDATE;
X_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
X_last_update_login := 0;
X_delete_mark := 0;
INSERT INTO PO_DIST_DTL ( DOC_TYPE,
DOC_ID,
LINE_ID,
RECV_SEQ_NO,
SEQ_NO,
AQUI_COST_ID,
ITEM_ID,
ACCTG_UNIT_ID,
ACCT_ID,
ACCT_DESC,
ACCT_TTL_TYPE,
AMOUNT_BASE,
AMOUNT_TRANS,
QUANTITY,
QUANTITY_UM,
PROJECT_NO,
GL_POSTED_IND,
EXPORTED_DATE,
CURRENCY_TRANS,
CURRENCY_BASE,
CO_CODE,
LEDGER_CODE,
FISCAL_YEAR,
PERIOD,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TRANS_CNT,
TEXT_CODE,
DELETE_MARK)
VALUES (GML_PO_GLDIST.P_doc_type,
GML_PO_GLDIST.P_pos_id,
GML_PO_GLDIST.P_line_id,
GML_PO_GLDIST.P_recv_seq_no,
GML_PO_GLDIST.p_row_num,
GML_PO_GLDIST.P_aqui_cost_id,
GML_PO_GLDIST.P_gl_item_id,
GML_PO_GLDIST.P_acctg_unit_id,
GML_PO_GLDIST.P_acct_id,
GML_PO_GLDIST.P_acct_desc,
GML_PO_GLDIST.P_acct_ttl_num,
nvl(X_amount_base,0),
nvl(X_amount_trans,0),
nvl(X_order_qty1,0),
GML_PO_GLDIST.P_order_um1,
GML_PO_GLDIST.P_project,
nvl(GML_PO_GLDIST.P_gl_posted_ind,0),
GML_PO_GLDIST.P_po_date,
GML_PO_GLDIST.P_billing_currency,
GML_PO_GLDIST.P_base_currency,
GML_PO_GLDIST.P_co_code,
GML_PO_GLDIST.P_ledger_code,
GML_PO_GLDIST.P_fiscal_year,
GML_PO_GLDIST.P_period,
X_last_update_date,
X_created_by,
X_creation_date,
X_last_updated_by,
X_last_update_login,
X_trans_cnt,
X_text_code,
X_delete_mark );
GML_PO_GLDIST.update_accounts_orcl( GML_PO_GLDIST.P_pos_id,
GML_PO_GLDIST.P_line_id,
GML_PO_GLDIST.P_orgn_code,
GML_PO_GLDIST.P_acct_ttl_num,
X_combination_id);
UPDATE PO_DIST_DTL SET aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id,
item_id = GML_PO_GLDIST.P_gl_item_id,
acctg_unit_id = GML_PO_GLDIST.P_acctg_unit_id,
acct_id = GML_PO_GLDIST.P_acct_id,
acct_desc = GML_PO_GLDIST.P_acct_desc,
acct_ttl_type = GML_PO_GLDIST.P_acct_ttl_num,
amount_base = nvl(X_amount_base,0),
amount_trans = nvl(X_amount_trans,0),
quantity = nvl(GML_PO_GLDIST.P_order_qty1,0),
quantity_um = GML_PO_GLDIST.P_order_um1,
project_no = GML_PO_GLDIST.P_project,
gl_posted_ind = nvl(GML_PO_GLDIST.P_gl_posted_ind,0),
last_update_date = X_last_update_date,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login
WHERE doc_type = GML_PO_GLDIST.P_doc_type and /* B2237665 */
doc_id = GML_PO_GLDIST.P_pos_id and
line_id = GML_PO_GLDIST.P_line_id and
recv_seq_no = GML_PO_GLDIST.P_recv_seq_no and
acct_ttl_type = GML_PO_GLDIST.P_acct_ttl_num and
seq_no = GML_PO_GLDIST.P_row_num_upd;
/* B1409258 PPB added the above insert statement incase if PO distributions are not created
for PO due to some reason. If the PO is then updated the the correct distributions will be created...
ie program goes to update the po_dist_dtl and finds no record there and then inserts a new if there is
no record. */
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO PO_DIST_DTL ( DOC_TYPE,
DOC_ID,
LINE_ID,
RECV_SEQ_NO,
SEQ_NO,
AQUI_COST_ID,
ITEM_ID,
ACCTG_UNIT_ID,
ACCT_ID,
ACCT_DESC,
ACCT_TTL_TYPE,
AMOUNT_BASE,
AMOUNT_TRANS,
QUANTITY,
QUANTITY_UM,
PROJECT_NO,
GL_POSTED_IND,
EXPORTED_DATE,
CURRENCY_TRANS,
CURRENCY_BASE,
CO_CODE,
LEDGER_CODE,
FISCAL_YEAR,
PERIOD,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TRANS_CNT,
TEXT_CODE,
DELETE_MARK)
VALUES (GML_PO_GLDIST.P_doc_type,
GML_PO_GLDIST.P_pos_id,
GML_PO_GLDIST.P_line_id,
GML_PO_GLDIST.P_recv_seq_no,
GML_PO_GLDIST.p_row_num,
GML_PO_GLDIST.P_aqui_cost_id,
GML_PO_GLDIST.P_gl_item_id,
GML_PO_GLDIST.P_acctg_unit_id,
GML_PO_GLDIST.P_acct_id,
GML_PO_GLDIST.P_acct_desc,
GML_PO_GLDIST.P_acct_ttl_num,
nvl(X_amount_base,0),
nvl(X_amount_trans,0),
nvl(X_order_qty1,0),
GML_PO_GLDIST.P_order_um1,
GML_PO_GLDIST.P_project,
nvl(GML_PO_GLDIST.P_gl_posted_ind,0),
GML_PO_GLDIST.P_po_date,
GML_PO_GLDIST.P_billing_currency,
GML_PO_GLDIST.P_base_currency,
GML_PO_GLDIST.P_co_code,
GML_PO_GLDIST.P_ledger_code,
GML_PO_GLDIST.P_fiscal_year,
GML_PO_GLDIST.P_period,
X_last_update_date,
X_created_by,
X_creation_date,
X_last_updated_by,
X_last_update_login,
X_trans_cnt,
X_text_code,
X_delete_mark );
GML_PO_GLDIST.update_accounts_orcl( GML_PO_GLDIST.P_pos_id,
GML_PO_GLDIST.P_line_id,
GML_PO_GLDIST.P_orgn_code,
GML_PO_GLDIST.P_acct_ttl_num,
X_combination_id);
SELECT std_actual_ind,
exp_booked_ind,
acquis_cost_ind
FROM gl_evnt_plc e, gl_srce_mst s, gl_evnt_mst m
WHERE e.co_code = v_co_code
AND e.trans_source_type = s.trans_source_type
AND e.event_type = m.event_type
AND e.trans_source_type = m.trans_source_type
AND s.trans_source_code = 'PO'
AND m.event_code = 'RCPT'
AND e.delete_mark = 0;
SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = v_to_whse
AND delete_mark = 0;
# If :system.record_status in INSERT mode, we call this procedure from
# popaced2, for each of the record in the INSERT status.
#
##########################################################################*/
PROCEDURE poglded2_check_new_aqui(retcode IN OUT NOCOPY NUMBER) AS
CURSOR Cur_po_cost_mst IS
SELECT cmpntcls_id , analysis_code
FROM po_cost_mst
WHERE aqui_cost_id = GML_PO_GLDIST.P_aqui_cost_id;
SELECT aqui_cost_id , cost_amount , incl_ind, delete_mark
FROM po_cost_dtl
WHERE doc_type = GML_PO_GLDIST.P_doc_type and
pos_id = GML_PO_GLDIST.P_pos_id and
line_id = GML_PO_GLDIST.P_line_id;
/* Sandeep. 11.Nov.98. This procedure modified to delete the existing aqui*/
/* rows and re-post the same in Update mode.*/
GML_PO_GLDIST.delete_aqui_costs;
GML_PO_GLDIST.P_delete_mark := Rec.delete_mark;
# delete_aqui_costs
#
# DESCRIPTION
# This Procedure deletes the existing Aquisition costs, when made a
# modification in the Query mode. In short, it would delete the existing
# Aquisition costs and re-post the data again.
# HISTORY
# created by Sandeep 12.Oct.1998
##########################################################################*/
PROCEDURE delete_aqui_costs IS
BEGIN
DELETE FROM PO_DIST_DTL
WHERE nvl(aqui_cost_id,0) > 0 and
doc_id = GML_PO_GLDIST.P_pos_id and
line_id = GML_PO_GLDIST.P_line_id and
doc_type = GML_PO_GLDIST.P_doc_type ;
END delete_aqui_costs;
X_syarg18 NUMBER(15) DEFAULT 0; /* LAST_UPDATE_LOGIN*/
SELECT set_of_books_name
FROM gl_plcy_mst
WHERE co_code = V_co_code;
/* Select set of books name from fiscal policy.*/
/*This function will be called from short_name procedure.*/
IF V_co_code IS NOT NULL THEN
OPEN Cur_gl_plcy_mstc1;
select chart_of_accounts_id
from gl_plcy_mst,gl_sets_of_books
where co_code = P_CO_CODE
and name like set_of_books_name
and set_of_books_id = sob_id;
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;
/* SR dt 25-Jan-2001 B1530509 added select to get segment delimiter */
SELECT segment_delimiter INTO v_segment_delimiter
FROM gl_plcy_mst
WHERE co_code = p_co_code
AND delete_mark = 0;
* OPM and GL. Select clause and order by clause has been changed.
******************************************************************************/
PROCEDURE parse_account( v_co_code IN VARCHAR2,
v_account IN VARCHAR2,
v_type IN NUMBER,
v_offset IN NUMBER,
v_segment IN OUT NOCOPY fnd_flex_ext.SegmentArray,
V_no_of_seg IN OUT NOCOPY NUMBER )
IS
/*Begin Bug#2376340 Piyush K. Mishra
Changed the cursor query.*/
CURSOR cur_plcy_seg IS
SELECT p.type, p.length,
--nvl(substrb(f.application_column_name,8),0) segment_ref, (Commented and added following for B#2376340)
f.segment_num segment_ref,
pm.segment_delimiter
FROM gl_plcy_seg p,
gl_plcy_mst pm,
fnd_id_flex_segments f,
gl_sets_of_books s
WHERE p.co_code = v_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#'
AND LOWER(f.segment_name) = LOWER(p.short_name)
AND f.enabled_flag = 'Y'
ORDER BY p.segment_no;
* update_accounts_orcl
* SYNOPSIS
* proc update_accounts_orcl
* RETURNS
* This procedure will update the ORacle Fianancial table
* po_distributions_all table with the correct accounts combination id
* for Purchase Price Varaince (PPV ) and Accrued Accounts Payable(AAP)
* GLOBAL VARIABLES
*
*
* DESCRIPTION
*
*
* HISTORY
*02/28/00 Preetam Bamb
*04/12/00 nchekuri Added Cursors inplace of direct select statements 'coz it was causing problems.
*03/27/03 Bug 1994882 Get ccid if null value is passed.
*******************************************************************************/
PROCEDURE update_accounts_orcl( v_po_id IN NUMBER,
v_line_id IN NUMBER,
v_orgn_code IN VARCHAR2,
v_acct_ttl_num IN NUMBER,
v_combination_id IN NUMBER)
IS
v_po_header_id NUMBER;
SELECT po_header_id,po_line_id,po_line_location_id
FROM cpg_oragems_mapping
WHERE po_id = v_po_id AND
line_id = v_line_id;
SELECT po_header_id, po_line_id, po_line_location_id, po_release_id
FROM cpg_oragems_mapping
WHERE po_id = v_po_id AND
line_id = v_line_id;
SELECT po_header_id, po_line_id, po_line_location_id, po_release_id
FROM cpg_oragems_mapping
WHERE po_id = v_po_id AND line_id = v_line_id;
SELECT cc.code_combination_id
FROM gl_code_combinations_kfv cc,
cpg_oragems_mapping map,
gl_accu_mst acu,
gl_acct_mst act,
po_dist_dtl pdd,
po_distributions_all pod,
gl_plcy_mst gpm,
gl_sets_of_books gsob
WHERE map.po_id = v_po_id
AND map.line_id = v_line_id
AND pod.po_header_id = map.po_header_id
AND pod.po_line_id = map.po_line_id
AND pod.line_location_id = map.po_line_location_id
AND NVL(pod.po_release_id,-1) = NVL(map.po_release_id,-1)
AND map.po_id = pdd.doc_id
AND map.line_id = pdd.line_id
AND pdd.acct_ttl_type = v_act_ttl_typ
AND pdd.acctg_unit_id = acu.acctg_unit_id
AND pdd.acct_id = act.acct_id
AND cc.concatenated_segments = acu.acctg_unit_no||gpm.SEGMENT_DELIMITER||act.acct_no
AND cc.chart_of_accounts_id = gsob.CHART_OF_ACCOUNTS_ID
AND gsob.name = gpm.set_of_books_name
AND gsob.set_of_books_id = gpm.sob_id
AND gpm.co_code = pdd.co_code;
update po_distributions_all
set variance_account_id = x_combination_id
where po_header_id = v_po_header_id
and po_line_id = v_po_line_id
and line_location_id = v_po_line_location_id;
update po_distributions_all
set accrual_account_id = x_combination_id
where po_header_id = v_po_header_id
and po_line_id = v_po_line_id
and line_location_id = v_po_line_location_id;
update po_distributions_all
set variance_account_id = x_combination_id
where po_header_id = v_po_header_id
and po_line_id = v_po_line_id
and line_location_id = v_po_line_location_id
and po_release_id = v_po_release_id;
update po_distributions_all
set accrual_account_id = x_combination_id
where po_header_id = v_po_header_id
and po_line_id = v_po_line_id
and line_location_id = v_po_line_location_id
and po_release_id = v_po_release_id;
update po_distributions_all
set variance_account_id = x_combination_id
where po_header_id = v_po_header_id
and po_line_id = v_po_line_id
and line_location_id = v_po_line_location_id
and po_release_id = v_po_release_id;
update po_distributions_all
set accrual_account_id = x_combination_id
where po_header_id = v_po_header_id
and po_line_id = v_po_line_id
and line_location_id = v_po_line_location_id
and po_release_id = v_po_release_id;
end update_accounts_orcl;