[Home] [Help]
145:
146: /*
147: CURSOR SRNO_II_CUR ( p_loc_id IN NUMBER, p_f_year IN NUMBER) IS
148: SELECT MAX(slno)
149: FROM JAI_CMN_RG_I_TRXS
150: WHERE organization_id = pr_new.organization_id and
151: location_id = p_loc_id and
152: inventory_item_id = pr_new.inventory_item_id and
153: fin_year = p_f_year;
155:
156: CURSOR srno_ii_cur (p_loc_id IN NUMBER, p_f_year IN NUMBER)
157: IS
158: SELECT slno, balance_packed, balance_loose
159: FROM JAI_CMN_RG_I_TRXS
160: WHERE organization_id = pr_new.organization_id
161: AND location_id = p_loc_id
162: AND inventory_item_id = pr_new.inventory_item_id
163: AND fin_year = p_f_year
161: AND location_id = p_loc_id
162: AND inventory_item_id = pr_new.inventory_item_id
163: AND fin_year = p_f_year
164: AND slno = (SELECT MAX (slno)
165: FROM JAI_CMN_RG_I_TRXS
166: WHERE organization_id = pr_new.organization_id
167: AND location_id = p_loc_id
168: AND inventory_item_id = pr_new.inventory_item_id
169: AND fin_year = p_f_year);
309: S.No Date(DD/MM/YY) Author and Details of Changes
310: ---- -------------- -----------------------------
311: 1 25/07/02 Vijay Shankar, Bug# 2480584, Version: 615.1
312: Added Code for back tracking from RG1. From now onwards deleveloper can back track RG1 record corresponding
313: to MTL_MATERIAL_TRANSACTIONS as JAI_CMN_RG_I_TRXS.REF_DOC_NO = to_char(MTL_MATERIAL_TRANSACTIONS.transaction_id)
314: Code is modified to take care of bonded and trading flags, if the subinventories are not defined in localization setup.
315: During Subinventory transacfer transaction(i.e transaction_action_id = 2), the serial number and balances
316: are not calculated properly, which is resolved in this bug.
317:
337: 5 31/12/02 cbabu for Bug# 2728521, Version: 615.5
338: Coding is done as per the functional inputs specified in the above bug for subinvetory transfer transaction
339:
340: 6 13/01/03 Nagaraj.s for Bug#2744695 Version : 615.6
341: Changed the Insert statement of JAI_CMN_RG_I_TRXS table from
342: --to_other_factory_n_pay_ed_qty to other_purpose_n_pay_ed_qty as per
343: the Functional Requirement.
344: and also previously for FGIN and CCIN class of Items, the v_trans_qty
345: was null which is changed to the Abs(v_new_trans_qty) for CCIN class.
945: --FETCH SRNO_II_CUR INTO v_srno,v_bal_packed,v_bal_loose; --commented by --sriram on 18-may-01
946: FETCH srno_ii_cur INTO v_srno1, v_bal_packed, v_bal_loose;
947: CLOSE srno_ii_cur;
948: */
949: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
950: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_temp_loc_id,pr_new.inventory_item_id,v_f_year,
951: v_srno1,v_bal_packed);
952: /*Bug 9550254 - End*/
953:
1052:
1053: IF v_rg_flag = 1 THEN
1054: -- -- jai_cmn_utils_pkg.print_log('rg1.log','before 1');
1055: --added rounding precision of 5 for bug 9466919
1056: INSERT INTO JAI_CMN_RG_I_TRXS(
1057: register_id, fin_year, slno, manufactured_qty, manufactured_packed_qty,
1058: manufactured_loose_qty, balance_packed, balance_loose,
1059: to_other_factory_n_pay_ed_qty,
1060: for_export_n_pay_ed_qty, other_purpose_n_pay_ed_qty, last_update_date, last_updated_by,
1062: inventory_item_id, organization_id, transaction_type, range_no,
1063: division_no, location_id, transaction_uom_code, transaction_date,
1064: primary_uom_code, REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1065: ) VALUES (
1066: JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno1,round( ABS(v_manu_qty),5),
1067: round(ABS(v_manu_pkd_qty),5), round(ABS(v_manu_loose_qty),5),round( v_bal_packed,5), round(v_bal_loose,5), --abs(v_trans_qty), commented by Sriram on 26-Dec-2001
1068: NULL, -- abs(v_t_qty), commented by Sriram on 26-Dec-2001
1069: NULL, round(ABS(v_trans_qty),5), pr_new.last_update_date, pr_new.last_updated_by,
1070: SYSDATE, pr_new.created_by, pr_new.last_update_login, v_temp_trans_type_id,
1235: -- FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
1236: FETCH srno_ii_cur INTO v_srno1, v_bal_packed, v_bal_loose;
1237: CLOSE srno_ii_cur;
1238: */
1239: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
1240: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
1241: v_srno1,v_bal_packed);
1242: /*Bug 9550254 - End*/
1243:
1574: OPEN srno_ii_cur (v_loc_id, v_f_year);
1575: FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
1576: CLOSE srno_ii_cur;
1577: */
1578: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
1579: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,v_srno,v_bal_packed);
1580: /*Bug 9550254 - Start*/
1581:
1582: IF NVL (v_srno, 0) = 0 THEN
1627: -- v_bal_packed := NVL(v_bal_packed,0) + NVL(v_manu_pkd_qty,0);
1628: v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_new_trans_qty, 0);
1629: END IF;
1630: -- jai_cmn_utils_pkg.print_log('rg1.log','before 2');
1631: INSERT INTO JAI_CMN_RG_I_TRXS(
1632: register_id, fin_year, slno, manufactured_qty,
1633: manufactured_packed_qty, manufactured_loose_qty,
1634: balance_packed, balance_loose,
1635: to_other_factory_n_pay_ed_qty,
1640: transaction_type, range_no, division_no, location_id,
1641: transaction_uom_code, transaction_date,
1642: primary_uom_code,
1643: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1644: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, round(ABS(v_manu_qty),5),
1645: round(ABS(v_manu_pkd_qty),5), round(ABS(v_manu_loose_qty),5),
1646: round(v_bal_packed,5), round(v_bal_loose,5),
1647: round(ABS (v_trans_qty),5),
1648: round(v_t_qty,5), pr_new.last_update_date,
1810: OPEN srno_ii_cur (v_loc_id, v_f_year);
1811: FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
1812: CLOSE srno_ii_cur;
1813: */
1814: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
1815: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
1816: v_srno,v_bal_packed);
1817: /*Bug 9550254 - End*/
1818:
1845: END IF;
1846:
1847:
1848: -- jai_cmn_utils_pkg.print_log('rg1.log','before 3');
1849: INSERT INTO JAI_CMN_RG_I_TRXS(
1850: register_id, fin_year, slno, manufactured_qty,
1851: manufactured_packed_qty, manufactured_loose_qty,
1852: balance_packed, balance_loose,
1853: --to_other_factory_n_pay_ed_qty, Commented by Nagaraj.s for Bug#2744695
1859: transaction_type, range_no, division_no, location_id,
1860: transaction_uom_code, transaction_date,
1861: primary_uom_code,
1862: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1863: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, round(ABS(v_manu_qty),5),
1864: round(ABS(v_manu_pkd_qty),5),round( ABS(v_manu_loose_qty),5),
1865: round(v_bal_packed,5), round(v_bal_loose,5),
1866: round(ABS (v_hit_rg1_qty),5),
1867: round(v_t_qty,5), pr_new.last_update_date,
2133: OPEN srno_ii_cur (v_loc_id, v_f_year);
2134: FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
2135: CLOSE srno_ii_cur;
2136: */
2137: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2138: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
2139: v_srno,v_bal_packed);
2140:
2141: /*Bug 9550254 - End*/
2227: -- End added by bug#3258066
2228:
2229: -- jai_cmn_utils_pkg.print_log('rg1.log','before 4');
2230:
2231: INSERT INTO JAI_CMN_RG_I_TRXS (
2232: register_id, fin_year, slno,
2233: manufactured_qty, manufactured_packed_qty,
2234: manufactured_loose_qty, balance_packed, balance_loose,
2235: -- other_purpose_n_pay_ed_qty, -- by sriram
2244: range_no, division_no, location_id,
2245: transaction_uom_code, transaction_date,
2246: primary_uom_code,
2247: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2248: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2249: round(v_manufactured_qty,5), round(v_manufactured_pack_qty,5),
2250: round(v_manu_loose_qty,5), round(v_bal_packed,5), round(v_bal_loose,5), -- v_manu_loose_qty changed to null by sriram bug # 3258086on product management advice.
2251: round(v_oth_pur_npe_qty,5), v_oth_purpose,
2252: round(v_other_npe_qty,5), NULL, --earlier 'PI' corresponding field is number -- put v_manu_loose_qty instead of v_trans_qty - sriram
2410: OPEN srno_ii_cur (v_loc_id, v_f_year);
2411: FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
2412: CLOSE srno_ii_cur;
2413: */
2414: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2415: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
2416: v_srno,v_bal_packed);
2417: /*Bug 9550254 - End*/
2418: IF NVL (v_srno, 0) = 0 THEN
2467: END IF;
2468:
2469:
2470: -- jai_cmn_utils_pkg.print_log('rg1.log','before 5');
2471: INSERT INTO JAI_CMN_RG_I_TRXS (
2472: register_id, fin_year, slno,
2473: manufactured_qty, manufactured_packed_qty,
2474: manufactured_loose_qty, balance_packed, balance_loose,
2475: -- other_purpose_n_pay_ed_qty, -- by sriram
2484: range_no, division_no, location_id,
2485: transaction_uom_code, transaction_date,
2486: primary_uom_code,
2487: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2488: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2489: round(v_manufactured_qty,5), round(v_manufactured_pack_qty,5),
2490: Null, round(v_bal_packed,5), round(v_bal_loose,5), -- v_manu_loose_qty set to null by sriram - bug # 3258066 based on product management advice.
2491: round(v_oth_pur_npe_qty,5), v_oth_purpose,
2492: round( v_manu_loose_qty,5), NULL, --earlier 'PI' corresponding field is number -- v_trans_qty changed to v_manu_loose_qty by sriram - bug # 3258066 based on p.m advice.
2790: OPEN srno_ii_cur (v_loc_id, v_f_year);
2791: FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
2792: CLOSE srno_ii_cur;
2793: */
2794: /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2795: v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
2796: v_srno,v_bal_packed);
2797: /*Bug 9550254 - End*/
2798:
2846: + NVL (v_new_trans_qty, 0);
2847: END IF;
2848:
2849: -- jai_cmn_utils_pkg.print_log('rg1.log','before 6');
2850: INSERT INTO JAI_CMN_RG_I_TRXS
2851: (register_id, fin_year, slno,
2852: manufactured_qty, manufactured_packed_qty,
2853: manufactured_loose_qty, balance_packed,
2854: balance_loose, to_other_factory_n_pay_ed_qty,
2859: transaction_type, range_no, division_no, location_id,
2860: transaction_uom_code, transaction_date,
2861: primary_uom_code,
2862: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2863: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2864: round(v_manufactured_qty,5), round(v_manufactured_pack_qty,5),
2865: round(v_manu_loose_qty,5), round( v_bal_packed,5),
2866: round( v_bal_loose,5), round( v_trans_qty,5),
2867: round(v_t_qty,5), pr_new.last_update_date,