[Home] [Help]
63: gmd_customer_spec_vrs gcs,
64: gmd_spec_tests_b gst,
65: gmd_qc_tests_b gt
66: where
67: gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id= c_cust_id)
68: and (c_item_id is NULL or gsb.item_id = c_item_id)
69: and gcs.orgn_code = c_orgn_code
70: and gsb.spec_id = gcs.spec_id
71: and gsb.spec_id = gst.spec_id
94: gmd_customer_spec_vrs gcs,
95: gmd_spec_tests_b gst,
96: gmd_qc_tests_b gt
97: where
98: gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
99: and (c_item_id is NULL or gsb.item_id = c_item_id)
100: and gcs.orgn_code is null
101: and gsb.spec_id = gcs.spec_id
102: and gsb.spec_id = gst.spec_id
182: - variable, change this code!
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,
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,
861: gt.test_code assay_code,
862: gr.result_date result_date,
892: and gss.disposition = '4A' -- ACCEPT
893: and nvl(gsr.evaluation_ind,'N') in ('0A')
894: and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
895: gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
896: and gs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
897: and (c_lot_id is NULL or gs.lot_id = c_lot_id)
898: and (c_item_id is NULL or gs.item_id = c_item_id)
899: and gs.orgn_code = c_orgn_code
900: and gs.delete_mark = 0
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
970: select gsb.spec_id qc_spec_id,
979: and gsb.spec_id = gst.spec_id
980: and gst.test_id = gt.test_id
981: and gsb.item_id = c_item_id
982: and gt.test_code = c_assay_code
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,
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,
992: decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
1000: and gsb.spec_id = gst.spec_id
1001: and gst.test_id = gt.test_id
1002: and gsb.item_id = c_item_id
1003: and gt.test_code = c_assay_code
1004: and gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
1005: and gcs.orgn_code is NULL
1006: and gsb.delete_mark= 0;
1007:
1008:
1410: WHERE item_id = c_item_id
1411: and delete_mark = 0
1412: ;
1413:
1414: CURSOR get_cust_info (c_cust_id op_cust_mst.cust_id%TYPE) IS
1415: SELECT
1416: custsort_no cust_no,
1417: cust_name cust_name
1418: from op_cust_mst
1414: CURSOR get_cust_info (c_cust_id op_cust_mst.cust_id%TYPE) IS
1415: SELECT
1416: custsort_no cust_no,
1417: cust_name cust_name
1418: from op_cust_mst
1419: WHERE cust_id = c_cust_id
1420: AND delete_mark= 0;
1421:
1422:
1777: END LOOP;
1778: /* end getting bol (shipping) info from op_bill_lad */
1779: END IF;
1780: /* end checking if bol id has a value */
1781: /* get customer no and name from op_cust_mst tables */
1782:
1783: IF tbl_hdr(loop_counter).cust_id is not NULL THEN
1784: FOR cust_cur_rec IN get_cust_info (tbl_hdr(loop_counter).cust_id) LOOP
1785: tbl_hdr(loop_counter).cust_no := cust_cur_rec.cust_no;