[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);
284: S.No Date(DD/MM/YY) Author and Details of Changes
285: ---- -------------- -----------------------------
286: 1 25/07/02 Vijay Shankar, Bug# 2480584, Version: 615.1
287: Added Code for back tracking from RG1. From now onwards deleveloper can back track RG1 record corresponding
288: to MTL_MATERIAL_TRANSACTIONS as JAI_CMN_RG_I_TRXS.REF_DOC_NO = to_char(MTL_MATERIAL_TRANSACTIONS.transaction_id)
289: Code is modified to take care of bonded and trading flags, if the subinventories are not defined in localization setup.
290: During Subinventory transacfer transaction(i.e transaction_action_id = 2), the serial number and balances
291: are not calculated properly, which is resolved in this bug.
292:
312: 5 31/12/02 cbabu for Bug# 2728521, Version: 615.5
313: Coding is done as per the functional inputs specified in the above bug for subinvetory transfer transaction
314:
315: 6 13/01/03 Nagaraj.s for Bug#2744695 Version : 615.6
316: Changed the Insert statement of JAI_CMN_RG_I_TRXS table from
317: --to_other_factory_n_pay_ed_qty to other_purpose_n_pay_ed_qty as per
318: the Functional Requirement.
319: and also previously for FGIN and CCIN class of Items, the v_trans_qty
320: was null which is changed to the Abs(v_new_trans_qty) for CCIN class.
953: END IF;
954:
955: IF v_rg_flag = 1 THEN
956: -- -- jai_cmn_utils_pkg.print_log('rg1.log','before 1');
957: INSERT INTO JAI_CMN_RG_I_TRXS(
958: register_id, fin_year, slno, manufactured_qty, manufactured_packed_qty,
959: manufactured_loose_qty, balance_packed, balance_loose,
960: to_other_factory_n_pay_ed_qty,
961: for_export_n_pay_ed_qty, other_purpose_n_pay_ed_qty, last_update_date, last_updated_by,
963: inventory_item_id, organization_id, transaction_type, range_no,
964: division_no, location_id, transaction_uom_code, transaction_date,
965: primary_uom_code, REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
966: ) VALUES (
967: JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno1, ABS(v_manu_qty),
968: ABS(v_manu_pkd_qty), ABS(v_manu_loose_qty), v_bal_packed, v_bal_loose, --abs(v_trans_qty), commented by Sriram on 26-Dec-2001
969: NULL, -- abs(v_t_qty), commented by Sriram on 26-Dec-2001
970: NULL, ABS(v_trans_qty), pr_new.last_update_date, pr_new.last_updated_by,
971: SYSDATE, pr_new.created_by, pr_new.last_update_login, v_temp_trans_type_id,
1464: -- v_bal_packed := NVL(v_bal_packed,0) + NVL(v_manu_pkd_qty,0);
1465: v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_new_trans_qty, 0);
1466: END IF;
1467: -- jai_cmn_utils_pkg.print_log('rg1.log','before 2');
1468: INSERT INTO JAI_CMN_RG_I_TRXS(
1469: register_id, fin_year, slno, manufactured_qty,
1470: manufactured_packed_qty, manufactured_loose_qty,
1471: balance_packed, balance_loose,
1472: to_other_factory_n_pay_ed_qty,
1477: transaction_type, range_no, division_no, location_id,
1478: transaction_uom_code, transaction_date,
1479: primary_uom_code,
1480: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1481: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, ABS(v_manu_qty),
1482: ABS(v_manu_pkd_qty), ABS(v_manu_loose_qty),
1483: v_bal_packed, v_bal_loose,
1484: ABS (v_trans_qty),
1485: v_t_qty, pr_new.last_update_date,
1647: END IF;
1648:
1649:
1650: -- jai_cmn_utils_pkg.print_log('rg1.log','before 3');
1651: INSERT INTO JAI_CMN_RG_I_TRXS(
1652: register_id, fin_year, slno, manufactured_qty,
1653: manufactured_packed_qty, manufactured_loose_qty,
1654: balance_packed, balance_loose,
1655: --to_other_factory_n_pay_ed_qty, Commented by Nagaraj.s for Bug#2744695
1661: transaction_type, range_no, division_no, location_id,
1662: transaction_uom_code, transaction_date,
1663: primary_uom_code,
1664: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1665: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, ABS(v_manu_qty),
1666: ABS(v_manu_pkd_qty), ABS(v_manu_loose_qty),
1667: v_bal_packed, v_bal_loose,
1668: ABS (v_hit_rg1_qty),
1669: v_t_qty, pr_new.last_update_date,
1993: -- End added by bug#3258066
1994:
1995: -- jai_cmn_utils_pkg.print_log('rg1.log','before 4');
1996:
1997: INSERT INTO JAI_CMN_RG_I_TRXS (
1998: register_id, fin_year, slno,
1999: manufactured_qty, manufactured_packed_qty,
2000: manufactured_loose_qty, balance_packed, balance_loose,
2001: -- other_purpose_n_pay_ed_qty, -- by sriram
2010: range_no, division_no, location_id,
2011: transaction_uom_code, transaction_date,
2012: primary_uom_code,
2013: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2014: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2015: v_manufactured_qty, v_manufactured_pack_qty,
2016: v_manu_loose_qty, v_bal_packed, v_bal_loose, -- v_manu_loose_qty changed to null by sriram bug # 3258086on product management advice.
2017: v_oth_pur_npe_qty, v_oth_purpose,
2018: v_other_npe_qty, NULL, --earlier 'PI' corresponding field is number -- put v_manu_loose_qty instead of v_trans_qty - sriram
2198: END IF;
2199:
2200:
2201: -- jai_cmn_utils_pkg.print_log('rg1.log','before 5');
2202: INSERT INTO JAI_CMN_RG_I_TRXS (
2203: register_id, fin_year, slno,
2204: manufactured_qty, manufactured_packed_qty,
2205: manufactured_loose_qty, balance_packed, balance_loose,
2206: -- other_purpose_n_pay_ed_qty, -- by sriram
2215: range_no, division_no, location_id,
2216: transaction_uom_code, transaction_date,
2217: primary_uom_code,
2218: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2219: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2220: v_manufactured_qty, v_manufactured_pack_qty,
2221: Null, v_bal_packed, v_bal_loose, -- v_manu_loose_qty set to null by sriram - bug # 3258066 based on product management advice.
2222: v_oth_pur_npe_qty, v_oth_purpose,
2223: v_manu_loose_qty, 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.
2507: + NVL (v_new_trans_qty, 0);
2508: END IF;
2509:
2510: -- jai_cmn_utils_pkg.print_log('rg1.log','before 6');
2511: INSERT INTO JAI_CMN_RG_I_TRXS
2512: (register_id, fin_year, slno,
2513: manufactured_qty, manufactured_packed_qty,
2514: manufactured_loose_qty, balance_packed,
2515: balance_loose, to_other_factory_n_pay_ed_qty,
2520: transaction_type, range_no, division_no, location_id,
2521: transaction_uom_code, transaction_date,
2522: primary_uom_code,
2523: REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2524: ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2525: v_manufactured_qty, v_manufactured_pack_qty,
2526: v_manu_loose_qty, v_bal_packed,
2527: v_bal_loose, v_trans_qty,
2528: v_t_qty, pr_new.last_update_date,