[Home] [Help]
2: /* $Header: qasamplb.pls 120.4.12010000.4 2010/02/11 08:46:02 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:
526: -- We dont need to check the sampling flag here because
527: -- launch_shipment_action() calls this only for sampling scenario.
528:
529: -- First, post the Inspection details from qa_results onto
530: -- the temp table qa_insp_collections_dtl_temp.
531: -- Here we build the detail temp table for the plan.
532:
533: post_insp_coll_details(p_collection_id);
534:
548: FETCH lot_insp_cur INTO l_lot_qty_insp;
549: CLOSE lot_insp_cur;
550:
551: -- Get the total lot quantity, not inspected. This needs to be inserted
552: -- onto qa_insp_collections_dtl_temp. Call the post_lot_qty_not_insp()
553: -- for this.
554:
555: l_lot_qty_not_insp := p_lot_size - l_lot_qty_insp;
556:
567: END IF;
568:
569:
570: -- If the lot_result is 'REJECT, then update the insp_result of
571: -- qa_insp_collections_dtl_temp with 'REJECT' for the collection_id.
572:
573: IF p_lot_result = 'REJECT' THEN
574: upd_insp_coll_dtl_result(p_collection_id);
575:
581: OPEN vend_lot_num(p_transaction_id);
582: FETCH vend_lot_num INTO l_vendor_lot_num;
583: CLOSE vend_lot_num;
584:
585: -- Now, fetch the records in qa_insp_collections_dtl_temp for calling the
586: -- RCV API. We have grouped the records in cursor so that it gives the
587: -- consolidated picture.
588:
589: OPEN insp_coll_dtl;
1694: -- The following select stmt is based on qa_results which is similar
1695: -- to the way LPN_ID's are fetched and processed in the procedure
1696: -- launch_shipment_action_int. In that procedure we fetch the
1697: -- lpn_id's from qa_results and post them in to temp table
1698: -- qa_insp_collections_dtl_temp and then process them. So based
1699: -- this cursor to fetch count of lpn_id from qa_results for the particular
1700: -- collection_id and if count is 0 stop calling the procedure
1701: -- launch_shipment_action_int.
1702: -- ntungare Mon Nov 14 21:11:30 PST 2005
2158:
2159: --
2160: -- Bug 3096256. Added the following procedures for RCV/WMS Merge.
2161: -- This procedure inserts the detailed Inspection results onto
2162: -- qa_insp_collections_dtl_temp. This enables unit wise inspection
2163: -- with LPN and at Lot/Serial levels.
2164: -- Called from launch_shipment_action_int() of QA_SAMPLING_PKG and
2165: -- QA_SKIPLOT_RES_ENGINE.
2166: -- kabalakr Fri Aug 29 09:06:28 PDT 2003.
2240: l_result_column || ' in (' || in_str_accept || ' )';
2241:
2242:
2243: -- Get the required info from the results entered for the plan for 'ACCEPT'
2244: -- and insert the same onto qa_insp_collections_dtl_temp.
2245:
2246: OPEN insp_acc FOR l_sql_acc USING p_collection_id, l_plan_id;
2247: LOOP
2248:
2256: l_qty;
2257:
2258: EXIT WHEN insp_acc%NOTFOUND;
2259:
2260: insert into qa_insp_collections_dtl_temp
2261: (collection_id,
2262: occurrence,
2263: organization_id,
2264: item_id,
2287: CLOSE insp_acc;
2288:
2289:
2290: -- Get the required info from the results entered for the plan for 'REJECT'
2291: -- and insert the same onto qa_insp_collections_dtl_temp.
2292:
2293: OPEN insp_rej FOR l_sql_rej USING p_collection_id, l_plan_id;
2294: LOOP
2295:
2303: l_qty;
2304:
2305: EXIT WHEN insp_rej%NOTFOUND;
2306:
2307: insert into qa_insp_collections_dtl_temp
2308: (collection_id,
2309: occurrence,
2310: organization_id,
2311: item_id,