[Home] [Help]
183: - ***********************************************************************/
184: PROCEDURE Assign_Cursor_Values_To_Table
185: (p_orgn_code IN op_ordr_hdr.orgn_code%TYPE,
186: p_cust_id IN op_cust_mst.cust_id%TYPE,
187: p_item_id IN ic_item_mst.item_id%TYPE,
188: p_whse_code IN ic_whse_mst.whse_code%TYPE,
189: p_lot_id IN ic_lots_mst.lot_id%TYPE,
190: p_qc_spec_id IN gmd_specifications_b.spec_id%TYPE,
191: p_assay_code IN gmd_qc_tests_b.test_code%TYPE,
852:
853: /* BEGIN BUG#1810652 James Bernard */
854: /* Added 'c_item_id is NULL or' in the where clause */
855: CURSOR get_cust_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
856: c_item_id ic_item_mst.item_id%TYPE,
857: c_lot_id ic_lots_mst.lot_id%TYPE,
858: c_cust_id op_cust_mst.cust_id%TYPE) IS
859: select gr.result_id qc_result_id,
860: gst.spec_id qc_spec_id,
904: /* use the next cursor if no rows returned from get_cust_rslt_info */
905: /* BEGIN BUG#1810652 James Bernard */
906: /* Added 'c_item_id is NULL or' and 'c_lot_id is NULL or' in the where clause */
907: CURSOR get_item_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
908: c_item_id ic_item_mst.item_id%TYPE,
909: c_lot_id ic_lots_mst.lot_id%TYPE) IS
910: select gr.result_id qc_result_id,
911: gst.spec_id qc_spec_id,
912: gt.test_code assay_code,
961:
962: /*END BUG#1810652 */
963:
964:
965: CURSOR get_cust_spec (c_item_id ic_item_mst.item_id%TYPE,
966: c_cust_id op_cust_mst.cust_id%TYPE,
967: c_orgn_code gmd_customer_spec_vrs.ORGN_CODE%TYPE,
968: c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
969: IS
983: and gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
984: and gcs.orgn_code = c_orgn_code
985: and gsb.delete_mark= 0;
986:
987: CURSOR get_global_cust_spec (c_item_id ic_item_mst.item_id%TYPE,
988: c_cust_id op_cust_mst.cust_id%TYPE,
989: c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
990: IS
991: select gsb.spec_id qc_spec_id,
1401: WHERE bol_id = c_bol_id
1402: and delete_mark = 0
1403: ;
1404:
1405: CURSOR get_item_info (c_item_id ic_item_mst.item_id%TYPE) IS
1406: SELECT item_no,
1407: item_desc1
1408: FROM
1409: ic_item_mst
1405: CURSOR get_item_info (c_item_id ic_item_mst.item_id%TYPE) IS
1406: SELECT item_no,
1407: item_desc1
1408: FROM
1409: ic_item_mst
1410: WHERE item_id = c_item_id
1411: and delete_mark = 0
1412: ;
1413:
1670: tbl_hdr(tbl_ndx).shipdate := ord_cur_rec.actual_shipdate;
1671: END IF;
1672:
1673: /* if generic id exists in sales order record, get item no and desc */
1674: /* from op_gnrc_itm, else get item no and desc from ic_item_mst */
1675:
1676: IF ord_cur_rec.generic_id is not NULL THEN
1677: FOR gnrc_cur_rec IN get_gnrc_info (ord_cur_rec.generic_id) LOOP
1678: tbl_hdr(tbl_ndx).item_no := gnrc_cur_rec.generic_item;
1683: FOR item_cur_rec IN get_item_info (ord_cur_rec.item_id) LOOP
1684: tbl_hdr(tbl_ndx).item_no := item_cur_rec.item_no;
1685: tbl_hdr(tbl_ndx).item_desc := item_cur_rec.item_desc1;
1686: END LOOP;
1687: /* end getting item info from ic_item_mst */
1688: END IF;
1689: /* end if generic_id has a value */
1690: /* fill in whse, customer name, bol no at end of code */
1691:
1788: /* end getting cust info from hz tables */
1789: END IF;
1790: /* end checking if cust id has a value */
1791:
1792: /* get item no and description from ic_item_mst if it was not */
1793: /* selected as part of generic item check. */
1794: /* It is correct to check item_NO, not item_ID! */
1795:
1796: IF tbl_hdr(loop_counter).item_no is NULL THEN
1797: FOR item_cur_rec IN get_item_info (tbl_hdr(loop_counter).item_id) LOOP
1798: tbl_hdr(loop_counter).item_no := item_cur_rec.item_no;
1799: tbl_hdr(loop_counter).item_desc := item_cur_rec.item_desc1;
1800: END LOOP;
1801: /* end getting item info from ic_item_mst */
1802: END IF;
1803: /* end checking if item id has a value */
1804:
1805: /* get warehouse no and name from ic_whse_mst */