[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
CURSOR get_spec_details (c_spec_id NUMBER)
IS
select gsb.item_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,
gt.test_unit uom,
gst.text_code spec_text_code
from
gmd_specifications_b gsb,
gmd_spec_tests_b gst,
gmd_qc_tests_b gt
where
gsb.spec_id = c_spec_id
and gsb.spec_id = gst.spec_id
and gst.test_id = gt.test_id
and nvl(gst.print_spec_ind,'N') = 'Y'
and gsb.delete_mark = 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.
# 26Sep2001 Manish Gupta New Quality
# Changed the cursors for getting data from New Quality tables.
################################################################*/
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_lot_no ic_lots_mst.lot_no%TYPE,
c_cust_id hz_cust_accounts.cust_account_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,
gt.test_unit 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 in ('4A','5AV') -- ACCEPT
and nvl(gsr.evaluation_ind,'N') in ('0A','1V')
and decode(nvl(gst.print_result_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N',
gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N') = 'Y'
and gs.cust_id = c_cust_id
and ( c_lot_id is NULL or decode(nvl(gs.lot_id,0),0,gs.lot_no, gs.lot_id) = decode(nvl(gs.lot_id,0),0, c_lot_no,c_lot_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,
gt.test_unit 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 in ('4A','5AV') -- ACCEPT
--and gss.disposition = '4A' -- ACCEPT
and nvl(gsr.evaluation_ind,'N') in ( '0A','1V')
and decode(nvl(gst.print_result_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N',
gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N') = 'Y'
-- and (c_lot_id is NULL or gs.lot_id = c_lot_id)
and ( c_lot_id is NULL or decode(nvl(gs.lot_id,0),0,gs.lot_no, gs.lot_id) = decode(nvl(gs.lot_id,0),0, c_lot_no,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 nvl(gst.print_result_ind, 'N') = 'Y'
and gsb.item_id = c_item_id
and gt.test_code = c_assay_code
and gcs.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 nvl(gst.print_result_ind, 'N') = 'Y'
and gsb.item_id = c_item_id
and gt.test_code = c_assay_code
and gcs.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
Trace('Populate_Details, in v_report_title = ''COA''');
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 l.header_id order_id,
l.line_id line_id,
wdd.delivery_detail_id,
null orgn_code,
l.org_id,
h.order_number order_no,
h.cust_po_number custpo_no,
l.schedule_ship_date sched_shipdate,
l.actual_shipment_date actual_shipdate,
l.ship_to_org_id ,
wnd.delivery_id bol_id,
wnd.name bol_no,
l.inventory_item_id discrete_item_id,
ic.item_id item_id,
ic.item_no,
ic.item_desc1 item_desc1,
decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
ship_from_org.organization_code from_whse,
decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity, l.ordered_quantity ) order_qty1,
decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity2,l.ordered_quantity2) order_qty2,
l.order_quantity_uom order_um1,
l.ordered_quantity_UOM2 order_um2,
wdd.shipped_quantity ship_qty1,
wdd.shipped_quantity2 ship_qty2,
C.cust_account_id shipcust_id,
C.account_number cust_no,
pr.party_name cust_name ,
0 alloc_qty
FROM
oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
mtl_parameters ship_from_org,
mtl_system_items msi,
ic_item_mst ic,
hz_cust_accounts c,
hz_cust_site_uses_all s,
hz_cust_acct_sites_all a,
hz_parties pr
where h.header_id = l.header_id
and l.header_id = wdd.source_header_id
and l.line_id = wdd.source_line_id
and wnd.delivery_id= wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and (c_order_id IS NULL OR h.header_id = c_order_id)
and (c_bol_id IS NULL OR wnd.delivery_id= c_bol_id)
and (c_shipment_no IS NULL OR wnd.name= c_shipment_no)
and (c_org_id IS NULL OR a.org_id = c_org_id)
and (c_cust_id IS NULL OR l.sold_to_org_id = c_cust_id)
--and wnd.name = 'passedname'
and wdd.source_code ='OE'
and l.ship_from_org_id = ship_from_org.organization_id(+)
and ship_from_org.process_enabled_flag(+)='Y'
and msi.organization_id=decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) -- oe_sys_parameters.value('MASTER_ORGANIZATION_ID')
and msi.inventory_item_id = l.inventory_item_id
and msi.segment1 = ic.item_no
and l.ship_to_org_id = s.site_use_id(+)
and s.site_use_code(+) ='SHIP_TO'
and s.org_id = a.org_id(+)
and s.cust_acct_site_id = a.cust_acct_site_id(+)
and a.cust_account_id = c.cust_account_id(+)
and c.party_id = pr.party_id(+)
order by l.header_id
;
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
A.CUST_ACCT_SITE_ID cust_id,
C.ACCOUNT_NUMBER cust_no,
PR.PARTY_NAME cust_name
from
HZ_CUST_ACCOUNTS C,
HZ_CUST_SITE_USES_ALL S,
HZ_CUST_ACCT_SITES_ALL A,
HZ_PARTIES PR
where C.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND S.CUST_ACCT_SITE_ID = A.CUST_ACCT_SITE_ID
AND S.SITE_USE_CODE IN ('SHIP_TO')
AND A.ORG_ID = S.ORG_ID
AND C.PARTY_ID = PR.PARTY_ID
AND S.SITE_USE_ID = c_ship_to_org_id
;*/
SELECT whse_name
FROM
ic_whse_mst
WHERE
whse_code = c_whse_code
and delete_mark = 0
;*/
SELECT s.orgn_code,
w.whse_code,
w.whse_name
FROM mtl_parameters p,
ic_whse_mst w,
sy_orgn_mst s
WHERE
w.mtl_organization_id = c_ship_from_org_id
AND p.ORGANIZATION_ID = c_ship_from_org_id
AND s.orgn_code = w.orgn_code
AND s.orgn_code = p.process_orgn_code
AND p.process_enabled_flag ='Y'
AND s.delete_mark = 0
AND w.delete_mark = 0
;
SELECT itp.lot_id, itp.whse_code, itp.location
FROM
ic_tran_pnd itp
WHERE
itp.doc_type = 'OMSO'
AND itp.completed_ind <> -1
AND itp.line_detail_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
;
/* select NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'Y')
INTO l_debug_enabled FROM sys.DUAL; */
select value into l_utl_file_dir from v$parameter where name like 'utl_file_dir';
/* 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);
select c.report_title, meaning
from gem_lookups l, gmd_coa_headers c
where l.lookup_type = 'GMD_COA_REPORT_TITLE'
and l.lookup_code = c.report_title;
select meaning
from gem_lookups l
where l.lookup_type = 'GMD_COA_REPORT_TITLE'
and l.lookup_code = 'BLK';