[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT invoice_date, interface_date, date_start, date_end
FROM
(SELECT b.date_transaction invoice_date,
b.date_to_interface interface_date,
date_start, date_end
FROM mtl_agreement_details_v a, oks_level_elements b
WHERE a.cle_id = b.cle_id
AND a.dnz_chr_id = b.dnz_chr_id
AND b.date_completed IS NOT NULL
AND a.dnz_chr_id = p_contract_id
ORDER BY b.id DESC) invoice_interface_det
WHERE ROWNUM <2;
SELECT /*+ parallel(MMT) */ MMT.locator_id
, MMT.organization_id
, WMS_DEPLOY.GET_CLIENT_CODE( inventory_item_id ) CLIENT_CODE
, MMT.transaction_action_id
, MMT.primary_quantity
, MMT.creation_date
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_3PL_LOCATOR_OCCUPANCY MLC
WHERE MMT.organization_id = MLC.organization_id
AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = nvl(p_client_code, MLC.client_code)
AND MMT.locator_id = MLC.locator_id
AND MMT.creation_date between MLC.last_invoiced_date and p_end_date
AND MMT.transaction_action_id not in (5,6,24,30,26,7,11,17,10,9,13,14,56,57)
AND EXISTS (SELECT 1 FROM mtl_parameters mp
WHERE wms_enabled_flag = 'Y'
AND mp.organization_id = mmt.organization_id)
ORDER BY MMT.inventory_item_id, MMT.locator_id, MMT.creation_date;
SELECT /*+ parallel(MMT) */ MMT.locator_id
, MMT.organization_id
, WMS_DEPLOY.GET_CLIENT_CODE( inventory_item_id ) CLIENT_CODE
, MMT.transaction_action_id
, MMT.primary_quantity
, MMT.creation_date
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE
WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM MTL_3PL_LOCATOR_OCCUPANCY MLC
WHERE MMT.organization_id = MLC.organization_id
AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = MLC.client_code
AND MMT.locator_id = MLC.locator_id)
AND MMT.creation_date BETWEEN p_start_date AND p_end_date
AND MMT.transaction_action_id not in (5,6,24,30,26,7,11,17,10,9,13,14,56,57)
AND EXISTS (SELECT 1 FROM mtl_parameters mp
WHERE wms_enabled_flag = 'Y'
AND mp.organization_id = mmt.organization_id)
ORDER BY MMT.inventory_item_id, MMT.locator_id, MMT.creation_date;
SELECT /*+ parallel(MMT) */ MMT.locator_id
, MMT.organization_id
, WMS_DEPLOY.GET_CLIENT_CODE( inventory_item_id ) CLIENT_CODE
, MMT.transaction_action_id
, MMT.primary_quantity
, MMT.creation_date
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE
WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) IS NOT NULL
AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = p_client_code
AND NOT EXISTS (SELECT 1
FROM MTL_3PL_LOCATOR_OCCUPANCY MLC
WHERE MMT.organization_id = MLC.organization_id
AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = p_client_code
AND MMT.locator_id = MLC.locator_id)
AND MMT.creation_date BETWEEN p_start_date AND p_end_date
AND MMT.transaction_action_id not in (5,6,24,30,26,7,11,17,10,9,13,14,56,57)
AND EXISTS (SELECT 1 FROM mtl_parameters mp
WHERE wms_enabled_flag = 'Y'
AND mp.organization_id = mmt.organization_id)
ORDER BY MMT.inventory_item_id, MMT.locator_id, MMT.creation_date;
'SELECT count(*) cnt '
|| 'FROM mtl_billing_rule_lines rule_lines, '
|| 'mtl_billing_rule_headers_b rule_headers, '
|| 'okc_k_headers_all_b contract_headers, '
|| 'mtl_client_parameters mcp '
|| 'WHERE rule_headers.billing_rule_header_id = rule_lines.billing_rule_header_id '
|| 'AND contract_headers.authoring_org_id = :OU_Id '
|| 'AND rule_headers.service_agreement_id = contract_headers.id '
|| 'AND mcp.client_code = rule_lines.client_code ';
|| 'SELECT 1 '
|| 'FROM mfg_lookups lookup, mtl_billing_sources_b blsrc '
|| 'WHERE blsrc.billing_source_code = ''S'' '
|| 'AND rule_lines.billing_source_id = blsrc.billing_source_id '
|| 'AND lookup.lookup_type = ''MTL_3PL_SEEDED_SOURCE'' '
|| 'AND lookup.lookup_code IN (7, 8) '
|| 'AND blsrc.procedure_code = lookup.lookup_code)';
SELECT client_code
INTO l_client_code
FROM mtl_client_parameters
WHERE client_id = p_client_id;
SELECT upgrade_date
INTO l_upgrade_date
FROM mtl_3pl_locator_occupancy
WHERE upgrade_date IS NOT NULL
AND ROWNUM <2;
SELECT creation_date
INTO l_profile_creation_date
FROM fnd_profile_options
WHERE profile_option_name = 'WMS_DEPLOYMENT_MODE'
AND application_id = 385;
debug('Going to insert data in locator table ');
debug('In Select sel_eligible_rec for date, code -> '||l_src_to_date||', '||l_client_code);
inv_3pl_loc_pvt.update_locator_capacity(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_inventory_location_id => sel_eligible_rec.locator_id
, p_organization_id => sel_eligible_rec.organization_id
, p_client_code => sel_eligible_rec.client_code
, p_transaction_action_id => sel_eligible_rec.transaction_action_id
, p_quantity => sel_eligible_rec.primary_quantity
, p_transaction_date => sel_eligible_rec.creation_date
);
debug('Error from update_locator_capacity - '||x_msg_data);
debug(l_progress ||' : Got error while selecting eligible transactions');
inv_3pl_loc_pvt.update_locator_capacity(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_inventory_location_id => sel_new_rec.locator_id
, p_organization_id => sel_new_rec.organization_id
, p_client_code => sel_new_rec.client_code
, p_transaction_action_id => sel_new_rec.transaction_action_id
, p_quantity => sel_new_rec.primary_quantity
, p_transaction_date => sel_new_rec.creation_date
);
debug('Error from update_locator_capacity - '||x_msg_data);
debug(l_progress ||' : Got error while selecting new transactions');
inv_3pl_loc_pvt.update_locator_capacity(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_inventory_location_id => sel_new_rec.locator_id
, p_organization_id => sel_new_rec.organization_id
, p_client_code => sel_new_rec.client_code
, p_transaction_action_id => sel_new_rec.transaction_action_id
, p_quantity => sel_new_rec.primary_quantity
, p_transaction_date => sel_new_rec.creation_date
);
debug('Error from update_locator_capacity - '||x_msg_data);
debug(l_progress ||' : Got error while selecting new transactions for Ct.');
'SELECT client_id, client_code, client_name, client_number, '
|| 'contract_id, contract_number, counter_item_id, '
|| 'last_computation_Date, net_Reading, last_reading, '
|| 'billing_uom, service_item_org_id, billing_source_id, '
|| 'billing_source_name, service_line_start_date, '
|| 'service_line_end_date '
|| 'FROM mtl_billing_rule_lines_v rules '
|| 'WHERE authoring_org_id IN ( SELECT organization_id '
|| 'FROM hr_operating_units hr '
|| 'WHERE '
|| 'mo_global.check_access(hr.organization_id)=''Y'') '
|| 'AND EXISTS (SELECT 1 '
|| 'FROM mtl_service_contracts_v active_contracts '
|| 'WHERE active_contracts.id = rules.contract_id) '
|| 'AND :OU_id IN ( SELECT organization_id '
|| 'FROM hr_operating_units hr '
|| 'WHERE '
|| 'mo_global.check_access(hr.organization_id) = ''Y'') ';
/* Derive Client code, last invoice date, last interface date, last computation date, last updated counter value, last billed counter value, source to date, Billing UOM */
l_progress := 10;
debug( 'l_src_to_date to be updated in PUB pl/sql-> '|| l_src_to_date);
SELECT meaning
INTO l_meaning
FROM mtl_billing_sources_b blsrc, mfg_lookups lookup
WHERE billing_source_id = v_billing_source_id
AND
( ( decode(blsrc.billing_source_code, 'C', lookup.lookup_type, NULL) = 'MTL_3PL_CUSTOM_SOURCE')
OR ( decode(blsrc.billing_source_code, 'S', lookup.lookup_type, NULL) = 'MTL_3PL_SEEDED_SOURCE')
)
AND blsrc.procedure_code = lookup.lookup_code
AND lookup.lookup_type IN ('MTL_3PL_CUSTOM_SOURCE', 'MTL_3PL_SEEDED_SOURCE');
debug(l_progress ||' : Get counter value to be updated for billing from custom procedure ');
debug(l_progress ||' : Now call IB api to update counter ');
SELECT csi_transactions_s.NEXTVAL
INTO l_transaction_id
FROM dual;
inv_3pl_billing_counter_pvt.inv_insert_readings_using_api(
p_counter_id => l_counter_item_id,
p_count_date => l_src_to_date , p_new_reading=> l_custom_reading ,
p_net_reading => v_net_Reading,
p_transaction_id=> l_transaction_id);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Could not update counter reading for the following combination ');