[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM JAI_AP_TOL_SETUPS_ALL
where org_id = p_org_id;
SELECT 1
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND vat_code = cp_vat_code
AND rownum = 1;
SELECT SUM(ail.amount) line_amount
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = inv_id
AND EXISTS (SELECT 1
FROM ap_invoice_distributions_all apid
WHERE apid.invoice_line_number = ail.line_number
AND apid.invoice_id = inv_id
);
SELECT least(h_amt*NVL(tolerance_pos_percent,0)/100, NVL(tolerance_pos_amt,0)) max_val,
least(h_amt*NVL(tolerance_neg_percent,0)/100, NVL(tolerance_neg_amt,0)) min_val
FROM JAI_AP_TOL_SETUPS_ALL;
SELECT distribution_line_number, set_of_books_id
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND invoice_line_number = cp_invoice_line_number
AND line_type_lookup_code = cp_line_type
AND description = cp_description
AND rownum = 1;
SELECT line_number, set_of_books_id
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id
AND line_type_lookup_code =cp_line_type
AND description = cp_description
AND rownum = 1;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = sob_id;
insertion_amount NUMBER := 0;
ln_login_id AP_INVOICE_LINES_ALL.LAST_UPDATE_LOGIN%TYPE;
SELECT max (line_number)
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
SELECT accounting_date
,period_name
,deferred_acctg_flag
,def_acctg_start_date
,def_acctg_end_date
,def_acctg_number_of_periods
,def_acctg_period_type
,set_of_books_id
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_number = cpn_max_line_num;
SELECT a.accrual_posted_flag,
a.assets_addition_flag,
a.assets_tracking_flag
FROM ap_invoice_distributions_all a,
ap_invoice_lines_all b
WHERE a.invoice_id = b.invoice_id
and a.invoice_line_number = b.line_number
and a.invoice_id = cp_invoice_id
AND b.line_type_lookup_code = 'ITEM';
UPDATE ap_invoice_distributions_all
SET amount = 0
WHERE invoice_id = p_invoice_id
AND vat_code = lv_vat_code
RETURNING invoice_line_number INTO ln_invc_line_num;
UPDATE ap_invoice_lines_all
SET amount = 0
WHERE invoice_id = p_invoice_id
AND line_number = ln_invc_line_num;
P_return_message := ' Updated existing rounding line';
--INSERT POSITIVE OR NEGATIVE DISTRIBUTION EQUAL TO DIFF_AMOUNT
insertion_amount := (-1)*diff_amount;
IF insertion_amount <> 0 THEN
lv_not_required := 'NOT REQUIRED';
UPDATE ap_invoice_lines_all
SET amount = insertion_amount,
last_update_date = sysdate,
last_updated_by = ln_user_id,
last_update_login = ln_login_id
WHERE invoice_id =p_invoice_id
AND line_number = ln_invc_line_num ;
INSERT INTO ap_invoice_lines_all
(
INVOICE_ID
, LINE_NUMBER
, LINE_TYPE_LOOKUP_CODE
, DESCRIPTION
, ORG_ID
, MATCH_TYPE
, ACCOUNTING_DATE
, PERIOD_NAME
, DEFERRED_ACCTG_FLAG
, DEF_ACCTG_START_DATE
, DEF_ACCTG_END_DATE
, DEF_ACCTG_NUMBER_OF_PERIODS
, DEF_ACCTG_PERIOD_TYPE
, SET_OF_BOOKS_ID
, AMOUNT
, WFAPPROVAL_STATUS
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES
(
p_invoice_id
, ln_invc_line_num
, lv_misc
, lv_description
, p_org_id
, lv_not_matched
, rec_max_ap_lines_all.accounting_date
, rec_max_ap_lines_all.period_name
, rec_max_ap_lines_all.deferred_acctg_flag
, rec_max_ap_lines_all.def_acctg_start_date
, rec_max_ap_lines_all.def_acctg_end_date
, rec_max_ap_lines_all.def_acctg_number_of_periods
, rec_max_ap_lines_all.def_acctg_period_type
, rec_max_ap_lines_all.set_of_books_id
, insertion_amount
, lv_not_required
, sysdate
, ln_user_id
, ln_user_id
, sysdate
, ln_login_id
);
fnd_file.put_line(FND_FILE.LOG,' Error while inserting JAI Rounding
adjustment in ail'|| SQLERRM);
UPDATE ap_invoice_distributions_all
SET amount = insertion_amount,
last_update_date = sysdate,
last_updated_by = ln_user_id,
last_update_login = ln_login_id
WHERE invoice_id =p_invoice_id
AND distribution_line_number = from_inv_dist_rec.distribution_line_number
AND invoice_line_number =ln_invc_line_num ;
INSERT INTO ap_invoice_distributions_all
(
accounting_date,
accrual_posted_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
invoice_id,
last_updated_by,
last_update_date,
line_type_lookup_code,
period_name,
set_of_books_id,
amount,
base_amount,
created_by,
creation_date,
description,
last_update_login,
posted_flag,
reversal_flag,
vat_code,
invoice_distribution_id,
org_id,
dist_match_type,
invoice_line_number
)
VALUES
(
rec_max_ap_lines_all.accounting_date,
r_invoice_distribution.accrual_posted_flag,
r_invoice_distribution.assets_addition_flag,
r_invoice_distribution.assets_tracking_flag,
lv_cash_posted_flag,
ln_distribution_line_num ,
set_up_values_rec.tolerance_charge_account_id,
p_invoice_id,
ln_user_id,
sysdate,
lv_misc,
rec_max_ap_lines_all.period_name,
rec_max_ap_lines_all.set_of_books_id,
insertion_amount,
insertion_amount,
ln_user_id,
sysdate,
lv_description, /* 'Commercial Rounding Off Distribution', Ramananda for removal of SQL LITERALs */
ln_login_id,
'N',
'N',
lv_vat_code, /* 'ROUNDING', Ramananda for removal of SQL LITERALs */
ap_invoice_distributions_s.nextval,
p_org_id
,lv_not_matched
,ln_invc_line_num
);
fnd_file.put_line(FND_FILE.LOG,' Error while inserting JAI Rounding adjustment in aid'|| SQLERRM);