[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_flag
FROM mtl_system_items
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_item_id;
SELECT max(fin_year) fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
SELECT excise_duty_range, excise_duty_division
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id;
SELECT nvl(fcl.precision,0)
-- FROM fnd_currencies_vl fcl
FROM fnd_currencies fcl
WHERE fcl.currency_code = 'INR'
AND NVL(fcl.enabled_flag, 'N') = 'Y'
AND NVL(fcl.currency_flag, 'N') = 'Y'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE ) >= SYSDATE;
SELECT concatenated_segments
FROM gl_code_combinations_kfv
WHERE code_combination_id = cp_code_combination_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_uom;
SELECT master_org_flag
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
FOR r_boe IN (SELECT boe_id FROM JAI_CMN_BOE_MATCHINGS
WHERE transaction_id = p_transaction_id)
LOOP
IF NVL(length(lv_boe_no), 0) <= 135 THEN
lv_boe_no := lv_boe_no||to_char(r_boe.boe_id)||'/';
SELECT register_id FROM JAI_CMN_RG_23AC_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = cp_register_type
AND inventory_item_id = p_inventory_item_id
AND fin_year = cp_fin_year
AND slno = (select max(slno) from JAI_CMN_RG_23AC_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = cp_register_type
AND inventory_item_id = p_inventory_item_id
AND fin_year = cp_fin_year);
SELECT register_id FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND fin_year = cp_fin_year
AND slno = (select max(slno) from JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND fin_year = cp_fin_year);
SELECT register_id FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND fin_year = cp_fin_year
AND slno = (select max(slno) from JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND fin_year = cp_fin_year);
SELECT register_id FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = cp_register_type
AND fin_year = cp_fin_year
AND slno = (select max(slno) from JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = cp_register_type
AND fin_year = cp_fin_year);
SELECT register_id FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND slno = (select max(slno) from JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year);
PROCEDURE update_rg_balances(
p_organization_id IN NUMBER,
p_location_id IN NUMBER,
p_register IN VARCHAR2,
p_amount IN NUMBER,
p_transaction_source IN VARCHAR2,
p_called_from IN VARCHAR2
) IS
ln_rg23a_amount NUMBER;
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_general_pkg.update_rg_balances';
UPDATE JAI_CMN_RG_BALANCES
SET rg23a_balance = nvl(rg23a_balance,0) + ln_rg23a_amount,
rg23c_balance = nvl(rg23c_balance,0) + ln_rg23c_amount,
pla_balance = nvl(pla_balance,0) + ln_pla_amount
WHERE organization_id = p_location_id
AND location_id = p_location_id;
END update_rg_balances;
SELECT NVL(cust_acct_site_id, 0) address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
WHERE A.site_use_id = NVL(p_party_site_id,0);
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code
FROM
JAI_CMN_CUS_ADDRESSES a,
qp_list_lines b,
qp_pricing_attributes c
WHERE
a.customer_id = p_party_id AND
a.address_id = p_address_id AND
a.vat_price_list_id = b.LIST_header_ID AND
c.list_line_id = b.list_line_id AND
c.product_attr_value = to_char(p_inventory_item_id) AND
c.product_uom_code = p_uom_code AND
p_ordered_date BETWEEN nvl( start_date_active, p_ordered_date) AND
nvl( end_date_active, SYSDATE);
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code
FROM
JAI_CMN_CUS_ADDRESSES a,
qp_list_lines b,
qp_pricing_attributes c
WHERE
a.customer_id = p_party_id AND
a.address_id = p_address_id AND
a.vat_price_list_id = b.list_header_id AND
c.list_line_id = b.list_line_id AND
c.product_attr_value = to_char(p_inventory_item_id) AND
trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
nvl(primary_uom_flag,'N') ='Y';
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code
FROM
JAI_CMN_CUS_ADDRESSES a,
qp_list_lines b,
qp_pricing_attributes c
WHERE
a.customer_id = p_party_id AND
a.address_id = p_address_id AND
a.vat_price_list_id = b.LIST_header_ID AND
c.list_line_id = b.list_line_id AND
c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) AND
NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
qp_list_lines b,
qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE
c.list_line_id = b.list_line_id AND
c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
c.product_uom_code = p_uom_code AND
qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
p_ordered_date BETWEEN nvl( b.start_date_active, p_ordered_date)
AND nvl( b.end_date_active, SYSDATE) AND
EXISTS ( Select 1
from qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
where qlh.list_header_id = b.list_header_id
and a.customer_id = p_party_id
AND a.address_id = p_address_id
AND a.vat_price_list_id = b.LIST_header_ID
and p_ordered_date BETWEEN nvl( qlh.start_date_active, p_ordered_date)
AND nvl( qlh.end_date_active, SYSDATE)
and nvl(qlh.active_flag,'N') = 'Y');
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
qp_list_lines b,
qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE
c.list_line_id = b.list_line_id AND
c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
nvl(qlhb.active_flag,'N') = 'Y' AND /*added for bug#16288090*/
exists ( select 1
from qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
where a.customer_id = p_party_id AND
a.address_id = p_address_id AND
qlh.list_header_id = b.list_header_id AND
a.vat_price_list_id = b.list_header_id AND
trunc(nvl(qlh.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
nvl(qlh.active_flag,'N') = 'Y' ) AND
nvl(primary_uom_flag,'N') ='Y';
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
qp_list_lines b,
qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE
c.list_line_id = b.list_line_id AND
c.PRODUCT_ATTR_VALUE = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
NVL(b.end_date_active,SYSDATE) >= p_ordered_date AND
EXISTS ( select 1
from qp_list_headers qlh, JAI_CMN_CUS_ADDRESSES a
WHERE a.customer_id = p_party_id AND
a.address_id = p_address_id AND
qlh.list_header_id = b.list_header_id AND
a.vat_price_list_id = b.LIST_header_ID AND
NVL(qlh.end_date_active,SYSDATE) >= p_ordered_date AND
NVL( qlh.active_flag,'N') = 'Y' );
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code,
qlhb.currency_code /* Added for bug#16288090 */
FROM
JAI_CMN_VENDOR_SITES a,
qp_list_lines b,
qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE
a.vendor_id = p_vendor_id AND
a.vendor_site_id = p_address_id AND
a.vat_price_list_id = b.LIST_header_ID AND
c.list_line_id = b.list_line_id AND
c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
c.product_uom_code = p_uom_code AND
p_ordered_date BETWEEN nvl( B.start_date_active, p_ordered_date) AND
nvl( b.end_date_active, SYSDATE);
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code,
qlhb.currency_code /* Added for bug#16288090 */
FROM
JAI_CMN_VENDOR_SITES a,
qp_list_lines b,
qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE
a.vendor_id = p_vendor_id AND
a.vendor_site_id = p_address_id AND
a.vat_price_list_id = b.list_header_id AND
c.list_line_id = b.list_line_id AND
c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,to_char(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
nvl(primary_uom_flag,'N') ='Y';
SELECT
b.operand list_price,
c.product_uom_code list_price_uom_code,
qlhb.currency_code /* Added for bug#16288090 */
FROM
JAI_CMN_VENDOR_SITES a,
qp_list_lines b,
qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE
a.vendor_id = p_vendor_id AND
a.vendor_site_id = p_address_id AND
a.vat_price_list_id = b.LIST_header_ID AND
c.list_line_id = b.list_line_id AND
c.PRODUCT_ATTR_VALUE = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(p_inventory_item_id)) AND--Modified by Junjian on 15-Oct-2012 for bug#14736812
qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
SELECT
category_set_name
FROM
mtl_default_category_sets_fk_v
WHERE functional_area_desc = 'Order Entry';
SELECT
b.operand list_price
, c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
jai_cmn_cus_addresses a
, qp_list_lines b
, qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE a.customer_id = pn_party_id
AND a.address_id = pn_address_id
AND a.vat_price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND c.product_uom_code = pv_uom_code
AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#16288090*/
pd_ordered_date BETWEEN NVL( b.start_date_active, pd_ordered_date)
AND NVL( b.end_date_active, SYSDATE)
AND EXISTS ( SELECT
'x'
FROM
mtl_item_categories_v d
WHERE d.category_set_name = lv_category_set_name
AND d.inventory_item_id = pn_inventory_item_id
AND c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
);
SELECT
b.operand list_price
, c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
jai_cmn_cus_addresses a
, qp_list_lines b
, qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE a.customer_id = pn_party_id
AND a.address_id = pn_address_id
AND a.vat_price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
AND NVL(primary_uom_flag,'N') ='Y'
AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
AND EXISTS ( SELECT
'x'
FROM
mtl_item_categories_v d
WHERE d.category_set_name = lv_category_set_name
AND d.inventory_item_id = pn_inventory_item_id
AND c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
);
SELECT
b.operand list_price
, c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
jai_cmn_cus_addresses a
, qp_list_lines b
, qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE a.customer_id = pn_party_id
AND a.address_id = pn_address_id
AND a.vat_price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
AND EXISTS ( SELECT
'x'
FROM
mtl_item_categories_v d
WHERE d.category_set_name = lv_category_set_name
AND d.inventory_item_id = pn_inventory_item_id
AND c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
);
SELECT
b.operand list_price
, c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
jai_cmn_vendor_sites a
, qp_list_lines b
, qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE a.vendor_id = pn_vendor_id
AND a.vendor_site_id = pn_address_id
AND a.vat_price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND c.product_uom_code = pv_uom_code
AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
AND pd_ordered_date BETWEEN NVL( b.start_date_active, pd_ordered_date)
AND NVL( b.end_date_active, SYSDATE)
AND EXISTS ( SELECT
'x'
FROM
mtl_item_categories_v d
WHERE d.category_set_name = lv_category_set_name
AND d.inventory_item_id = pn_inventory_item_id
AND c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
);
SELECT
b.operand list_price
, c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
jai_cmn_vendor_sites a
, qp_list_lines b
, qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE a.vendor_id = pn_vendor_id
AND a.vendor_site_id = pn_address_id
AND a.vat_price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
AND NVL(primary_uom_flag,'N') ='Y'
AND EXISTS ( SELECT
'x'
FROM
mtl_item_categories_v d
WHERE d.category_set_name = lv_category_set_name
AND d.inventory_item_id = pn_inventory_item_id
AND c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
);
SELECT
b.operand list_price
, c.product_uom_code list_price_uom_code ,
qlhb.currency_code /* Added for bug#16288090 */
FROM
jai_cmn_vendor_sites a
, qp_list_lines b
, qp_pricing_attributes c,
qp_list_headers_b qlhb /* Added for bug#16288090 */
WHERE a.vendor_id = pn_vendor_id
AND a.vendor_site_id = pn_address_id
AND a.vat_price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND qlhb.list_header_id = b.list_header_id AND /* Added for bug#16288090 */
NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date AND
NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#16288090*/
AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
AND EXISTS ( SELECT
'x'
FROM
mtl_item_categories_v d
WHERE d.category_set_name = lv_category_set_name
AND d.inventory_item_id = pn_inventory_item_id
AND c.product_attr_value = decode(c.product_attr_value,'ALL',
c.product_attr_value,TO_CHAR(d.category_id)) --Modified by Junjian on 15-Oct-2012 for bug#14736812
);