[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select manufacturing , trading
from jai_cmn_inventory_orgs
where organization_id = cp_organization_id
and location_id = cp_location_id;
select name
from oe_transaction_types_tl
where transaction_type_id = p_order_invoice_type_id;
Select name
from ra_batch_sources_all
where batch_source_id = p_order_invoice_type_id;
select a.register_code
from JAI_OM_OE_BOND_REG_HDRS a , JAI_OM_OE_BOND_REG_DTLS b
where a.organization_id = p_orgn_id
and a.location_id = p_locn_id
and a.register_id = b.register_id
and b.order_flag = p_order_flag
and b.order_type_id = p_order_invoice_type_id;
SELECT meaning
FROM ja_lookups
WHERE lookup_type = cp_lookup_type --'JAI_REGISTER_TYPE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND lookup_code = v_lookup_code;
Select start_number , prefix , jump_by , end_number , order_invoice_type, register_code /*Column order_invoice_type added for bug # 13854640*/
From JAI_CMN_RG_EXC_INV_NOS
where organization_id = p_orgn_id
and location_id = p_loc_id
and fin_year = p_fin_year
and (order_invoice_type= v_order_type_dom_exp OR order_invoice_type = v_order_type) -- Added by nprashar for bug#13854640
and (register_code = v_register_meaning_dom_exp OR register_code = v_register_meaning) -- Added by nprashar for bug#13854640
for update;
SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Tr_Ec_Code IN
(
SELECT B.Tr_Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = p_organization_id
AND B.Location_Id = p_location_id
);
SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Ec_Code IN
(
SELECT B.Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = p_organization_id
AND B.Location_Id = p_location_id
)
AND nvl(EXCISE_INVNO_AT_EC_CODE, 'N') = 'Y' /*Added by nprashar for bug # 7319628*/;
Select nvl(gp1,0) , nvl(gp2,0)
From JAI_CMN_RG_EXC_INV_NOS
where organization_id = p_orgn_id
and location_id = p_loc_id
and fin_year = p_fin_year
AND order_invoice_type IS NULL
AND register_code IS NULL;
Select ec_code , master_org_flag , master_organization_id, EXCISE_INVNO_AT_EC_CODE
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_location_id;
select organization_id , EXCISE_INVNO_AT_EC_CODE , location_id
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_orgn_id
and ec_code = p_ec_code
and master_org_flag = 'Y';
Select tr_ec_code , master_org_flag , master_organization_id
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_location_id;
select organization_id , location_id
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_orgn_id
and tr_ec_code = p_ec_code
and master_org_flag = 'Y';
2) Added if condition to update table JAI_CMN_RG_EXC_INV_NOS for the records where EXCISE_INVNO_AT_EC_CODE = 'N'
for the manufacturing organizations because such records will not be fetched by cursor c_ec_code_cur so invoice number
will not be updated to next number for that organization.
3) FP of bug#7505975
Issue: Excise Invoice Number for a Trading organization with Master-- Child setup
is not using its Master Organization Excise sequence defined.
Fix: Changes are done to ensure that Trading Org with master- child relation ship will use the
master org excise sequence.
vkaranam for bug#11886787
New excise invoice will be generated for inteorg with the following combination
Transaction_type : INTERORG TRANSFER
Order/Invoice Type: DOMESTIC
Register_code : DOMESTIC_EXCISE
if the setup is not there the old setup with "DOMESTIC"/"DOMESTIC" will be there .
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
--------------------------------------------------------------------------------------------------------------
Version Bug Dependencies
---------------------------------------------------------------------------------------------------------------
616.1 3071342 IN60104D1
619.1 3439480 No dependencies introduced - IN60105D2
*************************************************************************************************************/
fnd_file.put_line(fnd_file.log,'Starting Excise Invoice Generation Prg');--bug#7719911
Fnd_File.PUT_LINE(Fnd_File.LOG,'Before update time is ' || to_char(sysdate,'dd-mon-yyyy hh:mi:ss'));
update JAI_CMN_RG_EXC_INV_NOS
set last_update_date = last_update_date
where fin_year = p_fin_year
AND order_invoice_type IS NULL
AND register_code IS NULL
and (organization_id, location_id)
in
(SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Ec_Code IN
(
SELECT B.Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = v_act_organization_id
AND B.Location_Id = v_act_location_id
)
);
Fnd_File.PUT_LINE(Fnd_File.LOG,'After update time is ' || to_char(sysdate,'dd-mon-yyyy hh:mi:ss'));
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET start_number = v_start_number,
last_update_date = sysdate
WHERE organization_id = v_act_organization_id
AND location_id = v_act_location_id
AND fin_year = p_fin_year
-- AND order_invoice_type = v_order_type) Added by nprashar for bug#13854640
AND order_invoice_type = v_order_type_temp -- Added by nprashar for bug#13854640
-- AND register_code = v_register_meaning Added by nprashar for bug#13854640
AND register_code = v_register_code_temp -- Added by nprashar for bug#13854640
AND transaction_type = v_trans_type_up;
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET start_number = v_start_number
WHERE organization_id = v_act_organization_id
AND location_id = v_act_location_id
AND fin_year = p_fin_year
-- AND order_invoice_type = v_order_type) Added by nprashar for bug#13854640
AND order_invoice_type = v_order_type_temp -- Added by nprashar for bug#13854640
-- AND register_code = v_register_meaning Added by nprashar for bug#13854640
AND register_code = v_register_code_temp; -- Added by nprashar for bug#13854640
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET start_number = v_start_number
WHERE organization_id = master_org_rec.organization_id
AND location_id = master_org_rec.location_id
AND fin_year = p_fin_year
-- AND order_invoice_type = v_order_type) Added by nprashar for bug#13854640
AND order_invoice_type = v_order_type_temp -- Added by nprashar for bug#13854640
-- AND register_code = v_register_meaning Added by nprashar for bug#13854640
AND register_code = v_register_code_temp; -- Added by nprashar for bug#13854640
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET start_number = v_start_number
WHERE organization_id = v_act_organization_id
AND location_id = v_act_location_id
AND fin_year = p_fin_year
AND order_invoice_type = v_order_type_temp
AND register_code = v_register_code_temp;
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET start_number = v_start_number
WHERE organization_id = master_org_rec.organization_id
AND location_id = master_org_rec.location_id
AND fin_year = p_fin_year
AND order_invoice_type = v_order_type_temp
AND register_code = v_register_code_temp;
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET gp1 = v_gp1,
gp2 = v_gp2
WHERE organization_id = master_org_rec.organization_id
AND location_id = master_org_rec.location_id
AND fin_year = p_fin_year
AND order_invoice_type IS NULL
AND register_code IS NULL;
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET gp1 = v_gp1,
gp2 = v_gp2
WHERE organization_id =v_act_organization_id
AND location_id = v_act_location_id
AND fin_year = p_fin_year
AND order_invoice_type IS NULL
AND register_code IS NULL;
UPDATE JAI_CMN_RG_EXC_INV_NOS
SET gp1 = v_gp1,
gp2 = v_gp2
WHERE organization_id = master_org_rec.organization_id
AND location_id = master_org_rec.location_id
AND fin_year = p_fin_year
AND order_invoice_type IS NULL
AND register_code IS NULL;
Select Order_Type_code
from op_ordr_typ a , op_ordr_hdr b
Where UPPER(a.order_type) = UPPER(b.order_type)
and b.order_id = P_Ordid ;
SELECT a.register_code
FROM JAI_OM_OE_BOND_REG_HDRS a, JAI_OM_OE_BOND_REG_DTLS b,sy_orgn_mst c
WHERE a.organization_id = c.organization_id
AND UPPER(c.orgn_code) = UPPER(s_orgn_code) ------org_changed
AND a.register_id = b.register_id
AND b.order_flag = 'Y'
AND b.order_type_id = (select order_type From Op_Ordr_Hdr where Order_id =
(select order_id From JAI_OPM_SO_PICK_LINES
where bol_id = P_BOL_ID
and bolline_no = P_BOLLINE_NO ));
SELECT a.register_code
FROM JAI_OM_OE_BOND_REG_HDRS a, JAI_OM_OE_BOND_REG_DTLS b,org_organization_definitions c
WHERE a.organization_id = c.organization_id
AND UPPER(c.organization_code) = UPPER(s_orgn_code) ------org_changed
AND a.register_id = b.register_id
AND b.order_flag = 'Y'
AND b.order_type_id = (select order_type From Op_Ordr_Hdr where Order_id =
(select order_id From JAI_OPM_SO_PICK_LINES
where bol_id = P_BOL_ID
and bolline_no = P_BOLLINE_NO ));
Select meaning
From ja_lookups
Where UPPER(lookup_code) LIKE UPPER(p_register_code)
And UPPER(lookup_type) = cp_register_type ; --UPPER('JAI_REGISTER_TYPE'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980
SELECT NVL(MAX(DOM_NUMBER),0),NVL(MAX(EXP_NUMBER),0)
FROM JAI_OPM_EXCISE_INV_NOS
WHERE UPPER(orgn_code) = UPPER(s_orgn_code) ---org_changed
AND fin_year = p_fin_year
AND order_invoice_type IS Null
AND register_code IS Null;
SELECT MAX(a.fin_year)
FROM JAI_CMN_FIN_YEARS a ,sy_orgn_mst b
WHERE a.organization_id = b.organization_id
and UPPER(b.orgn_code) = UPPER(s_orgn_code) -----org_changed
and a.fin_active_flag = 'Y';
SELECT MAX(a.fin_year)
FROM JAI_CMN_FIN_YEARS a ,org_organization_definitions b
WHERE a.organization_id = b.organization_id
and upper(b.organization_code) = upper(s_orgn_code)
and a.fin_active_flag = 'Y';
Select start_number, end_number, jump_by, prefix
From JAI_OPM_EXCISE_INV_NOS
Where UPPER(orgn_code) = UPPER(s_orgn_code) ---org_changed
And fin_year = p_fin_year
And UPPER(order_invoice_type) = UPPER(p_batch_name)
And UPPER(register_code) = UPPER(p_register_code);
v_last_update_date CONSTANT Date := SYSDATE; --Added CONSTANT Ramananda for File.Sql.35
v_last_updated_by Number := 1774 ;
v_last_update_login Number := 233965 ;
SELECT FROM_WHSE
FROM OP_ORDR_HDR
WHERE ORDER_ID = P_Ordid ;
SELECT LOCATION_ID
FROM HR_LOCATIONS
WHERE UPPER(LOCATION_CODE) = UPPER(v_From_Whse) ;
SELECT ORGANIZATION_ID
FROM JAI_CMN_INVENTORY_ORGS
WHERE LOCATION_ID = v_loc_id ;
SELECT SUBSTR(ORGANIZATION_CODE,1,4)
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = v_From_org_id ;
SELECT B.ORGANIZATION_CODE
FROM ORG_ORGANIZATION_DEFINITIONS B,IC_WHSE_MST C
WHERE B.ORGANIZATION_CODE = C.ORGN_CODE
AND C.WHSE_CODE = v_From_whse_code;
UPDATE JAI_OPM_EXCISE_INV_NOS
SET start_number = v_start_number,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE UPPER(orgn_code) = UPPER(V_orgn_code)
AND fin_year = v_fin_year
AND UPPER(order_invoice_type) = UPPER(v_order_invoice_type)
AND UPPER(register_code) = UPPER(v_meaning);
UPDATE JAI_OPM_EXCISE_INV_NOS
SET dom_number = v_gp_1,
exp_number = v_gp_2,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE UPPER(orgn_code) = UPPER(v_orgn_code) ----org_changed
AND fin_year = v_fin_year
AND order_invoice_type IS Null
AND register_code IS Null;
SELECT SUBSTR(v_excise_inv_no, instr(v_excise_inv_no, '/', 1, 1) + 1)
into v_excise_inv_no
from dual;
UPDATE JAI_OPM_SO_PICK_LINES
SET excise_invoice_no = v_excise_inv_no
WHERE UPPER(orgn_code) = UPPER(v_orgn_code) ---org_changed
AND bol_id = p_bol_id
AND bolline_no = P_BOLLINE_NO ;
SELECT Price_List_Id
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = p_vendor_id
AND Vendor_Site_Id = NVL( p_vendor_site_id, 0 );
SELECT Price_List_Id
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_Id = p_vendor_id
AND Vendor_Site_Id = 0;
SELECT operand
FROM qp_List_Lines_v
WHERE List_header_id = v_price_list_id
AND product_attribute_context = cp_item --'ITEM' -- cbabu for Bug# 3083335
AND product_Id = p_inv_item_id
AND product_uom_code = p_line_uom
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
/*SELECT /*+ no_expand * operand -- for bug 14787674 by anupgupt
FROM
QP_LIST_LINES QPLL
, QP_PRICING_ATTRIBUTES QPPR
WHERE QPLL.list_line_id = QPPR.list_line_id
AND QPLL.List_header_id = v_price_list_id
AND QPPR.product_attribute_context = cp_item
--AND QPPR.Product_Attr_value = p_inv_item_id --Commented by zhiwei for Bug#12718676 on 20110704
AND QPPR.Product_Attr_value = to_char(p_inv_item_id) --Added by zhiwei for Bug#12718676 on 20110704
AND QPPR.product_uom_code = p_line_uom
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE; */
SELECT qpll.operand
FROM qp_list_lines qpll,
qp_pricing_attributes qppr
WHERE qppr.list_line_id = qpll.list_line_id
AND qpll.List_header_id = v_price_list_id
AND qppr.product_attribute_context = cp_item --'ITEM'
AND decode(UPPER(qppr.product_attr_value), 'ALL', NULL, qppr.product_attr_value) = to_char(p_inv_item_id)
AND qppr.product_uom_code = p_line_uom
AND NVL( qpll.Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( qpll.End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
SELECT
category_set_name
FROM
mtl_default_category_sets_fk_v
WHERE functional_area_desc = 'Order Entry';
SELECT
b.operand list_price
FROM
qp_list_lines b
, qp_pricing_attributes c
WHERE b.list_header_id = v_price_list_id
AND c.list_line_id = b.list_line_id
AND c.product_uom_code = pv_uom_code
AND NVL( start_date_active, SYSDATE- 1 ) <= SYSDATE
AND NVL( end_date_active, SYSDATE +1 )>= 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 = to_char(d.category_id)
);