[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(Location_id,0),
trading,
NVL(bonded,'N') bonded
FROM
JAI_INV_SUBINV_DTLS
WHERE
Sub_Inventory_Name = v_subinventory AND
organization_id = v_organization_id ;
SELECT
Item_Trading_Flag
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = v_organization_id AND
inventory_item_id = v_inventory_item_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_organization_id AND
a.location_id = p_location_id AND
a.register_id = b.register_id AND
b.order_flag = 'Y' AND
b.order_type_id = p_order_type_id ;
SELECT
sum(a.quantity_applied) quantity_applied
FROM
JAI_CMN_MATCH_RECEIPTS a
WHERE
a.ref_line_id = v_delivery_detail_id
AND a.order_invoice = 'O' ;
SELECT
excise_flag ,
modvat_flag ,
nvl(container_item_flag,'N'),
nvl(inventory_item_flag,'N')
FROM
mtl_system_items msi,
JAI_INV_ITM_SETUPS jmsi
WHERE msi.organization_id = jmsi.organization_id
AND msi.inventory_item_id = jmsi.inventory_item_id
AND jmsi.inventory_item_id = v_Inventory_Item_Id
AND jmsi.organization_id = v_organization_id;
SELECT
lc_flag
FROM
JAI_OM_OE_SO_LINES
WHERE
lc_flag = 'Y' AND
header_id = pr_new.source_header_id;
SELECT
sum(qty_matched)
FROM
JAI_OM_LC_MATCHINGS
WHERE
order_header_id = pr_new.source_header_id AND
delivery_detail_id = cp_delivery_detail_id AND
release_flag is null;
select split_from_line_id
from oe_order_lines_all
where line_id = pr_new.source_line_id;
select order_line_id
from JAI_OM_LC_MATCHINGS
where delivery_detail_id = v_delivery_detail_id;
SELECT
tax_amount
FROM
JAI_OM_OE_SO_LINES
WHERE
line_id = pr_new.source_line_id AND
header_id = pr_new.source_header_id;
SELECT
nvl(sum(so_tax.tax_amount),0)
FROM
JAI_OM_OE_SO_TAXES so_tax
, jai_cmn_taxes_all tax
WHERE
line_id = pr_new.source_line_id AND
header_id = pr_new.source_header_id
AND so_tax.tax_id = tax.tax_id
AND NVL(tax.inclusive_tax_flag,'N') = 'N' ;
SELECT
a.excise_exempt_type ,
a.line_number ,
a.shipment_line_number ,
quantity
FROM
JAI_OM_OE_SO_LINES a
WHERE
a.line_id = pr_new.source_line_id AND
a.header_id = pr_new.source_header_id ;
select trading
from JAI_CMN_INVENTORY_ORGS
where organization_id = pr_new.organization_id
and location_id = pr_new.ship_from_location_id ;
SELECT sum ( shipped_quantity)
FROM jai_wsh_del_details_gt
WHERE delivery_detail_id = cp_delivery_detail_id
OR split_from_delivery_detail_id = cp_delivery_detail_id;
Update jai_wsh_del_details_gt
set processed_flag = 'Y'
where delivery_detail_id = pr_new.delivery_detail_id;
update JAI_OM_LC_MATCHINGS
set order_line_id = pr_new.source_line_id
where delivery_detail_id = v_delivery_detail_id
and release_flag is null;
select count(1) into ln_unprocessed_recs from jai_wsh_del_details_gt
where processed_flag = 'N'
AND (delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id )
OR split_from_delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id));
delete jai_wsh_del_details_gt
where processed_flag = 'Y'
and (delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id )
OR split_from_delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id));
v_last_update_date DATE ;
v_last_updated_by NUMBER;
v_last_update_login NUMBER;
SELECT A.delivery_id,
A.confirm_date,
A.status_code
FROM Wsh_Delivery_Assignments B,
Wsh_New_deliveries A
WHERE B.Delivery_Id = A.Delivery_Id
AND B.Delivery_Detail_id = v_delivery_detail_id;
SELECT excise_flag, modvat_flag
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = v_Inventory_Item_Id
AND organization_id = v_organization_id;
SELECT COUNT(*)
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = v_source_line_id;
SELECT NVL(Selling_Price,0),
NVL(Quantity,0),
NVL(Tax_Category_Id,0),
NVL(Assessable_Value,0),
NVL(vat_assessable_value,0),
Excise_Exempt_Type,
Excise_Exempt_Refno,
Excise_Exempt_Date,
vat_exemption_flag,
vat_exemption_type,
vat_exemption_date,
vat_exemption_refno
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = v_source_line_id;
SELECT
transactional_curr_code ,
conversion_type_code ,
conversion_rate ,
nvl(b.actual_shipment_date,sysdate) actual_shipment_date
FROM
oe_order_headers_all a ,
oe_order_lines_all b
WHERE
a.header_id = b.header_id AND
b.line_id = v_source_line_id AND
a.header_id = v_source_header_id ;
SELECT b.Tax_Type,
NVL(b.Rounding_Factor,2) Rounding_Factor,
A.Tax_Line_No,
A.Precedence_1,
A.Precedence_2,
A.Precedence_3,
A.Precedence_4,
A.Precedence_5,
A.Precedence_6,
A.Precedence_7,
A.Precedence_8,
A.Precedence_9,
A.Precedence_10,
A.Tax_Id,
A.Tax_Rate,
A.Qty_Rate,
A.Uom,
A.Tax_Amount,
A.Base_Tax_Amount,
A.Func_Tax_Amount
FROM JAI_OM_OE_SO_TAXES A,
JAI_CMN_TAXES_ALL b
WHERE Line_id = v_source_line_id
AND A.Tax_Id = b.Tax_Id
ORDER BY A.Tax_Line_No;
SELECT COUNT(*)
FROM JAI_OM_WSH_LINE_TAXES
WHERE Delivery_Detail_Id = v_delivery_detail_id
AND Tax_Id = P_Tax_Id;
SELECT SUM(A.Tax_Amount)
FROM JAI_OM_WSH_LINE_TAXES A,
JAI_CMN_TAXES_ALL b
WHERE A.Delivery_Detail_Id = v_delivery_detail_id
AND b.Tax_Id = A.Tax_Id
AND b.Tax_Type <> 'TDS';
SELECT COUNT(*)
FROM JAI_OM_WSH_LINES_ALL
WHERE Delivery_Detail_Id = v_delivery_detail_id
AND Delivery_id = v_delivery_id;
SELECT NVL(Location_id,0),
trading
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = v_subinventory
AND organization_id = v_organization_id;
SELECT ship_to_org_id, invoice_to_org_id
FROM Oe_order_lines_all
WHERE line_id = p_line_id;
SELECT customer_id
FROM oe_invoice_to_orgs_v
WHERE site_use_id = cp_site_use_id;
SELECT nvl(sum(tax_amount),0) ,
nvl(sum(base_tax_amount),0),
nvl(sum(func_tax_amount),0)
FROM
JAI_CMN_MATCH_TAXES
WHERE
ref_line_id = v_delivery_detail_id AND
receipt_id IS NOT NULL AND
tax_id = p_tax_id;
SELECT
nvl(sum(a.func_tax_amount),0)
FROM
JAI_CMN_MATCH_TAXES a,
JAI_CMN_TAXES_ALL b
WHERE
a.tax_id = b.tax_id AND
b.tax_type = p_tax_type AND
A.ref_line_id = v_delivery_detail_id AND
A.receipt_id IS NOT NULL;
SELECT
item_trading_flag
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = v_organization_id AND
inventory_item_id = v_inventory_item_id;
SELECT order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = v_source_line_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_organization_id AND
A.location_id = p_location_id AND
A.register_id = b.register_id AND
b.order_flag = 'Y' AND
b.order_type_id = p_order_type_id AND
A.register_code LIKE '23D%';
SELECT
sum(a.quantity_applied) quantity_applied
FROM
JAI_CMN_MATCH_RECEIPTS a
WHERE
a.ref_line_id = v_delivery_detail_id;
SELECT
excise_flag,
item_class
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = p_organization_id AND
inventory_item_id = p_item_id;
SELECT NVL(A.bonded,'Y') bonded
FROM JAI_INV_SUBINV_DTLS A
WHERE A.sub_inventory_name = p_subinventory
AND A.organization_id = p_organization_id;
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
SELECT pref_rg23a,
pref_rg23c,
pref_pla,
NVL(Export_oriented_unit ,'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT NVL(rg23a_balance,0) rg23a_balance ,
NVL(rg23c_balance,0) rg23c_balance,
NVL(pla_balance,0) pla_balance,
NVL(basic_pla_balance,0) basic_pla_balance,
NVL(additional_pla_balance,0) additional_pla_balance,
NVL(other_pla_balance,0) other_pla_balance
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT ssi_unit_flag
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id;
SELECT A.Tax_Rate, NVL(b.Rounding_Factor,0) Rounding_Factor
FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
WHERE Line_id = v_source_line_id
AND A.Tax_Id = b.Tax_Id
AND b.tax_type = 'Modvat Recovery'
ORDER BY A.Tax_Line_No;
SELECT MODVAT_REVERSE_PERCENT
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND ( location_id = p_location_id OR
location_id is NULL AND p_location_id is NULL);
SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
FROM JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jsptl.tax_id
AND delivery_detail_id in
(SELECT delivery_detail_id
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id = cp_delivery_id
)
AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
FROM JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jsptl.tax_id
AND delivery_detail_id in
(SELECT delivery_detail_id
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id = cp_delivery_id
)
AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_sh_exc_edu_cess),
upper(jai_constants.tax_type_sh_cvd_edu_cess)
);
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc
, jai_regime_tax_types_v tax_types
WHERE jsptl.delivery_detail_id = cp_del_det_id
AND jtc.tax_id = jsptl.tax_id
AND jtc.tax_type = tax_types.tax_type
AND tax_types.regime_code = jai_constants.vat_regime;
SELECT regime_id,
attribute_value
FROM JAI_RGM_ORG_REGNS_V orrg
WHERE orrg.organization_id = cp_organization_id
AND orrg.location_id = cp_location_id
AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
AND attribute_code = jai_constants.attr_code_regn_no
AND regime_code = jai_constants.vat_regime;
SELECT 1
FROM JAI_RGM_INVOICE_GEN_T
WHERE delivery_id = cp_delivery_id;
SELECT 1
FROM JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc
WHERE jsptl.delivery_detail_id = cp_del_det_id
AND jtc.tax_id = jsptl.tax_id
AND jtc.tax_type = cp_tax_type ;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
SELECT COUNT(1)
FROM JAI_REGIME_TAX_TYPES_V JRTTV
, JAI_OM_WSH_LINE_TAXES JSPT
, JAI_CMN_TAXES_ALL JTC
WHERE JTC.TAX_ID = JSPT.TAX_ID
AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
AND REGIME_CODE = CP_REGIME_CODE
AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
v_last_update_date :=pr_new.Last_Update_Date;
v_last_updated_by :=pr_new.Last_Updated_By;
v_last_update_login :=pr_new.Last_Update_Login;
Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINE_TAXES for Bond register with
'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
*/
IF NVL(v_trading_flag,'N') = 'Y' AND
NVL(v_item_trading_flag,'N') = 'Y' AND
NVL(v_exe_flag,'N') = 'Y' AND
v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
THEN
OPEN rg23d_amount_cur(rec.tax_id);
INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id,
Tax_Line_No,
Precedence_1,
Precedence_2,
Precedence_3,
Precedence_4,
Precedence_5,
Precedence_6,
Precedence_7,
Precedence_8,
Precedence_9,
Precedence_10,
Tax_Id,
Tax_Rate,
Qty_Rate,
Uom,
Tax_Amount,
Base_Tax_Amount,
Func_Tax_Amount,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login)
VALUES (
v_delivery_detail_id,
rec.Tax_Line_No,
rec.Precedence_1,
rec.Precedence_2,
rec.Precedence_3,
rec.Precedence_4,
rec.Precedence_5,
rec.Precedence_6,
rec.Precedence_7,
rec.Precedence_8,
rec.Precedence_9,
rec.Precedence_10,
rec.Tax_id,
rec.Tax_rate,
rec.Qty_Rate,
rec.Uom,
v_tax_amount,
v_base_tax_amount,
v_func_tax_amount,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login
);
UPDATE JAI_OM_WSH_LINE_TAXES
SET Tax_Amount = v_tax_amount,
base_tax_amount = v_base_tax_amount,
func_tax_amount = v_func_tax_amount,
Last_Update_Date = v_last_update_date,
Last_Updated_By = v_last_updated_by,
Last_Update_Login = v_last_update_login
WHERE Delivery_Detail_Id = v_delivery_detail_id
AND Tax_Id = rec.Tax_Id;
select max(tax_line_no)
into ln_last_line_no
from JAI_OM_WSH_LINE_TAXES
where delivery_detail_id = v_delivery_detail_id;
select max(tax_line_no)
into ln_base_line_no
from JAI_OM_WSH_LINE_TAXES jsptl
, JAI_CMN_TAXES_ALL jtc
where jsptl.delivery_detail_id = v_delivery_detail_id
and jsptl.tax_id = jtc.tax_id
and jtc.tax_type = jai_constants.tax_type_tcs;
|| The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
|| into the JAI_OM_WSH_LINE_TAXES table
*/
ln_vat_assessable_value := nvl(ln_vat_assessable_value * v_shipped_quantity / v_quantity, 0);
Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINES_ALL for Bond register with
'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
*/
IF nvl(v_trading_flag,'N') = 'Y' AND
nvl(v_item_trading_flag,'N') = 'Y' AND
nvl(v_excise_flag,'N') = 'Y' AND
v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
THEN
OPEN ed_cur('Excise');
INSERT INTO JAI_OM_WSH_LINES_ALL( Delivery_Detail_Id,
Order_Header_Id,
Order_Line_Id,
split_from_delivery_detail_id,
Selling_Price,
Quantity,
Assessable_value,
vat_assessable_value,
Tax_Category_Id,
Tax_Amount,
Inventory_Item_Id,
Organization_Id,
Location_Id,
Unit_Code,
Excise_Amount,
Basic_Excise_Duty_Amount,
Add_Excise_Duty_Amount,
Oth_Excise_Duty_Amount,
Excise_Exempt_Type,
Excise_Exempt_Refno,
Excise_Exempt_Date,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
ORG_ID,
CUSTOMER_ID,
SHIP_TO_ORG_ID,
ORDER_TYPE_ID,
SUBINVENTORY,
DELIVERY_ID,
VAT_EXEMPTION_FLAG,
VAT_EXEMPTION_TYPE,
VAT_EXEMPTION_DATE,
VAT_EXEMPTION_REFNO
, SHIPPABLE_FLAG)
VALUES (v_delivery_detail_id,
v_source_header_id,
v_source_line_id,
pr_new.split_from_delivery_detail_id,
v_selling_price * v_conversion_rate,
v_shipped_quantity,
v_assessable_value * v_conversion_rate,
ln_vat_assessable_value * v_conversion_rate,
v_tax_category_id,
v_tot_tax_amount,
v_Inventory_Item_Id,
v_Organization_Id,
v_location_id,
v_Requested_Quantity_Uom,
v_excise_amount,
v_basic_excise_duty_amount,
v_add_excise_duty_amount,
v_oth_excise_duty_amount,
v_excise_exempt_type,
v_excise_exempt_refno,
v_excise_exempt_date,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_org_Id,
v_customer_id,
v_ship_to_org_id,
v_source_header_type_id,
v_subinventory,
v_DELIVERY_ID,
lv_vat_exemption_flag,
lv_vat_exemption_type,
ld_vat_exemption_date,
lv_vat_exemption_refno
, NULL);
INSERT INTO JAI_OM_OE_GEN_TAXINV_T( date_released,
date_confirmed,
delivery_detail_id,
order_header_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
delivery_id)
VALUES (SYSDATE,
v_date_confirmed,
v_delivery_detail_id,
v_source_header_id,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_delivery_id);
INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id ,
delivery_id ,
delivery_date ,
customer_trx_id ,
organization_id ,
location_id ,
registration_num ,
vat_invoice_no ,
vat_inv_gen_status ,
vat_inv_gen_err_message ,
vat_acct_status ,
vat_acct_err_message ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
party_id ,
party_site_id ,
party_type ,
creation_date ,
created_by ,
last_update_date ,
last_update_login ,
last_updated_by
)
VALUES (ln_regime_id ,
v_delivery_id ,
v_creation_date ,
null ,
v_organization_id ,
v_location_id ,
lv_regns_num ,
lv_vat_invoice_no ,
nvl(lv_vat_inv_gen_status, 'P'),
null ,
'P' ,
null ,
null ,
null ,
null ,
null ,
ln_bill_to_cust_id ,
v_bill_to_org_id ,
jai_constants.party_type_customer,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_update_login ,
v_last_updated_by
);
UPDATE JAI_OM_WSH_LINES_ALL
SET quantity = v_shipped_quantity,
tax_amount = v_tot_tax_amount,
order_line_id = v_source_line_id,
excise_amount = v_excise_amount,
basic_excise_duty_amount = v_basic_excise_duty_amount,
add_excise_duty_amount = v_add_excise_duty_amount,
oth_excise_duty_amount = v_oth_excise_duty_amount,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login,
VAT_EXEMPTION_FLAG = lv_vat_exemption_flag,
VAT_EXEMPTION_TYPE = lv_vat_exemption_type,
VAT_EXEMPTION_DATE = ld_vat_exemption_date,
VAT_EXEMPTION_REFNO = lv_vat_exemption_refno
WHERE Delivery_id = v_delivery_id
AND Delivery_Detail_id = v_delivery_detail_id;
SELECT trading,
excise_rcvble_account,
excise_paid_account,
excise_23d_account,
excise_in_rg23d,
order_price_excise_inclusive,
cess_paid_payable_account_id,
sh_cess_paid_payable_acct_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = pr_trig_row.organization_id
AND location_id = pr_trig_row.ship_from_location_id;
SELECT cost_of_sales_account
FROM mtl_parameters
WHERE organization_id = pr_trig_row.organization_id;
SELECT receipt_id, receipt_quantity_applied
FROM JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id = pr_trig_row.delivery_detail_id;
SELECT closing_balance_qty - opening_balance_qty receipt_quantity,
Nvl(basic_ed, 0) + Nvl(additional_ed, 0) + Nvl(other_ed, 0) duty_amount,
transaction_uom_code
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_register_id;
SELECT credit
FROM JAI_CMN_RG_OTHERS
WHERE source_register = 'RG23D'
AND source_register_id = p_register_id
AND tax_type = 'EXCISE_EDUCATION_CESS';
SELECT credit
FROM JAI_CMN_RG_OTHERS
WHERE source_register = 'RG23D'
AND source_register_id = p_register_id
AND tax_type = 'EXCISE_SH_EDU_CESS';
SELECT To_Char(delivery_id)
FROM wsh_delivery_assignments
WHERE delivery_detail_id = pr_trig_row.delivery_detail_id;
SELECT gsb.currency_code
FROM hr_operating_units hru, gl_sets_of_books gsb
WHERE gsb.set_of_books_id = hru.set_of_books_id
AND hru.organization_id = pr_trig_row.org_id;
SELECT Nvl(PRECISION, 2)
FROM gl_currencies
WHERE currency_code = cp_curr_code;
SELECT count(*)
FROM jai_om_wsh_line_taxes a, jai_cmn_taxes_all b
where a.delivery_Detail_id = cp_ddid
and a.tax_id = b.tax_id
and upper(b.tax_type) like '%EXCISE%'
AND B.INCLUSIVE_TAX_FLAG = 'Y';
INSERT INTO JAI_CMN_JOURNAL_ENTRIES
(JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
repository_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(JAI_CMN_JOURNAL_ENTRIES_S.nextval,
lv_regime_code,
pr_trig_row.organization_id,
v_set_of_books_id,
NULL,
NULL,
ln_cost_of_goods_ac,
ln_temp_amt,
NULL,
SYSDATE,
lv_source_name,
lv_source_table,
pr_trig_row.delivery_detail_id,
lv_reference_25,
lv_reference_26,
NULL,
v_currency_code,
NULL,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES
(JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
repository_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(JAI_CMN_JOURNAL_ENTRIES_S.nextval,
lv_regime_code,
pr_trig_row.organization_id,
v_set_of_books_id,
NULL,
NULL,
r_il_setup.excise_23d_account,
NULL,
ln_temp_amt,
SYSDATE,
lv_source_name,
lv_source_table,
pr_trig_row.delivery_detail_id,
lv_reference_25,
lv_reference_26,
NULL,
v_currency_code,
NULL,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES
(JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
repository_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(JAI_CMN_JOURNAL_ENTRIES_S.nextval,
lv_regime_code,
pr_trig_row.organization_id,
v_set_of_books_id,
NULL,
NULL,
r_il_setup.excise_rcvble_account,
ln_excise_amt,
NULL,
SYSDATE,
lv_source_name,
lv_source_table,
pr_trig_row.delivery_detail_id,
lv_reference_25,
lv_reference_26,
NULL,
v_currency_code,
NULL,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES
(JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
repository_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(JAI_CMN_JOURNAL_ENTRIES_S.nextval,
lv_regime_code,
pr_trig_row.organization_id,
v_set_of_books_id,
NULL,
NULL,
r_il_setup.cess_paid_payable_account_id,
ln_cess_amt,
NULL,
SYSDATE,
lv_source_name,
lv_source_table,
pr_trig_row.delivery_detail_id,
lv_reference_25,
lv_reference_26,
NULL,
v_currency_code,
NULL,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES
(JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
repository_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(JAI_CMN_JOURNAL_ENTRIES_S.nextval,
lv_regime_code,
pr_trig_row.organization_id,
v_set_of_books_id,
NULL,
NULL,
r_il_setup.sh_cess_paid_payable_acct_id,
ln_sh_cess_amt,
NULL,
SYSDATE,
lv_source_name,
lv_source_table,
pr_trig_row.delivery_detail_id,
lv_reference_25,
lv_reference_26,
NULL,
v_currency_code,
NULL,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES
(JOURNAL_ENTRY_ID,
regime_code,
organization_id,
set_of_books_id,
tax_type,
period_name,
code_combination_id,
accounted_dr,
accounted_cr,
transaction_date,
source,
source_table_name,
source_trx_id,
reference_name,
reference_id,
repository_id,
currency_code,
curr_conv_rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(JAI_CMN_JOURNAL_ENTRIES_S.nextval,
lv_regime_code,
pr_trig_row.organization_id,
v_set_of_books_id,
NULL,
NULL,
r_il_setup.excise_23d_account,
NULL,
ln_temp_amt,
SYSDATE,
lv_source_name,
lv_source_table,
pr_trig_row.delivery_detail_id,
lv_reference_25,
lv_reference_26,
NULL,
v_currency_code,
NULL,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
SELECT
lc_flag
FROM
JAI_OM_OE_SO_LINES
WHERE
lc_flag = 'Y' AND
header_id = pr_new.source_header_id;
Select count(delivery_detail_id) into ln_cnt
from jai_wsh_del_details_gt
Where delivery_detail_id = pr_new.delivery_detail_id;
Update jai_wsh_del_details_gt
set shipped_quantity = pr_new.shipped_quantity
where delivery_detail_id = pr_new.delivery_detail_id;
INSERT INTO jai_wsh_del_details_gt( delivery_detail_id,
organization_id ,
inventory_item_id,
source_header_type_id,
shipped_quantity,
source_header_id,
source_line_id,
SPLIT_FROM_DELIVERY_DETAIL_ID,
processed_flag)
Values ( pr_new.delivery_detail_id,
pr_new.organization_id ,
pr_new.inventory_item_id,
pr_new.source_header_type_id,
pr_new.shipped_quantity,
pr_new.source_header_id,
pr_new.source_line_id,
pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,
'N');
SELECT A.status_code
FROM Wsh_Delivery_Assignments B,
Wsh_New_deliveries A
WHERE B.Delivery_Id = A.Delivery_Id
AND B.Delivery_Detail_id = v_delivery_detail_id;
SELECT COUNT(*)
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = v_source_line_id;
SELECT NVL(Selling_Price,0),
NVL(Quantity,0),
NVL(Tax_Category_Id,0),
NVL(Assessable_Value,0),
NVL(vat_assessable_value,0),
Excise_Exempt_Type,
Excise_Exempt_Refno,
Excise_Exempt_Date,
vat_exemption_flag,
vat_exemption_type,
vat_exemption_date,
vat_exemption_refno
FROM JAI_OM_OE_SO_LINES
WHERE Line_id = v_source_line_id;
SELECT
sum(a.quantity_applied) quantity_applied
FROM
JAI_CMN_MATCH_RECEIPTS a
WHERE
a.ref_line_id = v_delivery_detail_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_organization_id AND
A.location_id = p_location_id AND
A.register_id = b.register_id AND
b.order_flag = 'Y' AND
b.order_type_id = p_order_type_id AND
A.register_code LIKE '23D%';
SELECT NVL(Location_id,0),
trading
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = v_subinventory
AND organization_id = v_organization_id;
SELECT
item_trading_flag
FROM
JAI_INV_ITM_SETUPS
WHERE
organization_id = v_organization_id AND
inventory_item_id = v_inventory_item_id;
SELECT excise_flag, modvat_flag
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = v_Inventory_Item_Id
AND organization_id = v_organization_id;
SELECT
transactional_curr_code ,
conversion_type_code ,
conversion_rate ,
nvl(b.actual_shipment_date,sysdate) actual_shipment_date
FROM
oe_order_headers_all a ,
oe_order_lines_all b
WHERE
a.header_id = b.header_id AND
b.line_id = v_source_line_id AND
a.header_id = v_source_header_id ;
SELECT COUNT(1)
FROM JAI_REGIME_TAX_TYPES_V JRTTV
, JAI_OM_WSH_LINE_TAXES JSPT
, JAI_CMN_TAXES_ALL JTC
WHERE JTC.TAX_ID = JSPT.TAX_ID
AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
AND REGIME_CODE = CP_REGIME_CODE
AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
SELECT A.confirm_date
FROM Wsh_Delivery_Assignments B,
Wsh_New_deliveries A
WHERE B.Delivery_Id = A.Delivery_Id
AND B.Delivery_Detail_id = v_delivery_detail_id;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
SELECT
jsptl.* ,
jrttv.regime_id,
jtc.tax_type
FROM
JAI_OM_WSH_LINES_ALL jspl ,
JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jspl.delivery_detail_id = jsptl.delivery_detail_id
AND jspl.delivery_id = v_delivery_id
AND jspl.delivery_detail_id = v_delivery_detail_id
AND jsptl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime;
SELECT NVL(Location_id,0)
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = v_subinventory
AND organization_id = v_organization_id;
SELECT *
FROM wsh_delivery_details
WHERE delivery_detail_id IN
( SELECT
delivery_detail_id
FROM
wsh_delivery_assignments
WHERE
delivery_id = p_delivery_id
);
SELECT *
FROM wsh_delivery_details
WHERE delivery_detail_id IN
( SELECT
delivery_detail_id
FROM
wsh_delivery_assignments
WHERE
delivery_id = p_delivery_id
);
SELECT *
FROM wsh_delivery_details
WHERE delivery_detail_id IN
( SELECT
delivery_detail_id
FROM
wsh_delivery_assignments
WHERE
delivery_id = p_delivery_id
);
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = jai_constants.vat_regime;
select delivery_date
from JAI_RGM_INVOICE_GEN_T
where delivery_id = p_delivery_id;
SELECT Location_id
FROM JAI_INV_SUBINV_DTLS
WHERE Sub_Inventory_Name = cp_subinventory
AND organization_id = cp_organization_id;
SELECT attribute_Value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = cp_regime_id
AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
AND attribute_code = jai_constants.attr_code_regn_no
AND organization_id = cp_organization_id
AND location_id = cp_location_id;
select 'Y'
from JAI_WSH_EXCEPTIONS_T
where DELIVERY_ID = p_delivery_id and
EXCEPTION_ENTITY = p_exp_entity;
'Delete table JAI_WSH_EXCEPTIONS_T. Delivery Id : ' || p_delivery_id || 'Exception Entity : ' || p_exp_entity);
DELETE FROM JAI_WSH_EXCEPTIONS_T WHERE DELIVERY_ID = p_delivery_id AND EXCEPTION_ENTITY = p_exp_entity ;
INSERT INTO JAI_WSH_EXCEPTIONS_T(EXCEPTION_ID,
DELIVERY_ID,
DELIVERY_DETAIL_ID,
EXCEPTION_TYPE,
EXCEPTION_ENTITY,
EXCEPTION_NAME,
ERROR_MESSAGE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY)
VALUES (JAI_WSH_EXCEPTIONS_S.NEXTVAL,
p_delivery_id,
p_exp_msg(l_exp_msg_index).DELIVERY_DETAIL_ID,
p_exp_msg(l_exp_msg_index).EXCEPTION_TYPE,
p_exp_msg(l_exp_msg_index).EXCEPTION_ENTITY,
p_exp_msg(l_exp_msg_index).EXCEPTION_NAME,
p_exp_msg(l_exp_msg_index).ERROR_MESSAGE,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
fnd_global.user_id);