[Home] [Help]
1: PACKAGE BODY jai_cmn_rg_i_trxs_pkg AS
2: /* $Header: jai_cmn_rg_i.plb 120.3.12010000.4 2010/04/28 11:48:17 vkaranam ship $ */
3:
4:
5: PROCEDURE validate_rg1_balances(
13: P_ERR_BUF OUT NOCOPY VARCHAR2
14: ) IS
15:
16: /* Added by Ramananda for bug#4407165 */
17: lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.validate_rg1_balances';
18:
19: v_primary_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
20: v_transaction_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
21: CURSOR c_item_primary_uom(p_organization_id NUMBER, p_inventory_item_id NUMBER) IS
55: This procedure is used to validate whether required amount of balances are available to issue the goods.
56: Populates P_ERR_BUF variable with proper message if balances are not sufficient to hit RG1 register
57:
58: - FUNCTION get_rg1_transaction_id
59: This function returns a UNIQUE NUMBER used to identify the RG1 transaction type in JAI_CMN_RG_I_TRXS table
60:
61: - PROCEDURE create_rg1_entry
62: This procedure takes in all the values that has to be populated into database columns.
63: Generates the sequence no, serial no for REGISTER_ID, SLNO columns. Calculates the quantity balance to
60:
61: - PROCEDURE create_rg1_entry
62: This procedure takes in all the values that has to be populated into database columns.
63: Generates the sequence no, serial no for REGISTER_ID, SLNO columns. Calculates the quantity balance to
64: be populated into BALANCE_LOOSE column of the table and finally inserts data into JAI_CMN_RG_I_TRXS table.
65:
66:
67:
68: Change History
134: v_quantity := nvl(p_quantity, 0) * vTransToPrimaryUOMConv;
135: /*Bug 9550254 - Start*/
136: /*
137: SELECT max(slno) INTO vMaxSlno
138: FROM JAI_CMN_RG_I_TRXS
139: WHERE organization_id = p_organization_id
140: AND location_id = p_location_id
141: AND inventory_item_id = p_inventory_item_id
142: AND fin_year = p_fin_year;
142: AND fin_year = p_fin_year;
143:
144: IF vMaxSlno IS NOT NULL THEN
145: SELECT NVL(balance_packed,0), NVL(balance_loose,0) INTO vBalancePacked, vBalanceLoose
146: FROM JAI_CMN_RG_I_TRXS
147: WHERE organization_id = p_organization_id
148: and location_id = p_location_id
149: and inventory_item_id = p_inventory_item_id
150: AND fin_year = p_fin_year
154: -- If execution comes here, then it means it is an ISSUE type of transaction and no balances available
155: p_err_buf := 'Enough RG1 balance is not available to Issue the Goods';
156: END IF;
157: */
158: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
159: vBalanceLoose := jai_om_rg_pkg.ja_in_rgi_balance(p_organization_id,p_location_id,p_inventory_item_id,p_fin_year,
160: vMaxSlno,vBalancePacked);
161: /*Bug 9550254 - End*/
162:
196:
197: v_transaction_id NUMBER := -1;
198:
199: /* Added by Ramananda for bug#4407165 */
200: lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.get_rg1_transaction_id';
201:
202: BEGIN
203: IF p_called_from = 'MANUAL ENTRY' THEN
204: IF p_transaction_type = 'R' THEN
310: P_SH_CESS_AMOUNT IN NUMBER DEFAULT NULL/*Bug 5989740 bduvarag*/
311: ) IS
312:
313: /* Added by Ramananda for bug#4407165 */
314: lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.create_rg1_entry';
315:
316: vMaxSlno NUMBER;
317: v_transaction_id JAI_CMN_RG_I_TRXS.TRANSACTION_SOURCE_NUM%TYPE;
318: v_primary_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
313: /* Added by Ramananda for bug#4407165 */
314: lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.create_rg1_entry';
315:
316: vMaxSlno NUMBER;
317: v_transaction_id JAI_CMN_RG_I_TRXS.TRANSACTION_SOURCE_NUM%TYPE;
318: v_primary_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
319: v_transaction_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
320: vTransToPrimaryUOMConv NUMBER;
321:
395:
396: /*Bug 9550254 - Start*/
397: /*
398: SELECT max(slno) INTO vMaxSlno
399: FROM JAI_CMN_RG_I_TRXS
400: WHERE organization_id = p_organization_id
401: AND location_id = p_location_id
402: AND inventory_item_id = p_inventory_item_id
403: AND fin_year = p_fin_year;
406: vMaxSlno,vBalancePacked);
407:
408: IF vMaxSlno IS NOT NULL THEN
409: SELECT NVL(balance_packed,0), NVL(balance_loose,0) INTO vBalancePacked, vBalanceLoose
410: FROM JAI_CMN_RG_I_TRXS
411: WHERE organization_id = p_organization_id
412: and location_id = p_location_id
413: and inventory_item_id = p_inventory_item_id
414: AND fin_year = p_fin_year
480: ELSE
481: P_SLNO := vMaxSlno + 1;
482: END IF;
483:
484: SELECT JAI_CMN_RG_I_TRXS_S.nextval INTO P_REGISTER_ID FROM DUAL;
485:
486: v_transaction_id := get_rg1_transaction_id(
487: p_transaction_type,
488: p_issue_type,
489: p_called_from
490: );
491:
492: --added rounding precision with 5 digits for bug#9466919
493: INSERT INTO JAI_CMN_RG_I_TRXS(
494: REGISTER_ID,
495: REGISTER_ID_PART_II,
496: FIN_YEAR,
497: SLNO,
617: app_exception.raise_exception;
618:
619: END create_rg1_entry;
620:
621: END jai_cmn_rg_i_trxs_pkg;