[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_id INTO l_user_id
FROM fnd_user
WHERE user_name = UPPER(p_user_name)
AND SYSDATE BETWEEN start_date AND NVL(end_date,sysdate+1);
, p_delete_mark IN NUMBER DEFAULT NULL
, p_from_last_update_date IN DATE DEFAULT NULL
, p_to_last_update_date IN DATE DEFAULT NULL
, x_test_methods_table OUT NOCOPY system.gmd_test_methods_tab_type -- 5284242
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
sql_statement VARCHAR2(2000);
gme_debug.put_line('Constructing select statement');
sql_statement := 'SELECT ';
||'gtm.test_method_desc, gtm.test_qty, gtm.test_qty_uom, gtm.delete_mark, '
||'gtm.display_precision, gtm.test_duration, gtm.days, gtm.hours, '
||'gtm.minutes, gtm.seconds, gtm.test_replicate, gtm.resources, '
||'gtm.test_kit_organization_id, NULL, ' --INVCONV
||'gtm.test_kit_inv_item_id, NULL, gtm.text_code, gtm.attribute1, '
||'gtm.attribute2, gtm.attribute3, gtm.attribute4, gtm.attribute5, '
||'gtm.attribute6, gtm.attribute7, gtm.attribute8, gtm.attribute9, '
||'gtm.attribute10, gtm.attribute11, gtm.attribute12, gtm.attribute13, '
||'gtm.attribute14, gtm.attribute15, gtm.attribute16, gtm.attribute17, '
||'gtm.attribute18, gtm.attribute19, gtm.attribute20, gtm.attribute21, '
||'gtm.attribute22, gtm.attribute23, gtm.attribute24, gtm.attribute25, '
||'gtm.attribute26, gtm.attribute27, gtm.attribute28, gtm.attribute29, '
||'gtm.attribute30, gtm.attribute_category, gtm.creation_date, '
||'gtm.created_by, fu1.user_name, gtm.last_updated_by, fu2.user_name, '
||'gtm.last_update_date, gtm.last_update_login) ';
' AND fu2.user_id (+) = gtm.last_updated_by '||
'AND 1=:dummy ';
IF p_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_delete_mark := p_delete_mark;
where_clause := where_clause||'AND gtm.delete_mark = :delete_mark ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_delete_mark';
IF p_from_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_last_update_date := p_from_last_update_date;
where_clause := where_clause||'AND gtm.last_update_date >= :from_last_update_date ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_from_last_update_date ';
IF p_to_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_last_update_date := p_to_last_update_date;
where_clause := where_clause||'AND gtm.last_update_date <= :to_last_update_date';
using_clause := using_clause||', gmd_outbound_apis_pub.g_to_last_update_date';
SELECT organization_code INTO g_test_methods_table(i).test_kit_organization_code
FROM mtl_parameters
WHERE organization_id = g_test_methods_table(i).test_kit_organization_id;
SELECT concatenated_segments INTO g_test_methods_table(i).test_kit_item_number
FROM mtl_system_items_b_kfv
WHERE organization_id = g_test_methods_table(i).test_kit_organization_id
AND inventory_item_id = g_test_methods_table(i).test_kit_inv_item_id;
, p_delete_mark IN NUMBER DEFAULT NULL
, p_from_last_update_date IN DATE DEFAULT NULL
, p_to_last_update_date IN DATE DEFAULT NULL
, x_tests_table OUT NOCOPY system.gmd_qc_tests_tab_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR test_values_cursor (p_test_id NUMBER) IS
SELECT system.gmd_qc_test_value_rec_type
( gtv.test_value_id, gtv.min_num, gtv.max_num, gtv.display_label_numeric_range, gtv.test_value_desc
, gtv.value_char, gtv.text_range_seq, gtv.expression_ref_test_id, gtv.text_code, gtv.attribute_category
, gtv.attribute1, gtv.attribute2, gtv.attribute3, gtv.attribute4, gtv.attribute5, gtv.attribute6, gtv.attribute7
, gtv.attribute8, gtv.attribute9, gtv.attribute10, gtv.attribute11, gtv.attribute12, gtv.attribute13, gtv.attribute14
, gtv.attribute15, gtv.attribute16, gtv.attribute17, gtv.attribute18, gtv.attribute19, gtv.attribute20
, gtv.attribute21, gtv.attribute22, gtv.attribute23, gtv.attribute24, gtv.attribute25, gtv.attribute26
, gtv.attribute27, gtv.attribute28, gtv.attribute29, gtv.attribute30, gtv.creation_date, gtv.created_by
, fu1.user_name, gtv.last_update_date, gtv.last_updated_by, fu2.user_name
, gtv.last_update_login
)
FROM gmd_qc_test_values gtv, fnd_user fu1, fnd_user fu2
WHERE gtv.test_id = p_test_id
AND fu1.user_id = gtv.created_by
AND fu2.user_id = gtv.last_updated_by;
SELECT system.gmd_customer_test_rec_type
( gct.cust_id, ocm.cust_no, gct.report_precision, gct.cust_test_display, gct.text_code
, gct.creation_date, gct.created_by, fu1.user_name, gct.last_update_date
, gct.last_updated_by, fu2.user_name, gct.last_update_login
)
FROM gmd_customer_tests gct, op_cust_mst ocm, fnd_user fu1, fnd_user fu2
WHERE gct.test_id = p_test_id
AND gct.cust_id = ocm.cust_id
AND gct.created_by = fu1.user_id
AND gct.last_updated_by = fu2.user_id;
sql_statement := 'SELECT ';
||', gqt.delete_mark, gqt.text_code, gqt.attribute_category, gqt.attribute1, gqt.attribute2'
||', gqt.attribute3, gqt.attribute4, gqt.attribute5, gqt.attribute6, gqt.attribute7, gqt.attribute8'
||', gqt.attribute9, gqt.attribute10, gqt.attribute11, gqt.attribute12, gqt.attribute13, gqt.attribute14'
||', gqt.attribute15, gqt.attribute16, gqt.attribute17, gqt.attribute18, gqt.attribute19, gqt.attribute20'
||', gqt.attribute21, gqt.attribute22, gqt.attribute23, gqt.attribute24, gqt.attribute25, gqt.attribute26'
||', gqt.attribute27, gqt.attribute28, gqt.attribute29, gqt.attribute30, gqt.creation_date'
||', gqt.created_by, fu1.user_name, gqt.last_update_date, gqt.last_updated_by'
||', fu2.user_name, gqt.last_update_login'
||', system.gmd_qc_test_values_tab_type (NULL), system.gmd_customer_tests_tab_type(NULL)' -- 5284242
||')';
||' AND fu2.user_id=gqt.last_updated_by'
||' AND 1=:dummy ';
IF p_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_delete_mark := p_delete_mark;
where_clause := where_clause||' AND gqt.delete_mark = :delete_mark';
using_clause := using_clause||', gmd_outbound_apis_pub.g_delete_mark';
IF p_from_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_last_update_date := p_from_last_update_date;
where_clause := where_clause||' AND gqt.last_update_date >= :from_last_update_date';
using_clause := using_clause||', gmd_outbound_apis_pub.g_from_last_update_date';
IF p_to_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_last_update_date := p_to_last_update_date;
where_clause := where_clause||' AND gqt.last_update_date <= :to_last_update_date';
using_clause := using_clause||', gmd_outbound_apis_pub.g_to_last_update_date';
, p_from_spec_last_update IN DATE DEFAULT NULL
, p_to_spec_last_update IN DATE DEFAULT NULL
, p_spec_status IN NUMBER DEFAULT NULL
, p_owner_organization_code IN VARCHAR2 DEFAULT NULL
, p_spec_delete_mark IN NUMBER DEFAULT NULL
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
, p_overlay_ind IN VARCHAR2 DEFAULT NULL
, p_spec_type IN VARCHAR2 DEFAULT NULL
, p_base_spec_id IN NUMBER DEFAULT NULL
, p_base_spec_name IN VARCHAR2 DEFAULT NULL
, p_base_spec_version IN NUMBER DEFAULT NULL
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
-- Parameters relating to spec tests
, p_test_code IN VARCHAR2 DEFAULT NULL
, p_test_id IN NUMBER DEFAULT NULL
, p_test_method_code IN VARCHAR2 DEFAULT NULL
, p_test_method_id IN NUMBER DEFAULT NULL
, p_test_qty_uom IN VARCHAR2 DEFAULT NULL
, p_test_priority IN VARCHAR2 DEFAULT NULL
, p_from_test_last_update IN DATE DEFAULT NULL
, p_to_test_last_update IN DATE DEFAULT NULL
, p_test_delete_mark IN NUMBER DEFAULT NULL
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
, p_from_base_ind IN VARCHAR2 DEFAULT NULL
, p_exclude_ind IN VARCHAR2 DEFAULT NULL
, p_modified_ind IN VARCHAR2 DEFAULT NULL
, p_calc_uom_conv_ind IN VARCHAR2 DEFAULT NULL
, p_to_qty_uom IN VARCHAR2 DEFAULT NULL
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
-- Parameters relating to wip spec validity rules
, p_wip_vr_status IN NUMBER DEFAULT NULL
, p_wip_vr_organization_code IN VARCHAR2 DEFAULT NULL
, p_wip_vr_batch_orgn_code IN VARCHAR2 DEFAULT NULL
, p_wip_vr_batch_no IN VARCHAR2 DEFAULT NULL
, p_wip_vr_batch_id IN NUMBER DEFAULT NULL
, p_wip_vr_recipe_no IN VARCHAR2 DEFAULT NULL
, p_wip_vr_recipe_version IN NUMBER DEFAULT NULL
, p_wip_vr_recipe_id IN NUMBER DEFAULT NULL
, p_wip_vr_formula_no IN VARCHAR2 DEFAULT NULL
, p_wip_vr_formula_version IN NUMBER DEFAULT NULL
, p_wip_vr_formula_id IN NUMBER DEFAULT NULL
, p_wip_vr_formulaline_no IN NUMBER DEFAULT NULL
, p_wip_vr_formulaline_id IN NUMBER DEFAULT NULL
, p_wip_vr_line_type IN NUMBER DEFAULT NULL
, p_wip_vr_routing_no IN VARCHAR2 DEFAULT NULL
, p_wip_vr_routing_version IN NUMBER DEFAULT NULL
, p_wip_vr_routing_id IN NUMBER DEFAULT NULL
, p_wip_vr_step_no IN NUMBER DEFAULT NULL
, p_wip_vr_step_id IN NUMBER DEFAULT NULL
, p_wip_vr_operation_no IN VARCHAR2 DEFAULT NULL
, p_wip_vr_operation_version IN NUMBER DEFAULT NULL
, p_wip_vr_operation_id IN NUMBER DEFAULT NULL
, p_wip_vr_start_date IN DATE DEFAULT NULL
, p_wip_vr_end_date IN DATE DEFAULT NULL
, p_wip_vr_coa_type IN VARCHAR2 DEFAULT NULL
, p_wip_vr_sampling_plan IN VARCHAR2 DEFAULT NULL
, p_wip_vr_sampling_plan_id IN NUMBER DEFAULT NULL
, p_wip_vr_delete_mark IN NUMBER DEFAULT NULL
, p_wip_vr_from_last_update IN DATE DEFAULT NULL
, p_wip_vr_to_last_update IN DATE DEFAULT NULL
-- Parameters relating to customer spec validity rules
, p_cust_vr_start_date IN DATE DEFAULT NULL
, p_cust_vr_end_date IN DATE DEFAULT NULL
, p_cust_vr_status IN NUMBER DEFAULT NULL
, p_cust_vr_organization_code IN VARCHAR2 DEFAULT NULL
, p_cust_vr_org_id IN NUMBER DEFAULT NULL
, p_cust_vr_coa_type IN VARCHAR2 DEFAULT NULL
, p_cust_vr_customer IN VARCHAR2 DEFAULT NULL
, p_cust_vr_customer_id IN NUMBER DEFAULT NULL
, p_cust_vr_order_number IN NUMBER DEFAULT NULL
, p_cust_vr_order_id IN NUMBER DEFAULT NULL
, p_cust_vr_order_type IN NUMBER DEFAULT NULL
, p_cust_vr_order_line_no IN NUMBER DEFAULT NULL
, p_cust_vr_order_line_id IN NUMBER DEFAULT NULL
, p_cust_vr_ship_to_location IN VARCHAR2 DEFAULT NULL
, p_cust_vr_ship_to_site_id IN NUMBER DEFAULT NULL
, p_cust_vr_operating_unit IN VARCHAR
, p_cust_vr_delete_mark IN NUMBER DEFAULT NULL
, p_cust_vr_from_last_update IN DATE DEFAULT NULL
, p_cust_vr_to_last_update IN DATE DEFAULT NULL
-- Parameters relating to supplier spec validity rules
, p_supl_vr_start_date IN DATE DEFAULT NULL
, p_supl_vr_end_date IN DATE DEFAULT NULL
, p_supl_vr_status IN NUMBER DEFAULT NULL
, p_supl_vr_organization_code IN VARCHAR2 DEFAULT NULL
, p_supl_vr_org_id IN NUMBER DEFAULT NULL
, p_supl_vr_coa_type IN VARCHAR2 DEFAULT NULL
, p_supl_vr_supplier IN VARCHAR2 DEFAULT NULL
, p_supl_vr_supplier_id IN NUMBER DEFAULT NULL
, p_supl_vr_po_number IN NUMBER DEFAULT NULL
, p_supl_vr_po_id IN NUMBER DEFAULT NULL
, p_supl_vr_po_line_no IN NUMBER DEFAULT NULL
, p_supl_vr_po_line_id IN NUMBER DEFAULT NULL
, p_supl_vr_supplier_site IN VARCHAR2 DEFAULT NULL
, p_supl_vr_supplier_site_id IN NUMBER DEFAULT NULL
, p_supl_vr_operating_unit IN VARCHAR2 DEFAULT NULL
, p_supl_vr_delete_mark IN NUMBER DEFAULT NULL
, p_supl_vr_from_last_update IN DATE DEFAULT NULL
, p_supl_vr_to_last_update IN DATE DEFAULT NULL
-- Parameters relating to inventory spec validity rules
, p_inv_vr_start_date IN DATE DEFAULT NULL
, p_inv_vr_end_date IN DATE DEFAULT NULL
, p_inv_vr_status IN NUMBER DEFAULT NULL
, p_inv_vr_organization_code IN VARCHAR2 DEFAULT NULL
, p_inv_vr_coa_type IN VARCHAR2 DEFAULT NULL
, p_inv_vr_item_number IN VARCHAR2 DEFAULT NULL
, p_inv_vr_inventory_item_id IN NUMBER DEFAULT NULL
, p_inv_vr_parent_lot_number IN VARCHAR2 DEFAULT NULL
, p_inv_vr_lot_number IN VARCHAR2 DEFAULT NULL
, p_inv_vr_subinventory IN VARCHAR2 DEFAULT NULL
, p_inv_vr_locator IN VARCHAR2 DEFAULT NULL
, p_inv_vr_locator_id IN NUMBER DEFAULT NULL
, p_inv_vr_sampling_plan IN VARCHAR2 DEFAULT NULL
, p_inv_vr_sampling_plan_id IN NUMBER DEFAULT NULL
, p_inv_vr_delete_mark IN NUMBER DEFAULT NULL
, p_inv_vr_from_last_update IN DATE DEFAULT NULL
, p_inv_vr_to_last_update IN DATE DEFAULT NULL
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
-- Parameters relating to monitor spec
, p_mon_vr_status IN NUMBER DEFAULT NULL
, p_mon_vr_rule_type IN VARCHAR2 DEFAULT NULL
, p_mon_vr_lct_organization_code IN VARCHAR2 DEFAULT NULL
, p_mon_vr_subinventory IN VARCHAR2 DEFAULT NULL
, p_mon_vr_locator_id IN NUMBER DEFAULT NULL
, p_mon_vr_locator IN VARCHAR2 DEFAULT NULL
, p_mon_vr_rsr_organization_code IN VARCHAR2 DEFAULT NULL
, p_mon_vr_resources IN VARCHAR2 DEFAULT NULL
, p_mon_vr_resource_instance_id IN NUMBER DEFAULT NULL
, p_mon_vr_sampling_plan IN VARCHAR2 DEFAULT NULL
, p_mon_vr_sampling_plan_id IN NUMBER DEFAULT NULL
, p_mon_vr_start_date IN DATE DEFAULT NULL
, p_mon_vr_end_date IN DATE DEFAULT NULL
, p_mon_vr_from_last_update_date IN DATE DEFAULT NULL
, p_mon_vr_to_last_update_date IN DATE DEFAULT NULL
, p_mon_vr_delete_mark IN NUMBER DEFAULT NULL
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
-- Return parameters
, x_specifications_tbl OUT NOCOPY system.gmd_specifications_tab_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
sql_statement VARCHAR2(32000);
/* SELECT gmd_specifications_rec_type
(
, CAST
( MULTISET
( gmd_spec_test_rec_type
( SELECT
FROM
WHERE ***
AS system.gmd_spec_tests_tab_type
)
, CAST
( MULTISET
( gmd_cust_spec_vrs_rec_type
( SELECT
FROM
WHERE ***
AS system.gmd_cust_spec_vrs_tab_type
)
, CAST
( MULTISET
( gmd_wip_spec_vrs_rec_type
( SELECT
FROM
WHERE ***
AS system.gmd_wip_spec_vrs_tab_type
)
, CAST
( MULTISET
( gmd_supl_spec_vrs_rec_type
( SELECT
FROM
WHERE ***
AS system.gmd_supl_spec_vrs_tab_type
)
, CAST
( MULTISET
( gmd_inv_spec_vrs_rec_type
( SELECT
FROM
WHERE ***
AS system.gmd_inv_spec_vrs_tab_type
)
)
FROM
WHERE
****
*/
-- The lines marked *** are the slightly complicated ones to construct as the conditions
-- have to have the appropriate bind variables embedded.
-- The whole statement is then put into an EXECUTE IMMEDIATE statement to pass it
-- to the database. Here goes......
-- GMD_SPECIFICATIONS basic clauses
main_column_list := ' gs.spec_id, gs.spec_name, gs.spec_vers, gs.spec_desc'
||', gs.inventory_item_id, NULL, gs.grade_code, gs.revision, gs.spec_status, gstat.description'
||', gs.owner_organization_id, gs.owner_id, fu3.user_name'
||', gs.sample_inv_trans_ind'
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
||', gs.overlay_ind, gs.spec_type, gs.base_spec_id'
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
||', gs.delete_mark, gs.text_code'
||', gs.attribute_category, gs.attribute1, gs.attribute2, gs.attribute3'
||', gs.attribute4, gs.attribute5, gs.attribute6, gs.attribute7'
||', gs.attribute8, gs.attribute9, gs.attribute10, gs.attribute11'
||', gs.attribute12, gs.attribute13, gs.attribute14, gs.attribute15'
||', gs.attribute16, gs.attribute17, gs.attribute18, gs.attribute19'
||', gs.attribute20, gs.attribute21, gs.attribute22, gs.attribute23'
||', gs.attribute24, gs.attribute25, gs.attribute26, gs.attribute27'
||', gs.attribute28, gs.attribute29, gs.attribute30, gs.creation_date'
||', gs.created_by, fu1.user_name, gs.last_update_date'
||', gs.last_updated_by, fu2.user_name, gs.last_update_login';
||' AND gs.last_updated_by = fu2.user_id'
||' AND gs.owner_id = fu3.user_id'
||' AND to_char(gs.spec_status) = gstat.status_code'
||' AND 1=:dummy1 ';
||', gst.created_by, fu4.user_name, gst.last_update_date'
||', gst.last_updated_by, fu5.user_name, gst.last_update_login'
||')';
spec_test_where_clause := ' gst.created_by = fu4.user_id AND gst.last_updated_by=fu5.user_id '
||' AND gst.test_id = gqt.test_id AND gst.test_method_id=gtm.test_method_id'
||' AND gst.spec_id = gs.spec_id AND 1=:dummy2 ';
||', cvr.delete_mark, cvr.text_code, cvr.attribute_category'
||', cvr.attribute1, cvr.attribute2, cvr.attribute3, cvr.attribute4, cvr.attribute5'
||', cvr.attribute6, cvr.attribute7, cvr.attribute8, cvr.attribute9, cvr.attribute10'
||', cvr.attribute11, cvr.attribute12, cvr.attribute13, cvr.attribute14, cvr.attribute15'
||', cvr.attribute16, cvr.attribute17, cvr.attribute18, cvr.attribute19, cvr.attribute20'
||', cvr.attribute21, cvr.attribute22, cvr.attribute23, cvr.attribute24, cvr.attribute25'
||', cvr.attribute26, cvr.attribute27, cvr.attribute28, cvr.attribute29, cvr.attribute30'
||', cvr.creation_date, cvr.created_by, fu6.user_name, cvr.last_update_date'
||', cvr.last_updated_by, fu7.user_name, cvr.last_update_login'
||')';
cust_vr_where_clause := ' cvr.created_by = fu6.user_id AND cvr.last_updated_by = fu7.user_id'
||' AND cvr.spec_id = gs.spec_id AND cvr.order_id = oeh.header_id(+) AND 1=:dummy3';
||', wvr.coa_req_from_supl_ind, wvr.delete_mark, wvr.text_code, wvr.attribute_category'
||', wvr.attribute1, wvr.attribute2, wvr.attribute3, wvr.attribute4, wvr.attribute5'
||', wvr.attribute6, wvr.attribute7, wvr.attribute8, wvr.attribute9, wvr.attribute10'
||', wvr.attribute11, wvr.attribute12, wvr.attribute13, wvr.attribute14, wvr.attribute15'
||', wvr.attribute16, wvr.attribute17, wvr.attribute18, wvr.attribute19, wvr.attribute20'
||', wvr.attribute21, wvr.attribute22, wvr.attribute23, wvr.attribute24, wvr.attribute25'
||', wvr.attribute26, wvr.attribute27, wvr.attribute28, wvr.attribute29, wvr.attribute30'
||', wvr.creation_date, wvr.created_by, fu8.user_name, wvr.last_update_date'
||', wvr.last_updated_by, fu9.user_name, wvr.last_update_login'
||')';
wip_vr_where_clause := ' wvr.created_by = fu8.user_id AND wvr.last_updated_by = fu9.user_id'
||' AND wvr.batch_id= gbh.batch_id(+) and gbh.batch_type(+) = 0'
||' AND wvr.spec_id = gs.spec_id and 1=:dummy4';
||', svr.delete_mark, svr.text_code, svr.attribute_category, svr.attribute1'
||', svr.attribute2, svr.attribute3, svr.attribute4, svr.attribute5, svr.attribute6'
||', svr.attribute7, svr.attribute8, svr.attribute9, svr.attribute10, svr.attribute11'
||', svr.attribute12, svr.attribute13, svr.attribute14, svr.attribute15, svr.attribute16'
||', svr.attribute17, svr.attribute18, svr.attribute19, svr.attribute20, svr.attribute21'
||', svr.attribute22, svr.attribute23, svr.attribute24, svr.attribute25, svr.attribute26'
||', svr.attribute27, svr.attribute28, svr.attribute29, svr.attribute30, svr.creation_date'
||', svr.created_by, fu10.user_name, svr.last_update_date, svr.last_updated_by'
||', fu11.user_name, svr.last_update_login'
||')';
supl_vr_where_clause := ' svr.created_by = fu10.user_id AND svr.last_updated_by = fu11.user_id'
||' AND svr.spec_id = gs.spec_id AND v.vendor_id = svr.supplier_id AND 1=:dummy5';
||', ivr.delete_mark, ivr.text_code, ivr.attribute_category, ivr.attribute1'
||', ivr.attribute2, ivr.attribute3, ivr.attribute4, ivr.attribute5, ivr.attribute6'
||', ivr.attribute7, ivr.attribute8, ivr.attribute9, ivr.attribute10, ivr.attribute11'
||', ivr.attribute12, ivr.attribute13, ivr.attribute14, ivr.attribute15'
||', ivr.attribute16, ivr.attribute17, ivr.attribute18, ivr.attribute19'
||', ivr.attribute20, ivr.attribute21, ivr.attribute22, ivr.attribute23'
||', ivr.attribute24, ivr.attribute25, ivr.attribute26, ivr.attribute27'
||', ivr.attribute28, ivr.attribute29, ivr.attribute30, ivr.creation_date'
||', ivr.created_by, fu12.user_name, ivr.last_update_date, ivr.last_updated_by'
||', fu13.user_name, ivr.last_update_login'
||')';
inv_vr_where_clause := ' ivr.created_by = fu12.user_id AND ivr.last_updated_by = fu13.user_id'
||' AND ivr.spec_id = gs.spec_id AND 1=:dummy6';
||', mvr.sampling_plan_id, NULL, mvr.delete_mark, mvr.text_code'
||', mvr.attribute_category, mvr.attribute1, mvr.attribute2, mvr.attribute3'
||', mvr.attribute4, mvr.attribute5, mvr.attribute6, mvr.attribute7, mvr.attribute8'
||', mvr.attribute9, mvr.attribute10, mvr.attribute11, mvr.attribute12, mvr.attribute13'
||', mvr.attribute14, mvr.attribute15, mvr.attribute16, mvr.attribute17, mvr.attribute18'
||', mvr.attribute19, mvr.attribute20, mvr.attribute21, mvr.attribute22, mvr.attribute23'
||', mvr.attribute24, mvr.attribute25, mvr.attribute26, mvr.attribute27, mvr.attribute28'
||', mvr.attribute29, mvr.attribute30, mvr.creation_date, mvr.created_by'
||', mvr.last_updated_by, mvr.last_update_date, mvr.last_update_login'
||')';
mon_vr_where_clause := ' mvr.created_by = fu14.user_id AND mvr.last_updated_by = fu15.user_id'
||' AND mvr.spec_id = gs.spec_id AND 1=:dummy7';
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments BETWEEN :from_item_number AND :to_item_number'
||' AND organization_id = gs.owner_organization_id)';
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments >= :from_item_number'
||' AND organization_id = gs.owner_organization_id)';
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments <= :to_item_number'
||' AND organization_id = gs.owner_organization_id)';
main_where_clause := main_where_clause|| ' AND gs.owner_organization_id = (SELECT organization_id'
|| ' FROM mtl_organizations WHERE organization_code = :owner_organization_code)';
IF p_spec_delete_mark IS NOT NULL
THEN
g_spec_delete_mark := p_spec_delete_mark;
main_where_clause := main_where_clause|| ' AND gs.delete_mark = :delete_mark';
main_using_clause := main_using_clause|| ', gmd_outbound_apis_pub.g_spec_delete_mark';
IF p_from_spec_last_update IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_spec_last_update := p_from_spec_last_update;
main_where_clause := main_where_clause||' AND gs.last_update_date >= :from_spec_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_from_spec_last_update';
IF p_to_spec_last_update IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_spec_last_update := p_to_spec_last_update;
main_where_clause := main_where_clause||' AND gs.last_update_date <= :to_spec_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_to_spec_last_update';
OR p_from_test_last_update IS NOT NULL OR p_to_test_last_update IS NOT NULL
OR p_test_delete_mark IS NOT NULL
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
OR p_from_base_ind IS NOT NULL OR p_exclude_ind IS NOT NULL OR p_modified_ind IS NOT NULL
OR p_calc_uom_conv_ind IS NOT NULL OR p_to_qty_uom IS NOT NULL
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
THEN
-- Add the tables to the main list and join to it.
main_where_clause := main_where_clause
||' AND gs.spec_id IN '
||' (SELECT mgst.spec_id FROM gmd_spec_tests mgst '
||' WHERE 1=1';
IF p_from_test_last_update IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_test_last_update := p_from_test_last_update;
main_where_clause := main_where_clause||' AND mgst.last_update_date >= :p_from_test_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_from_test_last_update';
spec_test_where_clause := spec_test_where_clause||' AND gst.last_update_date >= :p_from_test_last_update';
spec_test_using_clause := spec_test_using_clause||', gmd_outbound_apis_pub.g_from_test_last_update';
IF p_to_test_last_update IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_test_last_update := p_to_test_last_update;
main_where_clause := main_where_clause||' AND mgst.to_update_date <= :p_to_test_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_to_test_last_update';
spec_test_where_clause := spec_test_where_clause||' AND gst.to_update_date <= :p_to_test_last_update';
spec_test_using_clause := spec_test_using_clause||', gmd_outbound_apis_pub.g_to_test_last_update';
IF p_test_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_test_delete_mark := p_test_delete_mark;
main_where_clause := main_where_clause||' AND mgst.delete_mark = :p_test_delete_mark';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_test_delete_mark';
spec_test_where_clause := spec_test_where_clause||' AND gst.delete_mark = :p_test_delete_mark';
spec_test_using_clause := spec_test_using_clause||', gmd_outbound_apis_pub.g_test_delete_mark';
OR p_wip_vr_delete_mark IS NOT NULL OR p_wip_vr_from_last_update IS NOT NULL OR p_wip_vr_to_last_update IS NOT NULL
THEN
-- Add the table to the main list and join to it.
main_where_clause := main_where_clause
||' AND gs.spec_id IN'
||' (SELECT mwvr.spec_id'
||' FROM gmd_wip_spec_vrs mwvr'
||' WHERE 1=1';
main_where_clause := main_where_clause||' AND mwvr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :wip_vr_organization_code)';
wip_vr_where_clause := wip_vr_where_clause||' AND wvr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :wip_vr_organization_code)';
||' (SELECT batch_id FROM gme_batch_header '
||' WHERE organization_id = (SELECT organization_id FROM'
||' mtl_parameters WHERE organization_code = :wip_vr_batch_orgn_code) )';
||' (SELECT batch_id FROM gme_batch_header '
||' WHERE organization_id = (SELECT organization_id FROM'
||' mtl_parameters WHERE organization_code = :wip_vr_batch_orgn_code) )';
||' (SELECT batch_id FROM gme_batch_header '
||' WHERE batch_no = :wip_vr_batch_no '
||' AND batch_type = 0)';
||' (SELECT batch_id FROM gme_batch_header '
||' WHERE batch_no = :wip_vr_batch_no '
||' AND batch_type = 0)';
IF p_wip_vr_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_wip_vr_delete_mark := p_wip_vr_delete_mark;
main_where_clause := main_where_clause||' AND mwvr.delete_mark >= :wip_vr_delete_mark';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_wip_vr_delete_mark';
wip_vr_where_clause := wip_vr_where_clause||' AND wvr.delete_mark >= :wip_vr_delete_mark';
wip_vr_using_clause := wip_vr_using_clause||', gmd_outbound_apis_pub.g_wip_vr_delete_mark';
IF p_wip_vr_from_last_update IS NOT NULL
THEN
gmd_outbound_apis_pub.g_wip_vr_from_last_update := p_wip_vr_from_last_update;
main_where_clause := main_where_clause||' AND mwvr.last_update_date >= :wip_vr_from_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_wip_vr_from_last_update';
wip_vr_where_clause := wip_vr_where_clause||' AND wvr.last_update_date >= :wip_vr_from_last_update';
wip_vr_using_clause := wip_vr_using_clause||', gmd_outbound_apis_pub.g_wip_vr_from_last_update';
IF p_wip_vr_to_last_update IS NOT NULL
THEN
gmd_outbound_apis_pub.g_wip_vr_to_last_update := p_wip_vr_to_last_update;
main_where_clause := main_where_clause||' AND mwvr.last_update_date <= :wip_vr_to_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_wip_vr_to_last_update';
wip_vr_where_clause := wip_vr_where_clause||' AND wvr.last_update_date <= :wip_vr_to_last_update';
wip_vr_using_clause := wip_vr_using_clause||', gmd_outbound_apis_pub.g_wip_vr_to_last_update';
OR p_cust_vr_delete_mark IS NOT NULL OR p_cust_vr_from_last_update IS NOT NULL
OR p_cust_vr_to_last_update IS NOT NULL
THEN
-- Add the table to the list and join to it.
main_where_clause := main_where_clause
||' AND gs.spec_id IN'
||' (SELECT mcvr.spec_id FROM gmd_customer_spec_vrs mcvr'
||' WHERE 1=1';
main_where_clause := main_where_clause||' AND mcvr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :cust_vr_organization_code)';
cust_vr_where_clause := cust_vr_where_clause||' AND cvr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :cust_vr_organization_code)';
||' (SELECT hzca.cust_account_id'
||' FROM hz_parties hzp, hz_cust_accounts_all hzca'
||' WHERE hzp.party_id = hzca.party_id AND'
||' UPPER(hzp.party_name)'
||' LIKE UPPER(:cust_vr_customer)'
||' )';
||' (SELECT hzca.cust_account_id'
||' FROM hz_parties hzp, hz_cust_accounts_all hzca'
||' WHERE hzp.party_id = hzca.party_id AND'
||' UPPER(hzp.party_name)'
||' LIKE UPPER(:cust_vr_customer)'
||' )';
main_where_clause := main_where_clause||' AND mcvr.order_id = (select header_id '
||' from oe_order_headers_all'
||' where order_number = :cust_vr_order_number)';
IF p_cust_vr_delete_mark IS NOT NULL
THEN
g_cust_vr_delete_mark := p_cust_vr_delete_mark;
main_where_clause := main_where_clause||' AND mcvr.delete_mark = :cust_vr_delete_mark';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_cust_vr_delete_mark';
cust_vr_where_clause := cust_vr_where_clause||' AND cvr.delete_mark = :cust_vr_delete_mark';
cust_vr_using_clause := cust_vr_using_clause||', gmd_outbound_apis_pub.g_cust_vr_delete_mark';
IF p_cust_vr_from_last_update IS NOT NULL
THEN
g_cust_vr_from_last_update := p_cust_vr_from_last_update;
main_where_clause := main_where_clause||' AND mcvr.last_update_date >= :cust_vr_from_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_cust_vr_from_last_update';
cust_vr_where_clause := cust_vr_where_clause||' AND cvr.last_update_date >= :cust_vr_from_last_update';
cust_vr_using_clause := cust_vr_using_clause||', gmd_outbound_apis_pub.g_cust_vr_from_last_update';
IF p_cust_vr_to_last_update IS NOT NULL
THEN
g_cust_vr_to_last_update := p_cust_vr_to_last_update;
main_where_clause := main_where_clause||' AND mcvr.last_update_date <= :cust_vr_to_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_cust_vr_to_last_update';
cust_vr_where_clause := cust_vr_where_clause||' AND cvr.last_update_date <= :cust_vr_to_last_update';
cust_vr_using_clause := cust_vr_using_clause||', gmd_outbound_apis_pub.g_cust_vr_to_last_update';
main_where_clause := main_where_clause||' AND mcvr.order_id IN (select header_id '
||' FROM oe_order_headers_all h, oe_transaction_types_all t'
||' WHERE h.order_type_id = t.transaction_type_id'
||' AND t.transaction_type_code = :cust_vr_order_type)';
||' (SELECT site_use_id'
||' FROM hz_cust_site_uses_all'
||' WHERE location = :cust_vr_ship_to_location)';
||' (SELECT site_use_id'
||' FROM hz_cust_site_uses_all'
||' WHERE location = :cust_vr_ship_to_location)';
||' (SELECT organization_id'
||' FROM hr_operating_units'
||' WHERE name = :cust_vr_operating_unit)';
||' (SELECT organization_id'
||' FROM hr_operating_units'
||' WHERE name = :cust_vr_operating_unit)';
OR p_supl_vr_operating_unit IS NOT NULL OR p_supl_vr_delete_mark IS NOT NULL
OR p_supl_vr_from_last_update IS NOT NULL OR p_supl_vr_to_last_update IS NOT NULL
THEN
-- Include the table in the list, and join to it.
main_where_clause := main_where_clause
||' AND gs.spec_id IN'
||' ( SELECT spec_id fROM gmd_supplier_spec_vrs msvr, po_vendors mpv'
||' WHERE msvr.supplier_id = mpv.vendor_id';
main_where_clause := main_where_clause||' AND msvr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :supl_vr_organization_code)';
supl_vr_where_clause := supl_vr_where_clause||' AND svr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :supl_vr_organization_code)';
||' (SELECT pla.po_line_id FROM po_headers_all pha, po_lines_all pla'
||' WHERE pha.segment1 = :supl_vr_po_number'
||' AND pha.po_header_id = pla.po_header_id)';
||' (SELECT pla.po_line_id FROM po_headers_all pha, po_lines_all pla'
||' WHERE pha.segment1 = :supl_vr_po_number'
||' AND pha.po_header_id = pla.po_header_id)';
||' (SELECT po_line_id FROM po_lines_all'
||' WHERE po_header_id = :supl_vr_po_id)';
||' (SELECT po_line_id FROM po_lines_all'
||' WHERE po_header_id = :supl_vr_po_id)';
||' (SELECT po_line_id FROM po_lines_all'
||' WHERE line_num = :supl_vr_po_line_no)';
||' (SELECT po_line_id FROM po_lines_all'
||' WHERE line_num = :supl_vr_po_line_no)';
||' (SELECT vendor_site_id FROM po_vendor_sites_all'
||' WHERE vendor_site_code = :supl_vr_supplier_site';
||' (SELECT vendor_site_id FROM po_vendor_sites_all'
||' WHERE vendor_site_code = :supl_vr_supplier_site';
||' (SELECT organization_id'
||' FROM hr_operating_units'
||' WHERE name = :supl_vr_operating_unit)';
||' (SELECT organization_id'
||' FROM hr_operating_units'
||' WHERE name = :supl_vr_operating_unit)';
IF p_supl_vr_from_last_update IS NOT NULL
THEN
g_supl_vr_from_last_update := p_supl_vr_from_last_update;
main_where_clause := main_where_clause||' AND msvr.last_update_date >= :supl_vr_from_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_supl_vr_from_last_update';
supl_vr_where_clause := supl_vr_where_clause||' AND svr.last_update_date >= :supl_vr_from_last_update';
supl_vr_using_clause := supl_vr_using_clause||', gmd_outbound_apis_pub.g_supl_vr_from_last_update';
IF p_supl_vr_to_last_update IS NOT NULL
THEN
g_supl_vr_to_last_update := p_supl_vr_to_last_update;
main_where_clause := main_where_clause||' AND msvr.last_update_date <= :supl_vr_to_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_supl_vr_to_last_update';
supl_vr_where_clause := supl_vr_where_clause||' AND svr.last_update_date <= :supl_vr_to_last_update';
supl_vr_using_clause := supl_vr_using_clause||', gmd_outbound_apis_pub.g_supl_vr_to_last_update';
IF p_supl_vr_delete_mark IS NOT NULL
THEN
g_supl_vr_delete_mark := p_supl_vr_delete_mark;
main_where_clause := main_where_clause||' AND msvr.delete_mark =:supl_vr_delete_mark';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_supl_vr_delete_mark';
supl_vr_where_clause := supl_vr_where_clause||' AND svr.delete_mark = :supl_vr_delete_mark';
supl_vr_using_clause := supl_vr_using_clause||', gmd_outbound_apis_pub.g_supl_vr_delete_mark';
OR p_inv_vr_delete_mark IS NOT NULL OR p_inv_vr_from_last_update IS NOT NULL OR p_inv_vr_to_last_update IS NOT NULL
THEN
-- Include the table in the list, and join to it.
main_where_clause := main_where_clause
||' AND gs.spec_id IN'
||' (SELECT mivr.spec_id FROM gmd_inventory_spec_vrs mivr'
||' WHERE 1=1';
main_where_clause := main_where_clause||' AND mivr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :inv_vr_organization_code)';
inv_vr_where_clause := inv_vr_where_clause||' AND ivr.organization_id = (SELECT organization_id '
||'FROM mtl_organizations WHERE organization_code = :inv_vr_organization_code)';
||' (SELECT lot_number FROM mtl_lot_numbers'
||' WHERE inventory_item_id = :inv_vr_inventory_item_id)';
||' (SELECT lot_number FROM mtl_lot_numbers'
||' WHERE inventory_item_id = :inv_vr_inventory_item_id)';
||' (SELECT l.lot_number FROM mtl_lot_numbers l'
||' WHERE l.organization_id IN (SELECT organization_id FROM'
||' mtl_system_items_b_kfv WHERE concatenated_segments = :inv_vr_item_number'
||' AND inventory_item_id = l.inventory_item_id))';
||' (SELECT l.lot_number FROM mtl_lot_numbers l'
||' WHERE l.organization_id IN (SELECT organization_id FROM'
||' mtl_system_items_b_kfv WHERE concatenated_segments = :inv_vr_item_number'
||' AND inventory_item_id = l.inventory_item_id))';
main_where_clause := main_where_clause||' AND mivr.locator_id = (SELECT inventory_location_id '
||'FROM mtl_item_locations_kfv WHERE concatenated_segments = :inv_vr_locator '
||'AND organization_id = mivr.organization_id)';
inv_vr_where_clause := inv_vr_where_clause||' AND ivr.locator_id = (SELECT inventory_location_id '
||'FROM mtl_item_locations_kfv WHERE concatenated_segments = :inv_vr_locator '
||'AND organization_id = ivr.organization_id)';
IF p_inv_vr_from_last_update IS NOT NULL
THEN
g_inv_vr_from_last_update := p_inv_vr_from_last_update;
main_where_clause := main_where_clause||' AND mivr.last_update_date >= :inv_vr_from_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_inv_vr_from_last_update';
inv_vr_where_clause := inv_vr_where_clause||' AND ivr.last_update_date >= :inv_vr_from_last_update';
inv_vr_using_clause := inv_vr_using_clause||', gmd_outbound_apis_pub.g_inv_vr_from_last_update';
IF p_inv_vr_to_last_update IS NOT NULL
THEN
g_inv_vr_to_last_update := p_inv_vr_to_last_update;
main_where_clause := main_where_clause||' AND mivr.last_update_date <= :inv_vr_to_last_update';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_inv_vr_to_last_update';
inv_vr_where_clause := inv_vr_where_clause||' AND ivr.last_update_date <= :inv_vr_to_last_update';
inv_vr_using_clause := inv_vr_using_clause||', gmd_outbound_apis_pub.g_inv_vr_to_last_update';
IF p_inv_vr_delete_mark IS NOT NULL
THEN
g_inv_vr_delete_mark := p_inv_vr_delete_mark;
main_where_clause := main_where_clause||' AND mivr.delete_mark =:inv_vr_delete_mark';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_inv_vr_delete_mark';
inv_vr_where_clause := inv_vr_where_clause||' AND ivr.delete_mark = :inv_vr_delete_mark';
inv_vr_using_clause := inv_vr_using_clause||', gmd_outbound_apis_pub.g_inv_vr_delete_mark';
p_mon_vr_from_last_update_date IS NOT NULL OR
p_mon_vr_to_last_update_date IS NOT NULL OR
p_mon_vr_delete_mark IS NOT NULL
THEN
-- Include the table in the list, and join to it.
main_where_clause := main_where_clause
||' AND gs.spec_id IN'
||' (SELECT mmvr.spec_id FROM gmd_monitoring_spec_vrs mmvr'
||' WHERE 1=1';
main_where_clause := main_where_clause||' AND mmvr.locator_organization_id = (SELECT organization_id'
||' FROM mtl_organizations WHERE organization_code = :mon_vr_lct_organization_code)';
mon_vr_where_clause := mon_vr_where_clause||' AND mvr.locator_organization_id = (SELECT organization_id'
||' FROM mtl_organizations WHERE organization_code = :mon_vr_lct_organization_code)';
main_where_clause := main_where_clause||' AND mmvr.locator_id = (SELECT inventory_location_id'
||' FROM mtl_item_locations_kfv WHERE'
||' concatenated_segments = :mon_vr_locator'
||' AND organization_id = mmvr.locator_organization_id)';
mon_vr_where_clause := mon_vr_where_clause||' AND mvr.locator_id = (SELECT inventory_location_id'
||' FROM mtl_item_locations_kfv WHERE'
||' concatenated_segments = :mon_vr_locator'
||' AND organization_id = mvr.locator_organization_id)';
main_where_clause := main_where_clause||' AND mmvr.resource_organization_id = (SELECT organization_id'
||' FROM mtl_organizations WHERE organization_code = :mon_vr_rsr_organization_code)';
mon_vr_where_clause := mon_vr_where_clause||' AND mvr.resource_organization_id = (SELECT organization_id'
||' FROM mtl_organizations WHERE organization_code = :mon_vr_rsr_organization_code)';
IF p_mon_vr_from_last_update_date IS NOT NULL
THEN
g_mon_vr_from_last_update_date := p_mon_vr_from_last_update_date;
main_where_clause := main_where_clause||' AND mmvr.last_update_date >= :mon_vr_from_last_update_date';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_mon_vr_from_last_update_date';
mon_vr_where_clause := mon_vr_where_clause||' AND mvr.last_update_date >= :mon_vr_from_last_update_date';
mon_vr_using_clause := mon_vr_using_clause||', gmd_outbound_apis_pub.g_mon_vr_from_last_update_date';
IF p_mon_vr_to_last_update_date IS NOT NULL
THEN
g_mon_vr_to_last_update_date := p_mon_vr_to_last_update_date;
main_where_clause := main_where_clause||' AND mmvr.last_update_date <= :mon_vr_to_last_update_date';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_mon_vr_to_last_update_date';
mon_vr_where_clause := mon_vr_where_clause||' AND mvr.last_update_date <= :mon_vr_to_last_update_date';
mon_vr_using_clause := mon_vr_using_clause||', gmd_outbound_apis_pub.g_mon_vr_to_last_update_date';
IF p_mon_vr_delete_mark IS NOT NULL
THEN
g_mon_vr_delete_mark := p_mon_vr_delete_mark;
main_where_clause := main_where_clause||' AND mmvr.delete_mark =:mon_vr_delete_mark';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_mon_vr_delete_mark';
mon_vr_where_clause := mon_vr_where_clause||' AND mvr.delete_mark = :mon_vr_delete_mark';
mon_vr_using_clause := mon_vr_using_clause||', gmd_outbound_apis_pub.g_mon_vr_delete_mark';
sql_statement := 'SELECT system.gmd_specification_rec_type' -- 5284247
||'('||main_column_list
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||spec_test_column_list
||' FROM '||spec_test_table_list
||' WHERE '||spec_test_where_clause
||' ) AS system.gmd_spec_tests_tab_type' -- 5284242
||' )'
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||cust_vr_column_list
||' FROM '||cust_vr_table_list
||' WHERE '||cust_vr_where_clause
||' ) AS system.gmd_cust_spec_vrs_tab_type' -- 5284242
||' )'
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||wip_vr_column_list
||' FROM '||wip_vr_table_list
||' WHERE '||wip_vr_where_clause
||' ) AS system.gmd_wip_spec_vrs_tab_type' -- 5284242
||' )'
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||supl_vr_column_list
||' FROM '||supl_vr_table_list
||' WHERE '||supl_vr_where_clause
||' ) AS system.gmd_supl_spec_vrs_tab_type' -- 5284242
||' )'
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||inv_vr_column_list
||' FROM '||inv_vr_table_list
||' WHERE '||inv_vr_where_clause
||' ) AS system.gmd_inv_spec_vrs_tab_type' -- 5284242
||' )'
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||mon_vr_column_list
||' FROM '||mon_vr_table_list
||' WHERE '||mon_vr_where_clause
||' ) AS system.gmd_mon_spec_vrs_tab_type' -- 5284242
||' )'
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
||')'
||' FROM ' ||main_table_list
||' WHERE '||main_where_clause;
SELECT concatenated_segments INTO g_specifications_table(i).item_number
FROM mtl_system_items_b_kfv
WHERE organization_id = g_specifications_table(i).owner_organization_id
AND inventory_item_id = g_specifications_table(i).inventory_item_id;
, p_delete_mark IN NUMBER DEFAULT NULL
, p_from_last_update_date IN DATE DEFAULT NULL
, p_to_last_update_date IN DATE DEFAULT NULL
, p_planned_resource IN VARCHAR2 DEFAULT NULL
, p_planned_resource_instance IN NUMBER DEFAULT NULL
, p_actual_resource IN VARCHAR2 DEFAULT NULL
, p_actual_resource_instance IN NUMBER DEFAULT NULL
, p_from_planned_result_date IN DATE DEFAULT NULL
, p_to_planned_result_date IN DATE DEFAULT NULL
, p_from_test_by_date IN DATE DEFAULT NULL
, p_to_test_by_date IN DATE DEFAULT NULL
, p_reserve_sample_id IN NUMBER DEFAULT NULL
, x_results_table OUT NOCOPY system.gmd_results_tab_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
sql_statement VARCHAR2(2000);
SELECT concatenated_segments FROM mtl_system_items_kfv
WHERE inventory_item_id = cp_inventory_item_id;
gme_debug.put_line('Begin constructing SELECT statement');
sql_statement := 'SELECT ';
column_list := 'system.gmd_results_rec_type( r.update_instance_id, r.result_id, ' -- 5346480 add system.
||'r.sample_id, gs.sample_no, r.test_id, gt.test_code, '
||'r.test_replicate_cnt, r.lab_organization_id, r.result_value_num, '
||'r.result_date, r.test_kit_inv_item_id, NULL, '
||'r.test_kit_lot_number , r.tester, r.tester_id, '
||'r.test_provider_id, r.ad_hoc_print_on_coa_ind, r.seq, '
||'r.result_value_char, r.test_provider_code, r.assay_retest, '
||'gsr.in_spec_ind, gesd.disposition, gsr.evaluation_ind, '
||'r.planned_resource, r.planned_resource_instance, '
||'r.actual_resource, r.actual_resource_instance, '
||'r.planned_result_date, r.test_by_date, '
||'r.delete_mark, r.text_code, r.attribute_category, r.attribute1, '
||'r.attribute2, r.attribute3, r.attribute4, r.attribute5, '
||'r.attribute6, r.attribute7, r.attribute8, r.attribute9, '
||'r.attribute10, r.attribute11, r.attribute12, r.attribute13, '
||'r.attribute14, r.attribute15, r.attribute16, r.attribute17, '
||'r.attribute18, r.attribute19, r.attribute20, r.attribute21, '
||'r.attribute22, r.attribute23, r.attribute24, r.attribute25, '
||'r.attribute26, r.attribute27, r.attribute28, r.attribute29, '
||'r.attribute30, r.creation_date, '
||'r.created_by, fu1.user_name, r.last_updated_by, fu2.user_name, '
||'r.last_update_date, r.last_update_login, '
||'r.test_qty, r.test_qty_uom, '
||'r.reserve_sample_id, r.consumed_qty, '
||'r.parent_result_id, r.test_method_id )';
||'AND fu1.user_id = r.created_by AND fu2.user_id = r.last_updated_by and 1=:dummy ';
where_clause := where_clause||'AND gs.organization_id = (SELECT organization_id'
|| ' FROM mtl_organizations WHERE organization_code = :orgn_code)';
where_clause := where_clause||'AND r.lab_organization_id = (SELECT organization_id'
|| ' FROM mtl_organizations WHERE organization_code = :lab_orgn_code)';
IF p_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_delete_mark := p_delete_mark ;
where_clause := where_clause||'AND r.delete_mark = :delete_mark ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_delete_mark ';
IF p_from_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_last_update_date := p_from_last_update_date;
where_clause := where_clause||'AND r.last_update_date >= :from_last_update_date ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_from_last_update_date ';
IF p_to_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_last_update_date := p_to_last_update_date;
where_clause := where_clause||'AND r.last_update_date <= :to_last_update_date ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_to_last_update_date ';
gme_debug.put_line('select from mtl_system_items_kfv using inventory_item_id of '|| g_results_table(i).test_kit_inv_item_id);
SELECT concatenated_segments
INTO g_results_table(i).test_kit_inv_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = g_results_table(i).test_kit_inv_item_id
and rownum = 1; -- 5346480 rework
gme_debug.put_line('select from fnd_user using user_id of '
|| g_results_table(i).tester_id);
SELECT user_name
INTO g_results_table(i).tester
FROM fnd_user
WHERE user_id = g_results_table(i).tester_id;
, p_from_last_update_date IN DATE DEFAULT NULL
, p_to_last_update_date IN DATE DEFAULT NULL
, p_delete_mark IN NUMBER DEFAULT NULL
, x_composite_results_table OUT NOCOPY system.gmd_composite_results_tab_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
sql_statement VARCHAR2(2000);
gme_debug.put_line('Begin constructing SELECT statement');
sql_statement := 'SELECT ';
||'gcr.delete_mark, gcr.text_code, gcr.attribute_category, gcr.attribute1, '
||'gcr.attribute2, gcr.attribute3, gcr.attribute4, gcr.attribute5, '
||'gcr.attribute6, gcr.attribute7, gcr.attribute8, gcr.attribute9, '
||'gcr.attribute10, gcr.attribute11, gcr.attribute12, gcr.attribute13, '
||'gcr.attribute14, gcr.attribute15, gcr.attribute16, gcr.attribute17, '
||'gcr.attribute18, gcr.attribute19, gcr.attribute20, gcr.attribute21, '
||'gcr.attribute22, gcr.attribute23, gcr.attribute24, gcr.attribute25, '
||'gcr.attribute26, gcr.attribute27, gcr.attribute28, gcr.attribute29, '
||'gcr.attribute30, gcr.creation_date, gcr.created_by, fu1.user_name, '
||'gcr.last_update_date, gcr.last_updated_by, fu2.user_name, '
||'gcr.last_update_login, gcr.PARENT_COMPOSITE_RESULT_ID) '; -- 5346713
||'AND fu1.user_id = gcr.created_by AND fu2.user_id = gcr.last_updated_by and 1=:dummy ';
||'gcr.delete_mark, gcr.text_code, gcr.attribute_category, gcr.attribute1, '
||'gcr.attribute2, gcr.attribute3, gcr.attribute4, gcr.attribute5, '
||'gcr.attribute6, gcr.attribute7, gcr.attribute8, gcr.attribute9, '
||'gcr.attribute10, gcr.attribute11, gcr.attribute12, gcr.attribute13, '
||'gcr.attribute14, gcr.attribute15, gcr.attribute16, gcr.attribute17, '
||'gcr.attribute18, gcr.attribute19, gcr.attribute20, gcr.attribute21, '
||'gcr.attribute22, gcr.attribute23, gcr.attribute24, gcr.attribute25, '
||'gcr.attribute26, gcr.attribute27, gcr.attribute28, gcr.attribute29, '
||'gcr.attribute30, gcr.creation_date, gcr.created_by, fu1.user_name, '
||'gcr.last_update_date, gcr.last_updated_by, fu2.user_name, '
||'gcr.last_update_login, gcr.PARENT_COMPOSITE_RESULT_ID) '; -- 5346713
||'AND fu1.user_id = gcr.created_by AND fu2.user_id = gcr.last_updated_by and 1=:dummy ';
||'gcr.delete_mark, gcr.text_code, gcr.attribute_category, gcr.attribute1, '
||'gcr.attribute2, gcr.attribute3, gcr.attribute4, gcr.attribute5, '
||'gcr.attribute6, gcr.attribute7, gcr.attribute8, gcr.attribute9, '
||'gcr.attribute10, gcr.attribute11, gcr.attribute12, gcr.attribute13, '
||'gcr.attribute14, gcr.attribute15, gcr.attribute16, gcr.attribute17, '
||'gcr.attribute18, gcr.attribute19, gcr.attribute20, gcr.attribute21, '
||'gcr.attribute22, gcr.attribute23, gcr.attribute24, gcr.attribute25, '
||'gcr.attribute26, gcr.attribute27, gcr.attribute28, gcr.attribute29, '
||'gcr.attribute30, gcr.creation_date, gcr.created_by, fu1.user_name, '
||'gcr.last_update_date, gcr.last_updated_by, fu2.user_name, '
||'gcr.last_update_login, gcr.PARENT_COMPOSITE_RESULT_ID) '; -- 5347613
||'AND fu1.user_id = gcr.created_by AND fu2.user_id = gcr.last_updated_by and 1=:dummy ';
||'gcr.delete_mark, gcr.text_code, gcr.attribute_category, gcr.attribute1, '
||'gcr.attribute2, gcr.attribute3, gcr.attribute4, gcr.attribute5, '
||'gcr.attribute6, gcr.attribute7, gcr.attribute8, gcr.attribute9, '
||'gcr.attribute10, gcr.attribute11, gcr.attribute12, gcr.attribute13, '
||'gcr.attribute14, gcr.attribute15, gcr.attribute16, gcr.attribute17, '
||'gcr.attribute18, gcr.attribute19, gcr.attribute20, gcr.attribute21, '
||'gcr.attribute22, gcr.attribute23, gcr.attribute24, gcr.attribute25, '
||'gcr.attribute26, gcr.attribute27, gcr.attribute28, gcr.attribute29, '
||'gcr.attribute30, gcr.creation_date, gcr.created_by, fu1.user_name, '
||'gcr.last_update_date, gcr.last_updated_by, fu2.user_name, '
||'gcr.last_update_login, gcr.PARENT_COMPOSITE_RESULT_ID) '; -- 5346713
||'AND fu1.user_id = gcr.created_by AND fu2.user_id = gcr.last_updated_by and 1=:dummy ';
where_clause := where_clause||'AND msi.inventory_item_id IN ( SELECT distinct inventory_item_id FROM mtl_system_items_kfv'
||' WHERE concatenated_segments >= :from_item_number'
||' AND organization_id = gse.organization_id)'; -- 5346713 rework added org id
where_clause := where_clause||'AND msi.inventory_item_id IN ( SELECT distinct inventory_item_id FROM mtl_system_items_kfv'
||' WHERE concatenated_segments <= :to_item_number'
||' AND organization_id = gse.organization_id)'; -- 5346713 rework added org id
IF p_from_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_last_update_date := p_from_last_update_date;
where_clause := where_clause||'AND gcr.last_update_date >= :from_last_update_date ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_from_last_update_date ';
IF p_to_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_last_update_date := p_to_last_update_date;
where_clause := where_clause||'AND gcr.last_update_date <= :to_last_update_date '; -- BUG 3078683
using_clause := using_clause||', gmd_outbound_apis_pub.g_to_last_update_date ';
IF p_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_delete_mark := p_delete_mark ;
where_clause := where_clause||'AND gcr.delete_mark = :delete_mark ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_delete_mark ';
gme_debug.put_line('select from mtl_system_items_kfv using '|| g_composite_results_table(i).inventory_item_id);
SELECT concatenated_segments INTO g_composite_results_table(i).item_number
FROM mtl_system_items_kfv msi
WHERE inventory_item_id = g_composite_results_table(i).inventory_item_id
and rownum = 1 ; -- 5346713 rework
gme_debug.put_line('select from mtl_lot_numbers using '|| g_composite_results_table(i).lot_number);
SELECT lot_number INTO g_composite_results_table(i).lot_number
FROM mtl_lot_numbers
WHERE lot_number = g_composite_results_table(i).lot_number;
, p_from_last_update_date IN DATE DEFAULT NULL
, p_to_last_update_date IN DATE DEFAULT NULL
, p_retain_as IN VARCHAR2 DEFAULT NULL
, p_delete_mark IN NUMBER DEFAULT NULL
, p_lpn IN VARCHAR2 DEFAULT NULL -- 7027149
, p_lpn_id IN NUMBER DEFAULT NULL-- 7027149
, x_samples_table OUT NOCOPY system.gmd_samples_tab_type -- 5335829
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- BUG 3078013 increase size of variables
sql_statement VARCHAR2(2000);
gme_debug.put_line('Begin constructing SELECT statement');
sql_statement := 'SELECT ';
||'gs.delete_mark, gs.text_code, gs.attribute_category, gs.attribute1, '
||'gs.attribute2, gs.attribute3, gs.attribute4, gs.attribute5, '
||'gs.attribute6, gs.attribute7, gs.attribute8, gs.attribute9, '
||'gs.attribute10, gs.attribute11, gs.attribute12, gs.attribute13, '
||'gs.attribute14, gs.attribute15, gs.attribute16, gs.attribute17, '
||'gs.attribute18, gs.attribute19, gs.attribute20, gs.attribute21, '
||'gs.attribute22, gs.attribute23, gs.attribute24, gs.attribute25, '
||'gs.attribute26, gs.attribute27, gs.attribute28, gs.attribute29, '
||'gs.attribute30, gs.creation_date, gs.created_by, fu1.user_name, '
||'gs.last_update_date, gs.last_updated_by, fu2.user_name, '
||'gs.last_update_login, gs.retain_as, gs.remaining_qty, gs.lpn_id, null) '; -- 7027149
||' fu1.user_id = gs.created_by AND fu2.user_id = gs.last_updated_by and 1=:dummy ';
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments BETWEEN :from_item_number AND :to_item_number'
||' AND organization_id = gs.organization_id)'; -- 5335829 rework - owner_organization_id is not a valid column name
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments >= :from_item_number'
||' AND organization_id = gs.organization_id)'; -- 5335829 rework - owner_organization_id is not a valid column name
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments <= :to_item_number'
||' AND organization_id = gs.organization_id)'; -- 5335829 rework - owner_organization_id is not a valid column name
IF p_from_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_last_update_date := p_from_last_update_date;
where_clause := where_clause||'AND gs.last_update_date >= :from_last_update_date ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_from_last_update_date ';
IF p_to_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_last_update_date := p_to_last_update_date;
where_clause := where_clause||'AND gs.last_update_date <= :to_last_update_date ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_to_last_update_date ';
IF p_delete_mark IS NOT NULL
THEN
gmd_outbound_apis_pub.g_delete_mark := p_delete_mark ;
where_clause := where_clause||'AND gs.delete_mark = :delete_mark ';
using_clause := using_clause||', gmd_outbound_apis_pub.g_delete_mark ';
gme_debug.put_line('select from gmd_specifications_b using spec_id of '
|| g_samples_table(i).spec_id);
select spec_name, spec_vers into g_samples_table(i).spec_name,g_samples_table(i).spec_vers
from gmd_specifications_b
where spec_id = g_samples_table(i).spec_id;
select concatenated_segments into g_samples_table(i).item_number
from mtl_system_items_b_kfv
where inventory_item_id = g_samples_table(i).inventory_item_id
and organization_id = g_samples_table(i).organization_id;
select plant_code, batch_no into g_samples_table(i).plant_code,g_samples_table(i).batch_no
from gme_batch_header
where batch_id = g_samples_table(i).batch_id;
select recipe_no, recipe_version into g_samples_table(i).recipe_no,
g_samples_table(i).recipe_version
from gmd_recipes_b
where recipe_id = g_samples_table(i).recipe_id;
select formula_no, formula_vers into g_samples_table(i).formula_no,
g_samples_table(i).formula_vers
from fm_form_mst
where formula_id = g_samples_table(i).formula_id;
select line_no, line_type into g_samples_table(i).formulaline_no,
g_samples_table(i).line_type
from fm_matl_dtl
where formulaline_id = g_samples_table(i).formulaline_id;
select routing_no, routing_vers into g_samples_table(i).routing_no,g_samples_table(i).routing_vers
from gmd_routings_b
where routing_id = g_samples_table(i).routing_id;
select oprn_no, oprn_vers into g_samples_table(i).oprn_no,g_samples_table(i).oprn_vers
from gmd_operations
where oprn_id = g_samples_table(i).oprn_id;
select hp.party_name into g_samples_table(i).cust_name
from hz_cust_accounts_all hca,hz_parties hp
where hca.cust_account_id = g_samples_table(i).cust_id
and hca.party_id = hp.party_id;
select ooh.order_number, ott.transaction_type_code into
g_samples_table(i).order_number, g_samples_table(i).order_type
from oe_order_headers_all ooh, oe_transaction_types_all ott
where ooh.header_id = g_samples_table(i).order_id and
ooh.order_type_id = ott.transaction_type_id ;
select line_number into g_samples_table(i).order_line_number
from oe_order_lines_all
where line_id = g_samples_table(i).order_line_id;
select name into g_samples_table(i).org_name
from hr_operating_units
where organization_id = g_samples_table(i).org_id;
select segment1 into g_samples_table(i).supplier_no
from po_vendors
where vendor_id = g_samples_table(i).supplier_id;
select user_name into g_samples_table(i).sampler
from fnd_user
where user_id = g_samples_table(i).sampler_id;
select segment1 into g_samples_table(i).po_number
from po_headers_all
where po_header_id = g_samples_table(i).po_header_id;
select vendor_site_code into g_samples_table(i).supplier_site
from po_vendor_sites_all
where vendor_site_id = g_samples_table(i).supplier_site_id;
select line_num into g_samples_table(i).po_line_number
from po_lines_all
where po_line_id = g_samples_table(i).po_line_id;
select receipt_num into g_samples_table(i).receipt_no
from rcv_shipment_headers
where shipment_header_id = g_samples_table(i).receipt_id;
select rsl.line_num into g_samples_table(i).receipt_line
from rcv_shipment_lines rsl
where rsl.shipment_line_id = g_samples_table(i).receipt_line_id;
SELECT location into g_samples_table(i).ship_to_location
FROM hz_cust_site_uses_all
WHERE site_use_id = g_samples_table(i).ship_to_site_id;
SELECT license_plate_number into g_samples_table(i).lpn
FROM wms_license_plate_numbers
WHERE lpn_id = g_samples_table(i).lpn_id;
, p_delete_mark IN NUMBER DEFAULT NULL
, p_from_last_update_date IN DATE DEFAULT NULL
, p_to_last_update_date IN DATE DEFAULT NULL
-- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
, p_sg_organization_id IN VARCHAR2 DEFAULT NULL
, p_resources IN VARCHAR2 DEFAULT NULL
, p_instance_id IN NUMBER DEFAULT NULL
, p_ss_id IN NUMBER DEFAULT NULL
, p_ss_organization_id IN VARCHAR2 DEFAULT NULL
, p_ss_no IN VARCHAR2 DEFAULT NULL
, p_variant_id IN NUMBER DEFAULT NULL
, p_variant_no IN NUMBER DEFAULT NULL
, p_time_point_id IN NUMBER DEFAULT NULL
-- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
, x_sample_groups_table OUT NOCOPY system.gmd_sampling_events_tab_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
sql_statement VARCHAR2(32000);
sql_statement := 'SELECT ';
/* SELECT gmd_sampling_event_rec_type
(
, CAST
( MULTISET
( gmd_event_spec_disp_rec_type
( SELECT
FROM
where ***
)
AS gmd_event_spec_disps_tab_type
)
)
)
FROM
WHERE
****
*/
-- Here goes.....
main_column_list :=' se.RECEIPT_ID, rh.RECEIPT_NUM, se.PO_HEADER_ID , ph.SEGMENT1'
||', se.SAMPLING_EVENT_ID, se.ORIGINAL_SPEC_VR_ID, se.DISPOSITION'
||', se.SAMPLE_REQ_CNT, se.SAMPLE_TAKEN_CNT, se.SAMPLING_PLAN_ID'
||', se.EVENT_TYPE_CODE, se.SAMPLING_EVENT_ID, se.inventory_item_id, im.concatenated_segments'
||', se.lot_number, se.parent_lot_number, se.subinventory, se.locator_id'
||', se.BATCH_ID, bh.BATCH_NO, se.RECIPE_ID, r.RECIPE_NO'
||', r.RECIPE_VERSION, se.FORMULA_ID, fh.FORMULA_NO, fh.FORMULA_VERS'
||', se.FORMULALINE_ID, fd.LINE_NO, se.ROUTING_ID, se.OPRN_ID'
||', se.CHARGE, se.CUST_ID, NULL, se.ORDER_ID, oh.ORDER_NUMBER'
||', se.ORDER_LINE_ID, ol.line_number, se.ORG_ID, mp.organization_code, se.SUPPLIER_ID'
||', NULL, se.PO_LINE_ID, pl.LINE_NUM, se.RECEIPT_LINE_ID'
||', NULL, se.SUPPLIER_LOT_NO, se.COMPLETE_IND'
||', se.SAMPLE_ID_TO_EVALUATE, se.COMPOSITE_ID_TO_EVALUATE, se.TEXT_CODE'
||', se.CREATION_DATE, se.CREATED_BY, fu1.USER_NAME, se.LAST_UPDATED_BY'
||', fu2.USER_NAME, se.LAST_UPDATE_DATE, se.LAST_UPDATE_LOGIN'
||', se.SUPPLIER_SITE_ID, NULL, se.SHIP_TO_SITE_ID, NULL'
||', se.STEP_ID, se.STEP_NO, se.LOT_RETEST_IND, se.RECOMPOSITE_IND'
||', se.SAMPLE_ACTIVE_CNT '
||', se.organization_id, se.resources, se.instance_id, se.time_point_id '
||', se.variant_id, se.archived_taken, se.reserved_taken ';
||' AND se.last_updated_by = fu2.user_id';
||',sd.DELETE_MARK, sd.CREATION_DATE, sd.CREATED_BY, fu3.USER_NAME'
||',sd.LAST_UPDATE_DATE, sd.LAST_UPDATED_BY, fu4.USER_NAME, sd.LAST_UPDATE_LOGIN';
||' AND sd.last_updated_by = fu4.user_id'
||' AND sd.sampling_event_id = se.sampling_event_id'
||' AND 1=:dummy ';
||' AND gs.inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b_kfv'
||' WHERE concatenated_segments BETWEEN :from_item_number AND :to_item_number'
||' AND organization_id = gs.owner_organization_id)';
' ( select site_use_id '||
' from hz_cust_site_uses_all '||
' where location = :customer_ship_to_location)';
IF p_delete_mark IS NOT NULL
THEN
g_delete_mark := p_delete_mark;
disp_where_clause := disp_where_clause||' AND sd.delete_mark = :delete_mark';
disp_using_clause := disp_using_clause||', gmd_outbound_apis_pub.g_delete_mark';
IF p_from_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_from_last_update_date := p_from_last_update_date;
main_where_clause := main_where_clause||'AND se.last_update_date >= :from_last_update_date ';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_from_last_update_date ';
disp_where_clause := disp_where_clause||'AND sd.last_update_date >= :from_last_update_date ';
disp_using_clause := disp_using_clause||', gmd_outbound_apis_pub.g_from_last_update_date ';
IF p_to_last_update_date IS NOT NULL
THEN
gmd_outbound_apis_pub.g_to_last_update_date := p_to_last_update_date;
main_where_clause := main_where_clause||'AND se.last_update_date <= :to_last_update_date ';
main_using_clause := main_using_clause||', gmd_outbound_apis_pub.g_to_last_update_date ';
disp_where_clause := disp_where_clause||'AND sd.last_update_date <= :to_last_update_date ';
disp_using_clause := disp_using_clause||', gmd_outbound_apis_pub.g_to_last_update_date ';
sql_statement := 'SELECT system.gmd_sampling_event_rec_type' -- 5284247
||'('||main_column_list
||', CAST'
||' ( MULTISET'
||' ( SELECT ' ||disp_column_list
||' FROM '||disp_table_list
||' WHERE '||disp_where_clause
||' ) AS system.gmd_event_spec_disps_tab_type' -- 5284242
||' )'
||')'
||' FROM ' ||main_table_list
||' WHERE '||main_where_clause;
select rsl.line_num into g_sample_groups_table(i).receipt_line_number
from rcv_shipment_lines rsl
where rsl.shipment_line_id = g_sample_groups_table(i).receipt_line_id;
select segment1 into g_sample_groups_table(i).supplier_name
from po_vendors
where vendor_id=g_sample_groups_table(i).supplier_id;
select location into g_sample_groups_table(i).ship_to_site_name
from hz_cust_site_uses_all
where site_use_id = g_sample_groups_table(i).ship_to_site_id;
select vendor_site_code into g_sample_groups_table(i).supplier_site_name
from po_vendor_sites_all
where vendor_site_id = g_sample_groups_table(i).supplier_site_id;