[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
# Modified the where clause of the select statement in the
# get_qc_cust_spec cursor definition to retrieve records when
# c_item_id is null.
# Modified the where clause of the select statement in the
# get_qc_global_cust_spec cursor definition to retrieve records when
# c_item_id is null.
# Modified the where clause of the select statement in the
# get_qc_item_spec cursor definition to retrieve records when
# c_item_id is null.
# In this procedure in the for cursors tbl.hdr.FIRST and tbl_hdr.LAST
# are replaced with NVL(tbl.hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
############################################################################ */
PROCEDURE Look_For_CoC_Specs(
rec_param IN t_coa_parameters,
hdr_tbl_ndx OUT NOCOPY BINARY_INTEGER,
tbl_hdr IN OUT NOCOPY t_coa_header_tbl,
tbl_dtl IN OUT NOCOPY t_coa_detail_tbl)
IS
/* get cust spec else get global cust spec */
/* else get item spec else get global item spec */
/* BEGIN BUG#1810652 James Bernard */
/* Added 'c_item_id is NULL or' in the where clause */
CURSOR get_qc_cust_spec (c_cust_id NUMBER, c_item_id NUMBER,
c_orgn_code VARCHAR2)
IS
select gcs.orgn_code,
gcs.cust_id,
gsb.item_id,
null whse_code,
null lot_id,
gsb.spec_id qc_spec_id,
gt.test_code assay_code,
decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
specification,
gst.test_uom uom,
gcs.text_code spec_text_code
from
gmd_specifications_b gsb, -- qc_spec_mst qsm
gmd_customer_spec_vrs gcs,
gmd_spec_tests_b gst,
gmd_qc_tests_b gt
where
gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id= c_cust_id)
and (c_item_id is NULL or gsb.item_id = c_item_id)
and gcs.orgn_code = c_orgn_code
and gsb.spec_id = gcs.spec_id
and gsb.spec_id = gst.spec_id
and gst.test_id = gt.test_id
and gsb.delete_mark = 0
;
select gcs.orgn_code,
gcs.cust_id,
gsb.item_id,
null whse_code,
null lot_id,
gsb.spec_id qc_spec_id,
gt.test_code assay_code,
decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
specification,
gst.test_uom uom,
gcs.text_code spec_text_code
from
gmd_specifications_b gsb, -- qc_spec_mst qsm
gmd_customer_spec_vrs gcs,
gmd_spec_tests_b gst,
gmd_qc_tests_b gt
where
gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
and (c_item_id is NULL or gsb.item_id = c_item_id)
and gcs.orgn_code is null
and gsb.spec_id = gcs.spec_id
and gsb.spec_id = gst.spec_id
and gst.test_id = gt.test_id
and gsb.delete_mark = 0
;
select gcs.orgn_code,
null cust_id,
gsb.item_id,
gcs.whse_code whse_code,
gcs.lot_id lot_id,
gsb.spec_id qc_spec_id,
gt.test_code assay_code,
decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
specification,
gst.test_uom uom,
gcs.text_code spec_text_code
from
gmd_specifications_b gsb, -- qc_spec_mst qsm
gmd_inventory_spec_vrs gcs,
gmd_spec_tests_b gst,
gmd_qc_tests_b gt
where
((l_chk_whse_null =1 and gcs.whse_code is NULL)
OR (l_chk_whse_null=0 and
(c_whse_code is NULL or gcs.whse_code = c_whse_code)
) )
and ((l_chk_lot_null=1 and gcs.lot_id is NULL)
OR (l_chk_lot_null=0 and
(c_lot_id is NULL or gcs.lot_id = c_lot_id)
) )
and (c_item_id is NULL or gsb.item_id = c_item_id)
and ((l_chk_orgn_null=1 and gcs.orgn_code is NULL)
OR (l_chk_orgn_null=0 and
(c_orgn_code is NULL or gcs.orgn_code = c_orgn_code)
) )
and gsb.spec_id = gcs.spec_id
and gsb.spec_id = gst.spec_id
and gst.test_id = gt.test_id
and gsb.delete_mark = 0
;
/* just look for specs and insert data into details table */
/*BEGIN BUG#1810652 James Bernard */
/*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
/*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
/*END BUG#1810652 */
dtl_tbl_ndx := 0;
# of the select statement in the get_cust_rslt_info cursor
# definition.
# Added 'c_item_id is NULL or' and 'c_lot_id is NULL or'
# conditions in the where clause of the select statement
# in the get_item_rslt_info cursor definition.
################################################################*/
PROCEDURE Populate_Details (tbl_hdr IN t_coa_header_tbl,
tbl_dtl IN OUT NOCOPY t_coa_detail_tbl) IS
/* BEGIN BUG#1810652 James Bernard */
/* Added 'c_item_id is NULL or' in the where clause */
CURSOR get_cust_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
c_item_id ic_item_mst.item_id%TYPE,
c_lot_id ic_lots_mst.lot_id%TYPE,
c_cust_id op_cust_mst.cust_id%TYPE) IS
select gr.result_id qc_result_id,
gst.spec_id qc_spec_id,
gt.test_code assay_code,
gr.result_date result_date,
decode(gr.result_value_char, null, to_char(gr.result_value_num), gr.result_value_char) result,
decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
specification,
gst.test_uom uom,
gst.text_code spec_text_code,
gr.text_code rslt_text_code
from gmd_samples gs,
gmd_results gr,
gmd_qc_tests_b gt,
gmd_spec_results gsr,
gmd_spec_tests_b gst ,
gmd_sampling_events gse,
gmd_event_spec_disp ges,
gmd_specifications_b gsb,
gmd_sample_spec_disp gss
where gs.sample_id = gr.sample_id
and gse.sampling_event_id = gs.sampling_event_id
and gse.sampling_event_id = ges.sampling_event_id
and ges.spec_used_for_lot_attrib_ind ='Y'
and ges.spec_id(+) = gst.spec_id
and gst.test_id(+) = gt.test_id
and gs.sample_id = gr.sample_id
and gr.result_date is not null
and gr.result_id = gsr.result_id
and gr.test_id = gt.test_id
and ges.event_spec_disp_id = gsr.event_spec_disp_id
and ges.event_spec_disp_id = gss.event_spec_disp_id
and gsb.spec_id = ges.spec_id
and gss.disposition = '4A' -- ACCEPT
and nvl(gsr.evaluation_ind,'N') in ('0A')
and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
and gs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
and (c_lot_id is NULL or gs.lot_id = c_lot_id)
and (c_item_id is NULL or gs.item_id = c_item_id)
and gs.orgn_code = c_orgn_code
and gs.delete_mark = 0
;
select gr.result_id qc_result_id,
gst.spec_id qc_spec_id,
gt.test_code assay_code,
gr.result_date result_date,
decode(gr.result_value_char, null, to_char(gr.result_value_num), gr.result_value_char) result,
decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
specification,
gst.test_uom uom,
gst.text_code spec_text_code,
gr.text_code rslt_text_code
from gmd_samples gs,
gmd_results gr,
gmd_qc_tests_b gt,
gmd_spec_results gsr,
gmd_spec_tests_b gst ,
gmd_sampling_events gse,
gmd_event_spec_disp ges,
gmd_specifications_b gsb,
gmd_sample_spec_disp gss
where gs.sample_id = gr.sample_id
and gse.sampling_event_id = gs.sampling_event_id
and gse.sampling_event_id = ges.sampling_event_id
and ges.spec_used_for_lot_attrib_ind ='Y'
and ges.spec_id(+) = gst.spec_id
and gst.test_id(+) = gt.test_id
and gs.sample_id = gr.sample_id
and gr.result_date is not null
and gr.result_id = gsr.result_id
and gr.test_id = gt.test_id
and ges.event_spec_disp_id = gsr.event_spec_disp_id
and ges.event_spec_disp_id = gss.event_spec_disp_id
and gsb.spec_id = ges.spec_id
and gss.disposition = '4A' -- ACCEPT
and nvl(gsr.evaluation_ind,'N') = '0A'
and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
and (c_lot_id is NULL or gs.lot_id = c_lot_id)
and (c_item_id is NULL or gs.item_id = c_item_id)
and gs.orgn_code = c_orgn_code
and gs.delete_mark = 0
and gs.cust_id is NULL
and gs.batch_id is NULL
and gs.formula_id is NULL
and gs.routing_id is NULL
and gs.oprn_id is NULL
and gs.supplier_id is NULL
;
select gsb.spec_id qc_spec_id,
decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
specification,
gcs.text_code spec_text_code
from gmd_specifications_b gsb,
gmd_customer_spec_vrs gcs,
gmd_spec_tests_b gst,
gmd_qc_tests_b gt
where gsb.spec_id = gcs.spec_id
and gsb.spec_id = gst.spec_id
and gst.test_id = gt.test_id
and gsb.item_id = c_item_id
and gt.test_code = c_assay_code
and gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
and gcs.orgn_code = c_orgn_code
and gsb.delete_mark= 0;
select gsb.spec_id qc_spec_id,
decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
specification,
gcs.text_code spec_text_code
from gmd_specifications_b gsb,
gmd_customer_spec_vrs gcs,
gmd_spec_tests_b gst,
gmd_qc_tests_b gt
where gsb.spec_id = gcs.spec_id
and gsb.spec_id = gst.spec_id
and gst.test_id = gt.test_id
and gsb.item_id = c_item_id
and gt.test_code = c_assay_code
and gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
and gcs.orgn_code is NULL
and gsb.delete_mark= 0;
select gt.test_desc assay_desc
from gmd_qc_tests gt
where gt.test_code = c_assay_code
and gt.delete_mark = 0;
* select from result table looking for customer
* if no results, select from result table for item/loc result
* if no item/loc result, then end procedure, else
* look for a customer spec
* if spec_id <> cust spec then look for global cust spec
* get assay description
* *********************************************************************/
BEGIN /* begin Populate_Details */
IF v_report_title = 'COA' THEN
/*BEGIN BUG#1810652 James Bernard */
/*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
/*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0). */
FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
/*END BUG#1810652 */
found_a_row := FALSE;
select paragraph_code,
line_no,
text
from qc_text_tbl
where text_code = c_text_code
and line_no > 0
order by paragraph_code, line_no ;
select ooh.order_id,
ood.line_id,
ooh.orgn_code,
ooh.order_no,
ooh.custpo_no,
ood.sched_shipdate,
ood.actual_shipdate,
ood.shipcust_id,
ood.bol_id,
ood.item_id,
ood.from_whse,
ood.generic_id,
ood.order_qty1,
ood.order_qty2,
ood.order_um1,
ood.order_um2,
ood.ship_qty1,
ood.ship_qty2,
ood.alloc_qty
from
op_ordr_hdr ooh,
op_ordr_dtl ood
where
ooh.order_id = ood.order_id
and (c_from_shipdate is NULL or
( (ood.sched_shipdate between c_from_shipdate and c_to_shipdate)
OR
(ood.actual_shipdate between c_from_shipdate and c_to_shipdate)
))
and (c_cust_id IS NULL OR ood.shipcust_id = c_cust_id)
and (c_order_id IS NULL OR ood.order_id = c_order_id)
and (c_bol_id IS NULL OR ood.bol_id = c_bol_id)
and (c_item_id IS NULL OR ood.item_id = c_item_id)
and ood.ship_status <> -1
and ood.delete_mark = 0
;
SELECT generic_item,
generic_desc
FROM
op_gnrc_itm
WHERE generic_id = c_generic_id
and delete_mark = 0
;
SELECT bol_no
FROM
op_bill_lad
WHERE bol_id = c_bol_id
and delete_mark = 0
;
SELECT item_no,
item_desc1
FROM
ic_item_mst
WHERE item_id = c_item_id
and delete_mark = 0
;
SELECT
custsort_no cust_no,
cust_name cust_name
from op_cust_mst
WHERE cust_id = c_cust_id
AND delete_mark= 0;
SELECT whse_name
FROM
ic_whse_mst
WHERE
whse_code = c_whse_code
and delete_mark = 0
;
SELECT itp.lot_id, itp.whse_code, itp.location
FROM
ic_tran_pnd itp
WHERE
itp.doc_type = 'OPSO'
AND itp.completed_ind <> -1
AND itp.line_id = c_line_id
and itp.delete_mark = 0
;
SELECT ilm.lot_no,
ilm.lot_desc,
ilm.sublot_no
FROM
ic_lots_mst ilm
WHERE
ilm.lot_id = c_lot_id
and ilm.delete_mark = 0
;
* Modified the where clause of the select statement in the
* get_qc_cust_rslt cursor definition to retrieve records when
* c_lot_id and c_item_id are null.
* Modified the where clause of the select statement in the
* get_qc_item_rslt cursor definition to retrieve records when
* c_lot_id and c_item_id are null.
* **************************************************************/
PROCEDURE Look_For_Results (
tbl_ndx OUT NOCOPY BINARY_INTEGER)
IS
/* BEGIN BUG#1810652 James Bernard */
/* Added 'c_lot_id is NULL or' and 'c_item_id is NULL or' */
/* conditions in the where clause */
CURSOR get_qc_cust_rslt (c_cust_id NUMBER, c_item_id NUMBER,
c_lot_id NUMBER, c_orgn_code VARCHAR2)
IS
select distinct gs.orgn_code,
gs.cust_id,
gs.item_id,
gs.whse_code,
gs.lot_id
from
gmd_samples gs,
gmd_results gr,
gmd_spec_results gsr,
gmd_sampling_events gse,
gmd_event_spec_disp ges,
gmd_spec_tests_b gst,
gmd_sample_spec_disp gss
where gse.sampling_event_id = gs.sampling_event_id
and gse.sampling_event_id = ges.sampling_event_id
and ges.spec_used_for_lot_attrib_ind ='Y'
and gs.sample_id = gr.sample_id
and gr.result_id = gsr.result_id
and ges.event_spec_disp_id = gsr.event_spec_disp_id
and ges.event_spec_disp_id = gss.event_spec_disp_id(+)
and ges.spec_id(+) = gst.spec_id
and gst.test_id(+) = gr.test_id
and gss.disposition = '4A' -- ACCEPT
and nvl(gsr.evaluation_ind,'N') = '0A'
and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
and (c_lot_id is NULL or gs.lot_id = c_lot_id)
and (c_item_id is NULL or gs.item_id = c_item_id)
and gs.orgn_code = c_orgn_code
and gs.delete_mark = 0;
select distinct gs.orgn_code,
gs.cust_id,
gs.item_id,
gs.whse_code,
gs.lot_id
from
gmd_samples gs,
gmd_results gr,
gmd_spec_results gsr,
gmd_sampling_events gse,
gmd_event_spec_disp ges,
gmd_spec_tests_b gst,
gmd_sample_spec_disp gss
where
gs.cust_id is NULL
and gs.batch_id is NULL
and gs.formula_id is NULL
and gs.routing_id is NULL
and gs.oprn_id is NULL
and gs.supplier_id is NULL
and gse.sampling_event_id = gs.sampling_event_id
and gse.sampling_event_id = ges.sampling_event_id
and ges.spec_used_for_lot_attrib_ind ='Y'
and gs.sample_id = gr.sample_id
and gr.result_id = gsr.result_id
and ges.event_spec_disp_id = gsr.event_spec_disp_id
and ges.event_spec_disp_id = gss.event_spec_disp_id(+)
and ges.spec_id(+) = gst.spec_id
and gst.test_id(+) = gr.test_id
and gss.disposition = '4A' -- ACCEPT
and nvl(gsr.evaluation_ind,'N') = '0A'
and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
and (c_lot_id is NULL or gs.lot_id = c_lot_id)
and (c_item_id is NULL or gs.item_id = c_item_id)
and (c_whse_code is NULL OR gs.whse_code = c_whse_code)
and gs.orgn_code = c_orgn_code
and gs.delete_mark = 0
;
/* selected as part of generic item check. */
/* It is correct to check item_NO, not item_ID! */
IF tbl_hdr(loop_counter).item_no is NULL THEN
FOR item_cur_rec IN get_item_info (tbl_hdr(loop_counter).item_id) LOOP
tbl_hdr(loop_counter).item_no := item_cur_rec.item_no;
delete from gmd_coa_headers;
delete from gmd_coa_details;
delete from gmd_coa_spec_text;
delete from gmd_coa_rslt_text;
INSERT into gmd_coa_headers (gmd_coa_id, order_id, line_id, orgn_code,
order_no,
custpo_no,
shipdate, cust_id, cust_no, cust_name,
bol_id, bol_no, item_id,
item_no, item_desc1,
whse_code, whse_name,
lot_id, lot_no, lot_desc, sublot_no,
order_qty1, order_um1, order_qty2,
order_um2, ship_qty1, ship_qty2,
report_title,
created_by, creation_date, last_update_date,
last_updated_by, last_update_login)
VALUES (tbl_hdr(loop_counter).gmd_coa_id,
tbl_hdr(loop_counter).order_id,
tbl_hdr(loop_counter).line_id,
tbl_hdr(loop_counter).orgn_code,
tbl_hdr(loop_counter).order_no,
tbl_hdr(loop_counter).custpo_no,
tbl_hdr(loop_counter).shipdate,
tbl_hdr(loop_counter).cust_id,
tbl_hdr(loop_counter).cust_no,
tbl_hdr(loop_counter).cust_name,
tbl_hdr(loop_counter).bol_id,
tbl_hdr(loop_counter).bol_no,
tbl_hdr(loop_counter).item_id,
tbl_hdr(loop_counter).item_no,
tbl_hdr(loop_counter).item_desc,
tbl_hdr(loop_counter).whse_code,
tbl_hdr(loop_counter).whse_name,
tbl_hdr(loop_counter).lot_id,
tbl_hdr(loop_counter).lot_no,
tbl_hdr(loop_counter).lot_desc,
tbl_hdr(loop_counter).sublot_no,
tbl_hdr(loop_counter).order_qty1,
tbl_hdr(loop_counter).order_um1,
tbl_hdr(loop_counter).order_qty2,
tbl_hdr(loop_counter).order_um2,
tbl_hdr(loop_counter).ship_qty1,
tbl_hdr(loop_counter).ship_qty2,
tbl_hdr(loop_counter).report_title,
X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
);
INSERT into gmd_coa_details (gmd_coa_id, qc_result_id, result_date,
qc_spec_id, assay_code, assay_desc,
result, specification, uom,
rslt_text_code, spec_text_code,
created_by, creation_date,
last_update_date,
last_updated_by, last_update_login
)
VALUES (tbl_dtl(loop_counter).gmd_coa_id,
tbl_dtl(loop_counter).qc_result_id,
tbl_dtl(loop_counter).result_date,
tbl_dtl(loop_counter).qc_spec_id,
tbl_dtl(loop_counter).assay_code,
tbl_dtl(loop_counter).assay_desc,
tbl_dtl(loop_counter).result,
tbl_dtl(loop_counter).specification,
tbl_dtl(loop_counter).uom,
tbl_dtl(loop_counter).rslt_text_code,
tbl_dtl(loop_counter).spec_text_code,
X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
);
INSERT into gmd_coa_spec_text (gmd_coa_id, text_code,
paragraph_code, line_no, text)
VALUES (tbl_spec_text(loop_counter).gmd_coa_id,
tbl_spec_text(loop_counter).text_code,
tbl_spec_text(loop_counter).paragraph_code,
tbl_spec_text(loop_counter).line_no,
tbl_spec_text(loop_counter).text);
INSERT into gmd_coa_rslt_text (gmd_coa_id, text_code,
paragraph_code, line_no, text)
VALUES (tbl_rslt_text(loop_counter).gmd_coa_id,
tbl_rslt_text(loop_counter).text_code,
tbl_rslt_text(loop_counter).paragraph_code,
tbl_rslt_text(loop_counter).line_no,
tbl_rslt_text(loop_counter).text);