[Home] [Help]
2: /* $Header: MSCXHZUB.pls 120.2 2005/07/28 03:38:28 pragarwa noship $ */
3:
4:
5: /**
6: * The foll procedure has to update msc_sup_dem_entries with the
7: * updated values from the msc_hz_ui_lines table.
8: * The proc has to do the foll.
9: * - using the query id - select the rows that have the quantities updated
10: * (where qty_bucket(X) <> old_qty(X) )
8: * The proc has to do the foll.
9: * - using the query id - select the rows that have the quantities updated
10: * (where qty_bucket(X) <> old_qty(X) )
11: * - for each of the delta values - select the corresponding
12: * transactionids from msc_sup_dem_entries.
13: * order by quantity and createion date.
14: * - update the delta appropriately
15: */
16:
245: k number;
246: i number;
247:
248:
249: -- cursor to fetch the transaction ids from msc_sup_dem_entries;
250: -- using primary_quantity for orde by as for publisher we always display the primary qty on the hz view
251: -- and only publisher can edit the records on HZ View.
252: CURSOR c_transids (arg_item IN NUMBER, arg_order_type IN NUMBER, arg_cust IN VARCHAR2,
253: arg_sup IN VARCHAR2, arg_cust_site IN VARCHAR2, arg_sup_site IN VARCHAR2,
253: arg_sup IN VARCHAR2, arg_cust_site IN VARCHAR2, arg_sup_site IN VARCHAR2,
254: arg_start IN DATE, arg_end IN DATE)
255: IS
256: SELECT transaction_id,primary_quantity,primary_uom,uom_code,tp_uom_code,inventory_item_id
257: FROM msc_sup_dem_entries_ui_v
258: WHERE inventory_item_id = arg_item
259: AND customer_name = arg_cust
260: AND supplier_name = arg_sup
261: AND customer_site_name= arg_cust_site
1289:
1290: --dbms_output.put_line(' pkaligot : 199 : before update sup dem entries block: trans count: ' || v_upd_trans_id.COUNT);
1291: if v_upd_trans_id is not null AND v_upd_trans_id.COUNT > 0 and proceed = 0 then
1292: --dbms_output.put_line(' pkaligot : 200 : update sup dem entries block: trans count: ' || v_upd_trans_id.COUNT );
1293: -- update into msc_sup_dem_entries;
1294: BEGIN
1295: FORALL i in v_upd_trans_id.FIRST..v_upd_trans_id.LAST
1296:
1297: update msc_sup_dem_entries
1293: -- update into msc_sup_dem_entries;
1294: BEGIN
1295: FORALL i in v_upd_trans_id.FIRST..v_upd_trans_id.LAST
1296:
1297: update msc_sup_dem_entries
1298: set primary_quantity = round(v_upd_p_qty(i),DECPLACES),
1299: quantity = round(v_upd_qty(i),DECPLACES),
1300: tp_quantity = round(v_upd_tp_qty(i),DECPLACES),
1301: last_refresh_number = MSC_CL_REFRESH_S.NEXTVAL,
1313: arg_err_msg := 'MSC_X_HZ_UPDATE@update ' || SQLERRM;
1314: END;
1315: end if;
1316:
1317: -- insert into msc_sup_dem_entries.
1318:
1319: --dbms_output.put_line(' pkaligot : 200 : v_i_item.COUNT:' || v_i_item.COUNT );
1320: --dbms_output.put_line(' pkaligot : 201 : proceed :' || proceed );
1321:
1326: BEGIN
1327:
1328: FORALL i in v_i_item_id.FIRST..v_i_item_id.LAST
1329:
1330: insert into msc_sup_dem_entries
1331: (transaction_id,inventory_item_id,item_name,item_description,
1332: owner_item_name,customer_item_name,supplier_item_name,
1333: owner_item_description,customer_item_description,supplier_item_description,
1334: publisher_id,publisher_name,publisher_site_name,
1344: ship_from_party_name,ship_from_party_id,ship_from_party_site_name,ship_from_party_site_id,
1345: ship_to_party_name,ship_to_party_id,ship_to_party_site_name,ship_to_party_site_id,
1346: sr_instance_id)
1347: values
1348: (MSC_SUP_DEM_ENTRIES_S.nextval,v_i_item_id(i),v_i_item(i),Nvl(v_i_item_desc(i),v_i_owner_item_desc(i)),
1349: v_i_owner_item(i),v_i_cust_item(i),v_i_sup_item(i),
1350: v_i_owner_item_desc(i),v_i_cust_item_desc(i),v_i_sup_item_desc(i),
1351: v_pub_id,v_pub_name,v_i_pub_site_name(i),
1352: v_i_cust_id(i),v_i_cust_site_id(i),