[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_hdr_rec (p_hdr_rec t_coa_hdr_rec,
x_return_status OUT NOCOPY VARCHAR2) is
X_user_id NUMBER:= FND_GLOBAL.USER_ID;
PrintLn('Begin procedure insert_hdr_rec');
PrintLn('Inserting into gmd_coa_headers table');
INSERT INTO gmd_coa_headers (gmd_coa_id,
order_id,
line_id,
organization_id, --INVCONV
organization_code, --INVCONV
order_no,
custpo_no,
shipdate,
cust_id,
cust_no,
cust_name,
bol_id,
bol_no,
inventory_item_id, --INVCONV
item_number, --INVCONV
item_description,
revision, --Bug# 4662469
subinventory, --INVCONV
lot_number, --INVCONV
lot_description, --INVCONV
order_qty1,
order_uom1, --INVCONV
order_qty2,
order_uom2, --INVCONV
ship_qty1,
ship_qty2,
ship_qty_uom1, -- Bug # 3710191 Added ship_uom1 and ship_uom2
ship_qty_uom2, --INVCONV
report_title,
spec_hdr_text_code, -- Bug # 4260445
created_by, creation_date, last_update_date,
last_updated_by, last_update_login)
VALUES (p_hdr_rec.gmd_coa_id,
p_hdr_rec.order_id,
p_hdr_rec.line_id,
p_hdr_rec.organization_id,
p_hdr_rec.organization_code,
p_hdr_rec.order_no,
p_hdr_rec.custpo_no,
p_hdr_rec.shipdate,
p_hdr_rec.cust_id,
p_hdr_rec.cust_no,
p_hdr_rec.cust_name,
p_hdr_rec.bol_id,
p_hdr_rec.bol_no,
p_hdr_rec.inventory_item_id,
p_hdr_rec.item_number,
p_hdr_rec.item_description,
p_hdr_rec.revision, --bug# 4662469
p_hdr_rec.subinventory,
p_hdr_rec.lot_number,
p_hdr_rec.lot_description,
p_hdr_rec.order_qty1,
p_hdr_rec.order_uom1,
p_hdr_rec.order_qty2,
p_hdr_rec.order_uom2,
p_hdr_rec.ship_qty1,
p_hdr_rec.ship_qty2,
p_hdr_rec.ship_qty_uom1, -- Bug # 3710191 Added these two lines.
p_hdr_rec.ship_qty_uom2,
p_hdr_rec.report_title,
p_hdr_rec.spec_hdr_text_code, -- Bug # 4260445
X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
);
log_msg('procedure insert_hdr_rec, inserted into hdr table...');
PrintLn('End procedure insert_hdr_rec');
PrintLn('When Others in GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
log_msg('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
END insert_hdr_rec;
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 ;
PrintLn('Inserting into gmd_coa_spec_hdr_text');
INSERT into gmd_coa_spec_hdr_text (gmd_coa_id, text_code,
paragraph_code, line_no, text)
VALUES (tbl_hdr.gmd_coa_id,
tbl_hdr.spec_hdr_text_code,
text_cur_rec.paragraph_code,
text_cur_rec.line_no,
text_cur_rec.text);
log_msg('inserted into populate_hdr_text...');
PrintLn('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
log_msg('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
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 ;
PrintLn('Inserting into gmd_coa_spec_text');
INSERT into gmd_coa_spec_text (gmd_coa_id, text_code,
paragraph_code, line_no, text)
VALUES (tbl_dtl.gmd_coa_id,
tbl_dtl.spec_text_code,
text_cur_rec.paragraph_code,
text_cur_rec.line_no,
text_cur_rec.text);
PrintLn('Inserting into gmd_coa_rslt_text');
INSERT into gmd_coa_rslt_text (gmd_coa_id, text_code,
paragraph_code, line_no, text)
VALUES (tbl_dtl.gmd_coa_id,
tbl_dtl.rslt_text_code,
text_cur_rec.paragraph_code,
text_cur_rec.line_no,
text_cur_rec.text);
log_msg('inserted into populate_text...');
PrintLn('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
log_msg('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
PROCEDURE insert_dtl_rec(p_dtl_rec t_coa_dtl_rec,
x_return_status OUT NOCOPY VARCHAR2) IS
X_user_id NUMBER:= FND_GLOBAL.USER_ID;
PrintLn('Begin procedure insert_dtl_rec');
PrintLn('Inserting into gmd_coa_details');
INSERT into gmd_coa_details (gmd_coa_id,
qc_result_id,
result_date,
qc_spec_id,
assay_code,
assay_desc,
result,
specification,
uom, --Bug 6485606. Changed inv_uom to uom
rslt_text_code,
spec_text_code,
min_spec,max_spec,
test_method,
created_by, creation_date,
last_update_date,
last_updated_by, last_update_login
)
VALUES (p_dtl_rec.gmd_coa_id,
p_dtl_rec.result_id,
p_dtl_rec.result_date,
p_dtl_rec.spec_id,
p_dtl_rec.test_code,
p_dtl_rec.test_display,
p_dtl_rec.result,
p_dtl_rec.specification,
p_dtl_rec.test_unit,
p_dtl_rec.rslt_text_code,
p_dtl_rec.spec_text_code,
p_dtl_rec.min_spec,
p_dtl_rec.max_spec,
p_dtl_rec.test_method,
X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
);
PrintLn('End procedure insert_dtl_rec');
PrintLn('GMD_COA_DATA.INSERT_DTL_REC '|| SUBSTR(SQLERRM,1,100));
log_msg('GMD_COA_DATA.INSERT_DTL_REC '|| SUBSTR(SQLERRM,1,100));
END insert_dtl_rec;
select r.result_id,
r.test_id,
ges.spec_id,
nvl(r.result_value_char,result_value_num) result,
r.result_value_char,
r.result_value_num,
r.result_date,
r.text_code
from gmd_results r,
gmd_spec_results sr,
gmd_event_spec_disp ges
where r.sample_id = p_sample_id
and r.result_id = sr.result_id
and nvl(sr.evaluation_ind,'N') in ('0A','1V','2R','N') -- srakrish bug 5747932: To fetch results for non validated tests.
and sr.event_spec_disp_id = ges.event_spec_disp_id
and ges.spec_used_for_lot_attrib_ind ='Y'
and r.test_id = p_test_id
and r.delete_mark = 0
order by r.result_date, r.seq desc;
SELECT display_label_numeric_range
FROM gmd_qc_test_values
WHERE p_result between min_num and max_num
AND test_id = p_test_id;
select r.composite_result_id,
r.test_id,
--nvl(r.mean,r.mode_char) result,
r.mean,
r.mode_num,
r.mode_char,
r.low_num,
r.high_num,
r.range,
r.non_validated_result,
r.standard_deviation , ges.spec_id
from gmd_composite_results r,
gmd_composite_spec_disp sd,
gmd_event_spec_disp ges
where r.composite_spec_disp_id =sd.composite_spec_disp_id
and sd.event_spec_disp_id = p_event_spec_disp_id
and sd.event_spec_disp_id = ges.event_spec_disp_id
and nvl(ges.spec_used_for_lot_attrib_ind,'N') = 'Y'
and nvl(sd.latest_ind,'N') = 'Y'
and r.delete_mark = 0
and r.test_id = p_test_id;
SELECT cust_test_display,
report_precision
FROM gmd_customer_tests
WHERE cust_id = p_cust_id
AND test_id = p_test_id;
SELECT nvl(s.test_display,t.test_desc) display,
s.report_precision,t.test_type,
s.target_value_num, s.min_value_num, s.max_value_num,
s.target_value_char, s.min_value_char, s.max_value_char,
s.text_code spec_text_code,t.test_code,t.test_id,t.test_unit,
decode(m.test_method_code,'DEFAULT',NULL, m.test_method_code) test_method_code
FROM gmd_spec_tests s,
gmd_qc_tests t,
gmd_test_methods_b m
--Bug 3785184 backing out fix 3588346
-- gmd_results r, --BUG#3588346
-- gmd_spec_results sr --BUG#3588346
WHERE s.test_id= t.test_id
AND s.spec_id= p_spec_id
AND ((p_report_type = 'COC' and nvl(s.print_spec_ind,'N') ='Y') OR
(p_report_type = 'COA' and nvl(s.print_result_ind,'N') ='Y'))
-- Bug# 5223677. Pick all tests. Commented following AND condition.
-- AND nvl(s.optional_ind,'N') = 'N'
AND s.test_method_id = m.test_method_id
--Bug 3785184 backing out fix 3588346
--BEGIN BUG#3588346
-- AND r.sample_id = p_sample_id
-- AND s.test_id = r.test_id
-- AND r.result_id = sr.result_id
-- AND sr.evaluation_ind IS NOT NULL
--END BUG#3588346
ORDER BY s.seq;
SELECT display_label_numeric_range
FROM gmd_qc_test_values
WHERE p_result between min_num and max_num
AND test_id = p_test_id;
PrintLn('Calling procedure insert_dtl_rec');
insert_dtl_rec(l_detail_rec,x_return_status);
SELECT display_label_numeric_range
FROM gmd_qc_test_values
WHERE nvl(p_value,nvl(min_num,max_num)) between nvl(min_num,nvl(p_value,max_num)) and nvl(max_num,nvl(p_value,min_num))
AND test_id = p_test_id;
select item_no, item_desc1
from ic_item_mst
where item_id = p_item_id;*/
SELECT concatenated_segments item_number,
description item_description
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select a.account_number cust_no,
b.party_name cust_name
from hz_cust_accounts a,
hz_parties b
where a.party_id = b.party_id
and a.cust_account_id = p_cust_id;
select a.lot_no,a.lot_desc,
a.sublot_no
from ic_lots_mst a
where (p_lot_id is NULL OR lot_id = p_lot_id)
and (p_lot_no IS NULL or lot_no = p_lot_no);*/
SELECT spec_name,spec_vers
FROM gmd_specifications_b
WHERE spec_id = p_spec_id;
select nvl(c.SAMPLE_ACTIVE_CNT,0) sample_active_cnt,
b.event_spec_disp_id,
b.sampling_event_id
from gmd_samples a,gmd_event_spec_disp b, gmd_sampling_events c
where b.spec_id =p_spec_id
and (((a.lot_number = p_lot_number or p_lot_number is null)
and (a.parent_lot_number = p_parent_lot_number or p_parent_lot_number is null) ) -- 9655426 added
or (a.parent_lot_number = p_parent_lot_number and a.lot_number is null) ) -- 9655426 added latest
and b.disposition in ('4A','5AV','6RJ')
and b.spec_used_for_lot_attrib_ind ='Y'
and a.sampling_event_id= b.sampling_event_id
and a.sampling_event_id= c.sampling_event_id
and nvl (c.sample_active_cnt,0) >= 1
order by 1 DESC , a.creation_date desc; -- 9655426 ADDED lot number to order by
select a.sample_id , a.sample_no
from gmd_samples a, gmd_sample_spec_disp b
where a.sampling_event_id = p_sampling_event_id
AND a.sample_id = b.sample_id
AND b.disposition <> '7CN';
SELECT gst.test_id
FROM GMD_SPEC_TESTS_B gst
WHERE gst.spec_id = p_spec_id;
SELECT gs.lot_number,gs.cust_id,gs.creation_date,/*gs.location,*/gs.subinventory,
gr.sample_id sample_id,ges.event_spec_disp_id,
gr.test_id,
gr.result_value_num,
gr.result_value_char,'SAMPLE'
FROM GMD_SAMPLING_EVENTS gs ,
GMD_EVENT_SPEC_DISP ges,
GMD_RESULTS gr,
GMD_SPEC_RESULTS sp
WHERE
gs.inventory_item_id = l_inventory_item_id
AND (gs.revision = l_revision OR gs.revision IS NULL )
AND (((gs.lot_number = l_lot_number OR gs.lot_number IS NULL)
AND (gs.parent_lot_number = l_parent_lot_number OR gs.parent_lot_number IS NULL) ) -- 9655426 added
OR (gs.parent_lot_number = l_parent_lot_number and gs.lot_number IS NULL) ) -- 9655426 added
AND (gs.subinventory = l_subinventory OR gs.subinventory IS NULL)
AND (gs.locator_id = l_locator_id OR gs.locator_id IS NULL )
AND gr.delete_mark = 0
AND (gs.cust_id = l_cust_id OR gs.cust_id IS NULL)
AND (gr.result_value_num IS NOT NULL or gr.result_value_char IS NOT NULL)
AND gs.sample_active_cnt = 1
and ges.disposition in ('4A','5AV','6RJ') --8577332 changed from 5RJ to 6RJ
and ges.spec_used_for_lot_attrib_ind ='Y'
and gs.sampling_event_id = ges.sampling_event_id
and ges.event_spec_disp_id = sp.event_spec_disp_id
and sp.result_id = gr.result_id
UNION
SELECT gs.lot_number,gs.cust_id,gs.creation_date ,/*gs.location,*/gs.subinventory,
null sample_id,ges.event_spec_disp_id,
gr.test_id,
gr.mean result_value_num,
gr.mode_char result_value_char,'EVENT_SPEC_DISP'
FROM GMD_SAMPLING_EVENTS gs ,
GMD_EVENT_SPEC_DISP sd,
GMD_COMPOSITE_RESULTS gr, -- possble change here
GMD_COMPOSITE_SPEC_DISP ges
WHERE
gs.inventory_item_id = l_inventory_item_id
AND (gs.revision = l_revision OR gs.revision IS NULL)
AND (((gs.lot_number = l_lot_number OR gs.lot_number IS NULL)
AND (gs.parent_lot_number = l_parent_lot_number OR gs.parent_lot_number IS NULL) ) -- 9655426 added
OR (gs.parent_lot_number = l_parent_lot_number and gs.lot_number IS NULL) ) -- 9655426 added
AND (gs.subinventory = l_subinventory OR gs.subinventory IS NULL)
AND (gs.locator_id = l_locator_id OR gs.locator_id IS NULL )
AND (gs.cust_id = l_cust_id OR gs.cust_id IS NULL)
AND gr.delete_mark = 0
AND (gr.mean IS NOT NULL or gr.mode_char IS NOT NULL)
AND gs.sample_active_cnt > 1
AND gs.sampling_event_id = sd.sampling_event_id
and sd.event_spec_disp_id = ges.event_spec_disp_id
and ges.composite_spec_disp_id = gr.composite_spec_disp_id
and ges.disposition in ('4A','5AV','6RJ') ----8577332 changed from 5RJ to 6RJ
and nvl(ges.latest_ind,'N') = 'Y'
ORDER BY 1 DESC ,2,3 desc,4,5,6 ; -- added desc to 1 for 9655426
result_test_list.DELETE;
result_test_list.DELETE;
select a.organization_id, b.organization_code, a.sample_no
from gmd_samples a, mtl_parameters b
where sample_id = p_sample_id
and a.organization_id = b.organization_id;
select a.organization_id, c.organization_code, a.sample_no
from gmd_samples a,
gmd_event_spec_disp b,
mtl_parameters c
where a.sampling_event_id = b.sampling_event_id
and b.event_spec_disp_id = p_event_spec_disp_id
and a.organization_id = c.organization_id;
select nvl(coa_type,'A') coa_type
from gmd_com_spec_vrs_vl
where spec_vr_id = p_spec_vr_id;
SELECT text_code
FROM gmd_specifications
WHERE spec_id = l_spec_id;
SELECT parent_lot_number
FROM mtl_lot_numbers
WHERE inventory_item_id = p_hdr_rec.inventory_item_id
and lot_number = p_hdr_rec.lot_number
and organization_id = p_hdr_rec.organization_id;
SELECT lot_control_code INTO l_lot_ctl
FROM mtl_system_items
WHERE organization_id = p_hdr_rec.organization_id
AND inventory_item_id = p_hdr_rec.inventory_item_id;
PrintLn('Calling Procedure insert_hdr_rec');
insert_hdr_rec(p_hdr_rec,x_return_status);
PrintLn('Calling Procedure insert_hdr_rec');
insert_hdr_rec(p_hdr_rec,x_return_status);
PrintLn('Calling Procedure insert_hdr_rec');
insert_hdr_rec(p_hdr_rec,x_return_status);
select l.header_id order_id,
l.line_id line_id,
wdd.delivery_detail_id,
null organization_code, --INVCONV
l.org_id,
h.order_number order_no,
h.cust_po_number custpo_no,
nvl(l.actual_shipment_date,l.schedule_ship_date) actual_shipdate,
l.ship_to_org_id ,
wnd.delivery_id bol_id,
wnd.name bol_no,
l.inventory_item_id inventory_item_id, --INVCONV
msi.concatenated_segments item_number, --INVCONV
msi.description item_description, --INVCONV
l.item_revision, --Bug# 4662469
decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
l.ship_to_org_id ship_to_site_id, --Bug 4166529 added.
ship_from_org.organization_code from_whse,
h.sold_to_org_id cust_id,
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_uom1, --INVCONV
l.ordered_quantity_UOM2 order_uom2, --INVCONV
wdd.shipped_quantity ship_qty1,
wdd.shipped_quantity2 ship_qty2,
l.shipping_quantity_uom ship_qty_uom1, --INVCONV -- Bug # 3710191 Added these two lines.
l.shipping_quantity_uom2 ship_qty_uom2, --INVCONV
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_b_kfv msi, --INVCONV
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 (p_order_id IS NULL OR h.header_id = p_order_id)
and (wnd.delivery_id = p_delivery_id)
and (p_org_id IS NULL OR a.org_id = p_org_id)
and (p_cust_id IS NULL OR l.sold_to_org_id = p_cust_id)
and (p_inventory_item_id IS NULL OR msi.inventory_item_id = p_inventory_item_id) --INVCONV
and wdd.source_code ='OE'
--AND wdd.split_from_delivery_detail_ID IS NULL -- ADDED BY for bug 8733799 - remove for bug 9650071
and l.ship_from_org_id = ship_from_org.organization_id(+)
and ship_from_org.process_enabled_flag(+) ='Y'
and ((l.ship_from_org_id IS NOT NULL AND msi.organization_id = l.ship_from_org_id ) OR (l.ship_from_org_id IS NULL AND msi.organization_id = h.ship_from_org_id ))
and msi.inventory_item_id = l.inventory_item_id
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 l.header_id order_id,
l.line_id line_id,
wdd.delivery_detail_id,
null organization_code, --INVCONV
l.org_id,
h.order_number order_no,
h.cust_po_number custpo_no,
nvl(l.actual_shipment_date,l.schedule_ship_date) actual_shipdate,
l.ship_to_org_id ,
wnd.delivery_id bol_id,
wnd.name bol_no,
l.inventory_item_id inventory_item_id, --INVCONV
msi.concatenated_segments item_number, --INVCONV
msi.description item_description, --INVCONV
l.item_revision, --Bug# 4662469
decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
l.ship_to_org_id ship_to_site_id, --Bug 4166529 added.
ship_from_org.organization_code from_whse,
h.sold_to_org_id cust_id,
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_uom1, --INVCONV
l.ordered_quantity_UOM2 order_uom2, --INVCONV
wdd.shipped_quantity ship_qty1,
wdd.shipped_quantity2 ship_qty2,
l.shipping_quantity_uom ship_qty_uom1, --INVCONV -- Bug # 3710191 Added these two lines.
l.shipping_quantity_uom2 ship_qty_uom2, --INVCONV
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_b_kfv msi, --INVCONV
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 (p_order_id IS NULL OR h.header_id = p_order_id)
and (p_delivery_id IS NULL OR wnd.delivery_id = p_delivery_id)
and (p_org_id IS NULL OR a.org_id = p_org_id)
and (p_cust_id IS NULL OR l.sold_to_org_id = p_cust_id)
and (p_inventory_item_id IS NULL OR msi.inventory_item_id = p_inventory_item_id) --INVCONV
and wdd.source_code ='OE'
--AND wdd.split_from_delivery_detail_ID IS NULL -- ADDED BY for bug 8733799 - remove for bug 9650071
and l.ship_from_org_id = ship_from_org.organization_id(+)
and ship_from_org.process_enabled_flag(+) ='Y'
and ((l.ship_from_org_id IS NOT NULL AND msi.organization_id = l.ship_from_org_id ) OR (l.ship_from_org_id IS NULL AND msi.organization_id = h.ship_from_org_id ))
and msi.inventory_item_id = l.inventory_item_id
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 l.header_id order_id,
l.line_id line_id,
l.org_id,
h.order_number order_no,
h.cust_po_number custpo_no,
nvl(l.actual_shipment_date,l.schedule_ship_date) actual_shipdate,
l.ship_to_org_id ,
l.inventory_item_id inventory_item_id, --INVCONV
msi.concatenated_segments item_number, --INVCONV
msi.description item_description, --INVCONV
l.item_revision, --Bug# 4662469
decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
l.ship_to_org_id ship_to_site_id, --Bug 4166529 added.
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_uom1, --INVCONV
l.ordered_quantity_UOM2 order_uom2, --INVCONV
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,
mtl_parameters ship_from_org,
mtl_system_items_b_kfv msi, --INVCONV
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 (p_order_id IS NULL OR h.header_id = p_order_id)
and (p_org_id IS NULL OR a.org_id = p_org_id)
and (p_cust_id IS NULL OR l.sold_to_org_id = p_cust_id)
and (p_inventory_item_id IS NULL OR msi.inventory_item_id = p_inventory_item_id)
and l.ship_from_org_id = ship_from_org.organization_id(+)
and ship_from_org.process_enabled_flag(+) = 'Y'
and ((l.ship_from_org_id IS NOT NULL AND msi.organization_id = l.ship_from_org_id ) OR (l.ship_from_org_id IS NULL AND msi.organization_id = h.ship_from_org_id ))
and msi.inventory_item_id = l.inventory_item_id
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 s.orgn_code,
w.whse_code
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 distinct MTLT.LOT_NUMBER, Abs(mtlt.primary_quantity) qty1 -- 12659784 added primary_quantity
FROM MTL_TRANSACTION_LOTS_TEMP MTLT,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
MTL_TXN_REQUEST_LINES_V MTRL
WHERE MTLT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
AND MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND MTRL.TXN_SOURCE_LINE_ID = p_order_line_id
AND MTRL.TRANSACTION_TYPE_ID = 52
AND MTRL.TRANSACTION_ACTION_ID = 28
AND MTRL.TRANSACTION_SOURCE_TYPE_ID = 2
UNION
SELECT distinct MTLN.LOT_NUMBER , Abs(mtln.primary_quantity) qty1 -- 12659784 added primary_quantity
FROM MTL_TRANSACTION_LOT_NUMBERS MTLN,
MTL_MATERIAL_TRANSACTIONS MMT
WHERE MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID
AND MMT.TRX_SOURCE_LINE_ID = p_order_line_id
AND MMT.TRANSACTION_TYPE_ID = 52
AND MMT.TRANSACTION_ACTION_ID = 28
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2;
SELECT LOT_NUMBER FROM GMD_COA_HEADERS WHERE LOT_NUMBER = P_LOT_NUMBER AND ORDER_QTY1 = P_ORDER_QTY1;
SELECT NVL( SUBSTR( value, 1, INSTR( value, ',')-1), value)
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
FROM v$parameter
WHERE name = 'utl_file_dir';