[Home] [Help]
23:
24: BEGIN
25:
26: -- We assume that these information are common for all inspection
27: -- records entered in qa_results.
28:
29: OPEN dtl_info;
30: FETCH dtl_info INTO l_org_id, l_item_id, l_lpn_id;
31: CLOSE dtl_info;
94:
95: -- OPM Conv R12 Tracking Bug 4345760
96: -- change variable size for lot num
97:
98: l_lot_num qa_results.lot_number%TYPE;
99:
100:
101: l_serial_num VARCHAR2(30);
102: l_quantity NUMBER;
161:
162: -- OPM Conv R12 Tracking Bug 4345760
163: -- change variable size for lot num
164:
165: l_lot_number qa_results.lot_number%TYPE;
166:
167:
168: l_serial_number VARCHAR2(30);
169: l_insp_qty NUMBER;
267:
268: -- OPM Conv R12 Tracking Bug 4345760
269: -- change variable size for lot num
270:
271: l_lot_number qa_results.lot_number%TYPE;
272:
273:
274: l_serial_number VARCHAR2(30);
275: l_qty_rem NUMBER;
483:
484: -- OPM Conv R12 Tracking Bug 4345760
485: -- change variable size for lot num
486:
487: l_lot_number qa_results.lot_number%TYPE;
488:
489:
490: l_serial_number VARCHAR2(30);
491: l_insp_result VARCHAR2(80);
525: BEGIN
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);
868:
869: res_col varchar2(20);
870:
871: begin
872: --this function returns the softcoded column in qa_results
873: --where Inspection Result is stored for the given plan_id
874: open res_cur;
875:
876: fetch res_cur into res_col;
1434: -- can be in any language, we use in statement to check
1435: -- inspection result in all possible language
1436: --
1437: /*
1438: sql_str := 'select sum(quantity) from qa_results where exists ' ||
1439: '(select ' || result_column || ' from qa_results ' ||
1440: 'where collection_id = :1 and plan_id = :2 and '||
1441: result_column || ' in (' || in_str || ' ))';
1442: */
1435: -- inspection result in all possible language
1436: --
1437: /*
1438: sql_str := 'select sum(quantity) from qa_results where exists ' ||
1439: '(select ' || result_column || ' from qa_results ' ||
1440: 'where collection_id = :1 and plan_id = :2 and '||
1441: result_column || ' in (' || in_str || ' ))';
1442: */
1443:
1447: -- restricting by plan_id and collection_id results in multiple
1448: -- rows being returned which are then summed up as rejected qty.
1449: -- This in turn causes the whole lot to be rejected inspite of high
1450: -- AQL. Following sql computes correct rejected qty by restricting the
1451: -- qa_results row by plan_id and collection_id.
1452:
1453: --
1454: -- Bug 6129041
1455: -- Commented the below code and replaced with the code which
1459: -- procedure executes as before.
1460: -- skolluku Wed Jul 11 03:37:20 PDT 2007
1461: --
1462: /*
1463: sql_str := 'select sum(quantity) from qa_results ' ||
1464: 'where collection_id = :1 and plan_id = :2 and ' ||
1465: result_column || ' in (' || in_str || ' )';
1466:
1467: execute immediate sql_str into reject_qty
1466:
1467: execute immediate sql_str into reject_qty
1468: using p_collection_id, p_coll_plan_id;
1469: */
1470: sql_str := 'select sum(quantity) from qa_results ' ||
1471: 'where collection_id = :1 and plan_id = :2 and ';
1472: if p_item_id is not null then
1473: sql_str := sql_str || ' item_id = :3 and ';
1474: end if;
1650: l_plan_count NUMBER;
1651:
1652: --BUG 4741324
1653: --Added a new variable to get a count of the LPN IDs
1654: --from qa_results for a particular Collection Id
1655: --ntungare Mon Nov 21 20:44:54 PST 2005
1656: l_license_plate_no_id number;
1657:
1658: -- Bug 8806035.ntungare
1690: CLOSE vend_lot_num;
1691:
1692: --
1693: -- Bug 4732741:
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
1693: -- Bug 4732741:
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.
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
1703: --
1701: -- launch_shipment_action_int.
1702: -- ntungare Mon Nov 14 21:11:30 PST 2005
1703: --
1704: SELECT Count(lpn_id) INTO l_license_plate_no_id
1705: FROM qa_results
1706: WHERE collection_id = p_collection_id;
1707:
1708: -- If the Plan count is 1, call the new procedure and return.
1709: -- IF (l_plan_count = 1) THEN
2183:
2184: -- OPM Conv R12 Tracking Bug 4345760
2185: -- change variable size for lot num
2186:
2187: l_lot_num qa_results.lot_number%TYPE;
2188:
2189: l_serial_num VARCHAR2(25);
2190: l_qty NUMBER;
2191:
2212:
2213: l_result_column := get_softcoded_column(l_plan_id);
2214:
2215: -- Construct the SQL for fetching REJECT and ACCEPT from
2216: -- the results entered in Inspection Plan in qa_results.
2217:
2218: in_str_reject :=
2219: ' select displayed_field' ||
2220: ' from po_lookup_codes' ||
2229: ' and lookup_code = ''ACCEPT''';
2230:
2231:
2232: l_sql_rej := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
2233: ' lot_number, serial_number, quantity from qa_results' ||
2234: ' where collection_id = :1 and plan_id = :2 and ' ||
2235: l_result_column || ' in (' || in_str_reject || ' )';
2236:
2237: l_sql_acc := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
2234: ' where collection_id = :1 and plan_id = :2 and ' ||
2235: l_result_column || ' in (' || in_str_reject || ' )';
2236:
2237: l_sql_acc := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
2238: ' lot_number, serial_number, quantity from qa_results' ||
2239: ' where collection_id = :1 and plan_id = :2 and ' ||
2240: l_result_column || ' in (' || in_str_accept || ' )';
2241:
2242: