[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select
a.act_quantity,
a.uom_code,
a.excise_payable,
a.created_by,
a.creation_date,
a.last_updated_by,
a.last_update_date,
a.last_update_login,
a.organization_id,
a.inventory_item_id
From
JAI_OPM_OSP_DTLS a,
JAI_OPM_OSP_HDRS b
Where a.osp_header_id = p_ospheader
and a.issue_recpt_flag = p_mode
and b.osp_header_id = a.osp_header_id
and a.trans_date <= b.extended_due_date
and main_rcpt_flag = 'Y';
Select
excise_duty_Range,
excise_duty_division,
vendor_site_id
From
JAI_CMN_VENDOR_SITES
Where vendor_id = p_vendor_id ;
Select
item_class,
nvl(modvat_flag, 'N')
From
jai_inv_itm_setups -- JAI_OPM_ITM_MASTERS --
Where organization_id = cpn_organization_id
AND inventory_item_id = cpn_inv_itm_id ;-- item_id = p_item_id;
Select transaction_date
From JAI_OPM_OSP_HDRS
Where osp_header_id = p_ospheader;
select
max(a.fin_year)
from
JAI_CMN_FIN_YEARS a
where a.organization_id = cpn_organization_id
and a.fin_active_flag = 'Y';
- Direct inserts in OPM tables
are removed and instead related discrete API are called to make the entries in RG/PLA
Tables.
-------------------------------------------------------------------------------*/
amount_flag := 'N'; -- File.Sql.35 by Brathod
jai_cmn_rg_23ac_i_trxs_pkg.insert_row
(
P_REGISTER_ID => v_reg_id
,P_INVENTORY_ITEM_ID => rec.inventory_item_id
,P_ORGANIZATION_ID => rec.organization_id
,P_QUANTITY_RECEIVED => v_r_quantity
,P_RECEIPT_ID => v_r_ospheader
,P_TRANSACTION_TYPE => p_iss_recpt_mode
,P_RECEIPT_DATE => v_r_txndate
,P_PO_HEADER_ID => Null
,P_PO_HEADER_DATE => Null
,P_PO_LINE_ID => Null
,P_PO_LINE_LOCATION_ID => Null
,P_VENDOR_ID => p_vendor_id
,P_VENDOR_SITE_ID => ln_vendor_site_id
,P_CUSTOMER_ID => Null
,P_CUSTOMER_SITE_ID => Null
,P_GOODS_ISSUE_ID => v_i_ospheader
,P_GOODS_ISSUE_DATE => v_i_txndate
,P_GOODS_ISSUE_QUANTITY => v_i_quantity
,P_SALES_INVOICE_ID => Null
,P_SALES_INVOICE_DATE => Null
,P_SALES_INVOICE_QUANTITY => Null
,P_EXCISE_INVOICE_ID => Null
,P_EXCISE_INVOICE_DATE => Null
,P_OTH_RECEIPT_QUANTITY => Null
,P_OTH_RECEIPT_ID => Null
,P_OTH_RECEIPT_DATE => Null
,P_REGISTER_TYPE => v_register_type
,P_IDENTIFICATION_NO => null
,P_IDENTIFICATION_MARK => null
,P_BRAND_NAME => null
,P_DATE_OF_VERIFICATION => null
,P_DATE_OF_INSTALLATION => null
,P_DATE_OF_COMMISSION => null
,P_REGISER_ID_PART_II => null
,P_PLACE_OF_INSTALL => null
,P_REMARKS => 'OPM OSP Transaction'
,P_LOCATION_ID => p_location_id
,P_TRANSACTION_UOM_CODE => rec.uom_code
,P_TRANSACTION_DATE => p_trans_date
,P_BASIC_ED => v_excise_amt
,P_ADDITIONAL_ED => null
,P_OTHER_ED => null
,P_CHARGE_ACCOUNT_ID => NULL
,P_TRANSACTION_SOURCE => 'OPM_OSP'
,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg23_entry'
,P_SIMULATE_FLAG => jai_constants.no
,P_PROCESS_STATUS => lv_proc_status
,P_PROCESS_MESSAGE => lv_proc_msg
);
select JAI_CMN_RG_23AC_II_TRXS_S.nextval
into v_reg_id
from dual;
jai_cmn_rg_23ac_ii_pkg.insert_row
(
P_REGISTER_ID => lv_reg_id_ii
,P_INVENTORY_ITEM_ID => rec.inventory_item_id
,P_ORGANIZATION_ID => rec.organization_id
,P_RECEIPT_ID => v_r_ospheader
,P_RECEIPT_DATE => v_r_txndate
,P_CR_BASIC_ED => v_r_excise_amt
,P_CR_ADDITIONAL_ED => null
,P_CR_OTHER_ED => null
,P_DR_BASIC_ED => v_i_excise_amt
,P_DR_ADDITIONAL_ED => null
,P_DR_OTHER_ED => null
,P_EXCISE_INVOICE_NO => NULL
,P_EXCISE_INVOICE_DATE => NULL
,P_REGISTER_TYPE => v_register_type
,P_REMARKS => 'OPM OSP Transaction'
,P_VENDOR_ID => p_vendor_id
,P_VENDOR_SITE_ID => ln_vendor_site_id
,P_CUSTOMER_ID => null
,P_CUSTOMER_SITE_ID => null
,P_LOCATION_ID => p_location_id
,P_TRANSACTION_DATE => p_trans_date
,P_CHARGE_ACCOUNT_ID => null
,P_REGISTER_ID_PART_I => v_reg_id
,P_REFERENCE_NUM => null
,P_ROUNDING_ID => null
,P_OTHER_TAX_CREDIT => null
,P_OTHER_TAX_DEBIT => null
,P_TRANSACTION_TYPE => p_iss_recpt_mode
,P_TRANSACTION_SOURCE => 'OPM_OSP'
,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg23_entry'
,P_SIMULATE_FLAG => jai_constants.no
,P_PROCESS_STATUS => lv_proc_status
,P_PROCESS_MESSAGE => lv_proc_msg
);
UPDATE jai_cmn_rg_23ac_i_trxs
SET register_id_part_ii = lv_reg_id_ii
WHERE register_id = v_reg_id;
jai_cmn_rg_pla_trxs_pkg.insert_row
(
P_REGISTER_ID => lv_reg_id_pla
,P_TR6_CHALLAN_NO => NULL
,P_TR6_CHALLAN_DATE => NULL
,P_CR_BASIC_ED => v_r_excise_amt
,P_CR_ADDITIONAL_ED => null
,P_CR_OTHER_ED => null
,P_REF_DOCUMENT_ID => p_ospheader
,P_REF_DOCUMENT_DATE => sysdate
,P_DR_INVOICE_ID => null
,P_DR_INVOICE_DATE => null
,P_DR_BASIC_ED => v_i_excise_amt
,P_DR_ADDITIONAL_ED => null
,P_DR_OTHER_ED => null
,P_ORGANIZATION_ID => rec.organization_id
,P_LOCATION_ID => p_location_id
,P_BANK_BRANCH_ID => null
,P_ENTRY_DATE => sysdate
,P_INVENTORY_ITEM_ID => rec.inventory_item_id
,P_VENDOR_CUST_FLAG => 'V'
,P_VENDOR_ID => p_vendor_id
,P_VENDOR_SITE_ID => ln_vendor_site_id
,P_EXCISE_INVOICE_NO => NULL
,P_REMARKS => 'OPM OSP Transaction'
,P_TRANSACTION_DATE => nvl(l_tran_date, sysdate)
,P_CHARGE_ACCOUNT_ID => null
,P_OTHER_TAX_CREDIT => null
,P_OTHER_TAX_DEBIT => null
,P_TRANSACTION_TYPE => p_iss_recpt_mode
,P_TRANSACTION_SOURCE => 'OPM OSP'
,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg23_entry'
,P_SIMULATE_FLAG => jai_constants.no
,P_PROCESS_STATUS => lv_proc_status
,P_PROCESS_MESSAGE => lv_proc_msg
,P_ROUNDING_ID => NULL
);
Update JAI_CMN_RG_BALANCES
set rg23a_balance = rg23a_balance - nvl(v_i_excise_amt,0)
Where organization_id = l_org_id;
Update JAI_CMN_RG_BALANCES
set rg23c_balance = rg23c_balance - nvl(v_i_excise_amt,0)
Where organization_id = l_org_id;
Update JAI_CMN_RG_BALANCES
set pla_balance = pla_balance - nvl(v_i_excise_amt,0)
Where organization_id = l_org_id;
Update JAI_CMN_RG_BALANCES
set rg23a_balance = rg23a_balance + nvl(v_r_excise_amt,0)
Where organization_id = l_org_id;
Update JAI_CMN_RG_BALANCES
set rg23c_balance = rg23c_balance + nvl(v_r_excise_amt,0)
Where organization_id = l_org_id;
Update JAI_CMN_RG_BALANCES
set pla_balance = pla_balance + nvl(v_r_excise_amt,0)
Where organization_id = l_org_id;
SELECT NVL(pla_balance,0)
FROM JAI_CMN_RG_BALANCES a, JAI_CMN_INVENTORY_ORGS b
WHERE a.organization_id = b.organization_id
and a.location_id = b.location_id
and a.organization_id = p_org_id
and b.MASTER_ORG_FLAG = 'Y';
SELECT NVL(pla_balance,0)
FROM JAI_CMN_RG_BALANCES a, JAI_CMN_INVENTORY_ORGS b
WHERE a.organization_id = b.organization_id
and a.location_id = b.location_id
and a.organization_id = p_org_id
and b.MASTER_ORG_FLAG = 'Y' ;
SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_org_id and
-- location_id = p_location_id and
fin_year = p_fin_year;
select item_class
from JAI_INV_ITM_SETUPS --JAI_OPM_ITM_MASTERS
where organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id ;--item_id = p_item_id;
select po_id
from JAI_OPM_OSP_HDRS
where osp_header_id = p_ospheader;
select vendor_id, vendor_site_id
from po_headers_all --po_ordr_hdr
where po_header_id = l_po_id;
select excise_duty_range, excise_duty_division
from JAI_CMN_VENDOR_SITES
where vendor_id = l_shipvend_id;
select payable_excise
from JAI_OPM_OSP_HDRS
where osp_header_id = p_ospheader ;
select max(a.fin_year)
from JAI_CMN_FIN_YEARS a
where a.organization_id = p_organization_id
and a.fin_active_flag = 'Y';
Procedure to insert into Rg1 table through OSP process
Change History for FileName create_rg_i_entry_prc.sql
S.No DD/MM/YYYY Author and Description
----------------------------------------------------------------------------------------------------------------------------
1 29/09/2004 Vijay Shankar for Bug# 3030446, File Version : 712.1
population of data into BALANCE_PACKED column is stopped as it was leading to datafixes
and also redundant.
From now on only balance_loose should be used and balance_packed is obsolete
2 17/10/2004 Aparajita.
Merge of OPM and Discrete with Obsoletion of PO logistics.
Changed the definition of cursor C_vend_ran_div to fetch the details from
JAI_CMN_VENDOR_SITES instead of ja_in_vendors. ja_in_vendors has been dropped
for the obsoletion of Obsoletion of PO logistics.
--------------------------------------------------------------------------------------------------------------------------*/
exciseitem := 'N'; -- File.Sql.35 by Brathod
select max(slno) into srno
from JAI_OPM_RG23_I_TRXS
where orgn_code = p_orgn_code
and register_type = l_reg_type
and fin_year = l_year;
insert into JAI_OPM_RG23_I_TRXS
(
register_id,
slno,
fin_year,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
TRANSACTION_SOURCE_NUM,
transaction_date,
inventory_item_id,
orgn_code,
transaction_type,
vendor_id,
vendor_site_id,
register_type,
uom_code,
folio_no,
entry_date,
LOCATION_CODE,
range_no,
division_no,
quantity_received,
GOODS_ISSUE_ID_REF,
receipt_date
)
values
(
JAI_CMN_RG_23AC_I_TXNS_S.nextval,
l1_slno,
l_year,
sysdate,
p_created_by,
null,
sysdate,
p_created_by,
92,
p_trans_date ,
p_item_id,
p_orgn_code ,
'R',
l_shipvend_id,
l_vend_site_id,
l_reg_type,
p_uom_code,
l1_folio,
sysdate,
p_whse_code,
l_range_no,
l_div_no,
p_qty,
p_ospheader,
p_trans_date
);
jai_cmn_rg_23ac_i_trxs_pkg.insert_row
(
P_REGISTER_ID => ln_reg_id
,P_INVENTORY_ITEM_ID => p_inventory_item_id
,P_ORGANIZATION_ID => p_organization_id
,P_QUANTITY_RECEIVED => p_qty
,P_RECEIPT_ID => NULL
,P_TRANSACTION_TYPE => 'R'
,P_RECEIPT_DATE => p_trans_date
,P_PO_HEADER_ID => l_po_id
,P_PO_HEADER_DATE => Null
,P_PO_LINE_ID => Null
,P_PO_LINE_LOCATION_ID => Null
,P_VENDOR_ID => l_shipvend_id
,P_VENDOR_SITE_ID => l_vend_site_id
,P_CUSTOMER_ID => Null
,P_CUSTOMER_SITE_ID => Null
,P_GOODS_ISSUE_ID => p_ospheader
,P_GOODS_ISSUE_DATE => null
,P_GOODS_ISSUE_QUANTITY => null
,P_SALES_INVOICE_ID => Null
,P_SALES_INVOICE_DATE => Null
,P_SALES_INVOICE_QUANTITY => Null
,P_EXCISE_INVOICE_ID => Null
,P_EXCISE_INVOICE_DATE => Null
,P_OTH_RECEIPT_QUANTITY => Null
,P_OTH_RECEIPT_ID => Null
,P_OTH_RECEIPT_DATE => Null
,P_REGISTER_TYPE => l_reg_type
,P_IDENTIFICATION_NO => null
,P_IDENTIFICATION_MARK => null
,P_BRAND_NAME => null
,P_DATE_OF_VERIFICATION => null
,P_DATE_OF_INSTALLATION => null
,P_DATE_OF_COMMISSION => null
,P_REGISER_ID_PART_II => null
,P_PLACE_OF_INSTALL => null
,P_REMARKS => 'OPM OSP Transaction'
,P_LOCATION_ID => p_location_id
,P_TRANSACTION_UOM_CODE => p_uom_code
,P_TRANSACTION_DATE => p_trans_date
,P_BASIC_ED => null
,P_ADDITIONAL_ED => null
,P_OTHER_ED => null
,P_CHARGE_ACCOUNT_ID => NULL
,P_TRANSACTION_SOURCE => 'OPM OSP'
,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg_i_entry'
,P_SIMULATE_FLAG => jai_constants.no
,P_PROCESS_STATUS => lv_proc_status
,P_PROCESS_MESSAGE => lv_proc_msg
);
/*select max(slno)
into srno
from JAI_OPM_RG_I_TRXS
where orgn_code = p_orgn_code
and fin_year = l_year;
insert into JAI_OPM_RG_I_TRXS
(
register_id,
slno,
fin_year,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
TRANSACTION_SOURCE_NUM,
transaction_date,
inventory_item_id,
orgn_code,
transaction_type,
--balance_packed, Commented by Vijay Shankar for Bug# 3030446
REF_DOC_NO,
uom_code,
transaction_uom,
manufactured_qty,
excise_duty_amount,
basic_excise_duty_amount,
entry_date,
LOCATION_CODE,
slno_part_ii,
folio_no_part_ii
)
values
(
JAI_CMN_RG_I_TXNS_S.nextval,
l_slno,
l_year,
sysdate,
p_created_by,
null,
sysdate,
p_created_by,
92,
p_trans_date,
p_item_id,
p_orgn_code,
'R',
-- p_qty, Commented by Vijay Shankar for Bug# 3030446
p_ospheader,
p_uom_code,
p_uom_code,
p_qty,
l_excise,
l_excise,
sysdate,
p_whse_code,
null,
null
);*/
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATED_BY => p_created_by
,P_LAST_UPDATE_LOGIN => ln_login_id
,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg_i_entry'
);