[Home] [Help]
2: /* $Header: qasamplb.pls 120.4 2008/02/08 09:14:48 pdube ship $ */
3:
4:
5: -- This procedure inserts the Details for the quantity left in the
6: -- lot, not inspected, onto the temp table - qa_insp_collections_dtl_temp.
7: -- Called from launch_shipment_action_int().
8: -- Bug 3096256. For RCV/WMS Merge. kabalakr Thu Aug 28 08:34:59 PDT 2003.
9:
10: PROCEDURE post_lot_qty_not_insp(p_collection_id IN NUMBER,
12: p_result IN VARCHAR2) IS
13:
14: CURSOR dtl_info IS
15: select organization_id, item_id, lpn_id
16: from qa_insp_collections_dtl_temp
17: where collection_id = p_collection_id
18: and rownum = 1;
19:
20: l_lpn_id NUMBER;
29: OPEN dtl_info;
30: FETCH dtl_info INTO l_org_id, l_item_id, l_lpn_id;
31: CLOSE dtl_info;
32:
33: -- Insert a record onto qa_insp_collections_dtl_temp for the Quantity
34: -- not inspected in the Lot.
35:
36: -- Bug 3229571. Passing Lot_number and serial_number as 'DEFERRED'. The Lot
37: -- and serial numbers associated with the lot qty not inspected, will be derived
36: -- Bug 3229571. Passing Lot_number and serial_number as 'DEFERRED'. The Lot
37: -- and serial numbers associated with the lot qty not inspected, will be derived
38: -- and attached to the inspection txn later. kabalakr.
39:
40: insert into qa_insp_collections_dtl_temp
41: (collection_id,
42: occurrence,
43: organization_id,
44: item_id,
65: END post_lot_qty_not_insp;
66:
67:
68:
69: -- This procedure updates the records in qa_insp_collections_dtl_temp
70: -- with insp_result = 'REJECT', when the lot_result is 'REJECT'.
71: -- Called from launch_shipment_action_int().
72: -- Bug 3096256. For RCV/WMS Merge. kabalakr Thu Aug 28 08:34:59 PDT 2003.
73:
76: l_sql_string VARCHAR2(200);
77:
78: BEGIN
79:
80: l_sql_string := 'UPDATE qa_insp_collections_dtl_temp '||
81: ' SET insp_result = ''REJECT'''||
82: ' WHERE collection_id = :1';
83:
84: EXECUTE IMMEDIATE l_sql_string USING p_collection_id;
178: and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
179:
180: CURSOR insp_dtl IS
181: select lot_number, serial_number, insp_qty
182: from qa_insp_collections_dtl_temp
183: where collection_id = p_collection_id;
184:
185:
186: BEGIN
459: where collection_id = p_collection_id;
460:
461: CURSOR lot_insp_cur IS
462: select sum(insp_qty) AS total_insp_qty
463: from qa_insp_collections_dtl_temp
464: where collection_id = p_collection_id;
465:
466: CURSOR insp_coll_dtl IS
467: select organization_id, item_id, lpn_id, xfr_lpn_id,
465:
466: CURSOR insp_coll_dtl IS
467: select organization_id, item_id, lpn_id, xfr_lpn_id,
468: lot_number, serial_number, insp_result, sum(insp_qty)
469: from qa_insp_collections_dtl_temp
470: where collection_id = p_collection_id
471: group by organization_id, item_id, lpn_id, xfr_lpn_id,
472: lot_number, serial_number, insp_result;
473:
518: -- We dont need to check the sampling flag here because
519: -- launch_shipment_action() calls this only for sampling scenario.
520:
521: -- First, post the Inspection details from qa_results onto
522: -- the temp table qa_insp_collections_dtl_temp.
523: -- Here we build the detail temp table for the plan.
524:
525: post_insp_coll_details(p_collection_id);
526:
540: FETCH lot_insp_cur INTO l_lot_qty_insp;
541: CLOSE lot_insp_cur;
542:
543: -- Get the total lot quantity, not inspected. This needs to be inserted
544: -- onto qa_insp_collections_dtl_temp. Call the post_lot_qty_not_insp()
545: -- for this.
546:
547: l_lot_qty_not_insp := p_lot_size - l_lot_qty_insp;
548:
559: END IF;
560:
561:
562: -- If the lot_result is 'REJECT, then update the insp_result of
563: -- qa_insp_collections_dtl_temp with 'REJECT' for the collection_id.
564:
565: IF p_lot_result = 'REJECT' THEN
566: upd_insp_coll_dtl_result(p_collection_id);
567:
567:
568: END IF;
569:
570:
571: -- Now, fetch the records in qa_insp_collections_dtl_temp for calling the
572: -- RCV API. We have grouped the records in cursor so that it gives the
573: -- consolidated picture.
574:
575: OPEN insp_coll_dtl;
1617: -- The following select stmt is based on qa_results which is similar
1618: -- to the way LPN_ID's are fetched and processed in the procedure
1619: -- launch_shipment_action_int. In that procedure we fetch the
1620: -- lpn_id's from qa_results and post them in to temp table
1621: -- qa_insp_collections_dtl_temp and then process them. So based
1622: -- this cursor to fetch count of lpn_id from qa_results for the particular
1623: -- collection_id and if count is 0 stop calling the procedure
1624: -- launch_shipment_action_int.
1625: -- ntungare Mon Nov 14 21:11:30 PST 2005
2081:
2082: --
2083: -- Bug 3096256. Added the following procedures for RCV/WMS Merge.
2084: -- This procedure inserts the detailed Inspection results onto
2085: -- qa_insp_collections_dtl_temp. This enables unit wise inspection
2086: -- with LPN and at Lot/Serial levels.
2087: -- Called from launch_shipment_action_int() of QA_SAMPLING_PKG and
2088: -- QA_SKIPLOT_RES_ENGINE.
2089: -- kabalakr Fri Aug 29 09:06:28 PDT 2003.
2163: l_result_column || ' in (' || in_str_accept || ' )';
2164:
2165:
2166: -- Get the required info from the results entered for the plan for 'ACCEPT'
2167: -- and insert the same onto qa_insp_collections_dtl_temp.
2168:
2169: OPEN insp_acc FOR l_sql_acc USING p_collection_id, l_plan_id;
2170: LOOP
2171:
2179: l_qty;
2180:
2181: EXIT WHEN insp_acc%NOTFOUND;
2182:
2183: insert into qa_insp_collections_dtl_temp
2184: (collection_id,
2185: occurrence,
2186: organization_id,
2187: item_id,
2210: CLOSE insp_acc;
2211:
2212:
2213: -- Get the required info from the results entered for the plan for 'REJECT'
2214: -- and insert the same onto qa_insp_collections_dtl_temp.
2215:
2216: OPEN insp_rej FOR l_sql_rej USING p_collection_id, l_plan_id;
2217: LOOP
2218:
2226: l_qty;
2227:
2228: EXIT WHEN insp_rej%NOTFOUND;
2229:
2230: insert into qa_insp_collections_dtl_temp
2231: (collection_id,
2232: occurrence,
2233: organization_id,
2234: item_id,