[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR select_claims_csr(p_transaction_header_id IN VARCHAR2)
IS
SELECT ocl.claim_number
FROM ozf_claims_all ocl,
dpp_transaction_claims_all dtcl
WHERE dtcl.transaction_header_id = p_transaction_header_id
AND dtcl.claim_type IN('SUPP_CUST_CL','SUPP_DSTR_CL','SUPP_DSTR_INC_CL','CUST_CL')
AND dtcl.claim_id = ocl.claim_id
AND ocl.status_code <> 'CLOSED';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_transaction_lines_all dtla
WHERE transaction_header_id = p_transaction_header_id
AND update_purchasing_docs IN ('Y','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='UPDTPO';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_transaction_lines_all dtla
WHERE transaction_header_id = p_transaction_header_id
AND update_inventory_costing IN ('Y','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='INVC';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_transaction_lines_all dtla
WHERE transaction_header_id = p_transaction_header_id
AND update_item_list_price IN ('Y','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='UPDTLP';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_transaction_lines_all dtla
WHERE transaction_header_id = p_transaction_header_id
AND supp_dist_claim_status IN ('Y','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='DSTRINVCL';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_customer_claims_all dcca
WHERE transaction_header_id = p_transaction_header_id
AND supplier_claim_created IN ('Y','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='CUSTINVCL';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'UPDTPO'
AND update_purchasing_docs IN ('N','P');
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='UPDTPO';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'NTFYPO'
AND notify_purchasing_docs IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='NTFYPO';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'INVC'
AND update_inventory_costing IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='INVC';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'UPDTLP'
AND update_item_list_price IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='UPDTLP';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'INPL'
AND notify_inbound_pricelist IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='INPL';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'OUTPL'
AND notify_outbound_pricelist IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='OUTPL';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'PROMO'
AND notify_promotions_pricelist IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='PROMO';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_transaction_lines_all dtla
WHERE dep.transaction_header_id = dtla.transaction_header_id
AND dtla.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'DSTRINVCL'
AND supp_dist_claim_status IN ('N','P','D') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='DSTRINVCL';
SELECT DISTINCT 'Y' INTO l_flag
FROM dpp_execution_processes dep, dpp_customer_claims_all dcca
WHERE dep.transaction_header_id = dcca.transaction_header_id
AND dcca.transaction_header_id = p_transaction_header_id
AND dep.process_code = 'CUSTINVCL'
AND supplier_claim_created IN ('N','P') ;
SELECT meaning
INTO l_process_code
FROM fnd_lookups
WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
AND lookup_code ='CUSTINVCL';
FOR select_claims_rec IN select_claims_csr(p_transaction_header_id)
LOOP
l_flag := 'Y';
fnd_message.set_token('CLAIM_NUMBER',select_claims_rec.claim_number );
SELECT
vendor_id,
segment1 vendor_num,
vendor_name
FROM
ap_suppliers pov
WHERE enabled_flag = 'Y'
AND hold_flag = 'N'
--AND pov.party_id = hzp.party_id
AND (l_trunc_sysdate >= NVL(TRUNC(start_date_active), l_trunc_sysdate) AND l_trunc_sysdate < NVL(TRUNC(end_date_active), l_trunc_sysdate + 1))
-- BETWEEN NVL(start_date_active, l_trunc_sysdate) AND NVL(end_date_active,l_trunc_sysdate)
AND ((UPPER(vendor_name) like UPPER(p_search_text) || '%' AND p_search_criteria = 'VENDOR_NAME')
OR (UPPER(segment1) like UPPER(p_search_text) || '%' AND p_search_criteria = 'VENDOR_NUMBER'))
ORDER BY vendor_name,segment1;
SELECT apssa.vendor_id,
apssa.vendor_site_id,
apssa.vendor_site_code,
apssa.address_line1,
apssa.address_line2,
apssa.address_line3,
apssa.city,
apssa.state,
apssa.zip,
apssa.country
FROM ap_supplier_sites_all apssa,
ozf_supp_trd_prfls_all ostp
WHERE apssa.vendor_id = to_NUMBER(p_search_text1)
AND ostp.supplier_id = apssa.vendor_id
AND ostp.supplier_site_id = apssa.vendor_site_id
AND ostp.org_id = apssa.org_id
AND p_search_criteria1 = 'VENDOR_ID'
AND nvl(apssa.rfq_only_site_flag, 'N') ='N'
AND NVL(apssa.inactive_date, TRUNC(SYSDATE +1)) > TRUNC(SYSDATE)
AND UPPER(apssa.vendor_site_code) like UPPER(p_search_text2) || '%'
AND p_search_criteria2 = 'VENDOR_SITE_CODE'
AND apssa.org_id = to_NUMBER(p_search_text3)
AND p_search_criteria3 = 'ORG_ID'
ORDER BY apssa.vendor_site_code;
SELECT assa.vendor_site_id,
poc.vendor_contact_id,
poc.first_name,
poc.middle_name,
poc.last_name,
poc.area_code
||poc.phone phone,
poc.email_address,
poc.fax
FROM po_vendor_contacts poc,
ap_supplier_sites_all assa
WHERE assa.vendor_site_id = TO_NUMBER(p_search_text1)
AND assa.party_site_id = poc.org_party_site_id
AND assa.vendor_site_id = poc.vendor_site_id
AND p_search_criteria1 = 'VENDOR_SITE_ID'
AND NVL(poc.inactive_date,SYSDATE + 1) > SYSDATE
AND ((UPPER(first_name) LIKE UPPER(p_search_text2)
AND p_search_criteria2 = 'FIRST_NAME')
OR (UPPER(last_name) LIKE UPPER(p_search_text2)
AND p_search_criteria2 = 'LAST_NAME')
OR (UPPER(middle_name) LIKE UPPER(p_search_text2)
AND p_search_criteria2 = 'MIDDLE_NAME'))
ORDER BY poc.last_name,poc.first_name,poc.middle_name;
SELECT msi.inventory_item_id,
msi.concatenated_segments,
msi.description,
occ.external_code
FROM mtl_system_items_kfv msi,
financials_system_params_all fspa,
ozf_supp_code_conversions_all occ
WHERE occ.internal_code = to_char(msi.inventory_item_id)
AND occ.code_conversion_type = 'OZF_PRODUCT_CODES'
AND occ.supp_trade_profile_id = p_supp_trade_profile_id
AND occ.org_id = fspa.org_id
AND TRUNC(SYSDATE) BETWEEN NVL(occ.start_date_active,TRUNC(SYSDATE)) AND
NVL(occ.end_date_active,TRUNC(SYSDATE))
AND msi.purchasing_item_flag = 'Y'
AND msi.shippable_item_flag = 'Y'
AND msi.enabled_flag = 'Y'
AND nvl(msi.consigned_flag,2) = 2 -- 2=unconsigned
AND msi.mtl_transactions_enabled_flag = 'Y'
AND msi.organization_id = fspa.inventory_organization_id
AND fspa.org_id = to_number(p_search_text1)
AND p_search_criteria1 = 'ORG_ID'
AND ((p_search_criteria2 = 'ITEM_NUMBER'
AND UPPER(msi.concatenated_segments) LIKE UPPER(p_search_text2) || '%')
OR (p_search_criteria2 = 'SUPPLIER_ITEM_NUMBER'
AND UPPER(occ.external_code) LIKE UPPER(p_search_text2) || '%'))
UNION
SELECT msi.inventory_item_id,
msi.concatenated_segments,
msi.description,
null external_code
FROM mtl_system_items_kfv msi,
financials_system_params_all fspa
WHERE msi.purchasing_item_flag = 'Y'
AND msi.shippable_item_flag = 'Y'
AND msi.enabled_flag = 'Y'
AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
AND msi.mtl_transactions_enabled_flag = 'Y'
AND msi.organization_id = fspa.inventory_organization_id
AND fspa.org_id = to_number(p_search_text1)
AND p_search_criteria1 = 'ORG_ID'
AND p_search_criteria2 = 'ITEM_NUMBER'
AND UPPER(msi.concatenated_segments) LIKE UPPER(p_search_text2) || '%'
AND NOT EXISTS (SELECT 1
FROM ozf_supp_code_conversions_all occ
WHERE occ.internal_code = to_char(msi.inventory_item_id)
AND occ.code_conversion_type = 'OZF_PRODUCT_CODES'
AND occ.supp_trade_profile_id = p_supp_trade_profile_id
AND occ.org_id = to_number(p_search_text1)
AND TRUNC(SYSDATE) BETWEEN NVL(occ.start_date_active,TRUNC(SYSDATE)) AND
NVL(occ.end_date_active,TRUNC(SYSDATE)))
ORDER BY 2,4;
SELECT DISTINCT msi.inventory_item_id,
msi.concatenated_segments,
msi.description,
NULL external_code
FROM mtl_system_items_kfv msi,
financials_system_params_all fspa
WHERE msi.organization_id = fspa.inventory_organization_id
AND msi.purchasing_item_flag = 'Y'
AND msi.shippable_item_flag = 'Y'
AND msi.enabled_flag = 'Y'
AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
AND msi.mtl_transactions_enabled_flag = 'Y'
AND fspa.org_id = TO_NUMBER(p_search_text1)
AND p_search_criteria1 = 'ORG_ID'
AND p_search_criteria2 = 'ITEM_NUMBER'
AND UPPER(msi.concatenated_segments) like UPPER(p_search_text2) || '%'
ORDER BY msi.concatenated_segments;
SELECT supp_trade_profile_id
INTO l_supp_trade_profile_id
FROM ozf_supp_trd_prfls_all
WHERE supplier_id = to_number(l_search_criteria_tbl(3).search_text)
AND supplier_site_id = to_number(l_search_criteria_tbl(4).search_text)
AND org_id = l_search_criteria_tbl(1).search_text;
SELECT count(*)
INTO l_supp_item_count
FROM ozf_supp_code_conversions_all
WHERE supp_trade_profile_id = l_supp_trade_profile_id;
SELECT count(*)
INTO l_supp_item_count
FROM ozf_supp_code_conversions_all
WHERE supp_trade_profile_id = l_supp_trade_profile_id;
-- select from msi and occ (get_item_csr)
OPEN get_item_csr(NVL(l_search_criteria_tbl(1).search_criteria,'ORG_ID'),
l_search_criteria_tbl(1).search_text,
NVL(l_search_criteria_tbl(2).search_criteria,'ITEM_NUMBER'),
l_search_criteria_tbl(2).search_text,
l_supp_trade_profile_id);
-- select only from msi (get_msi_item_csr)
OPEN get_msi_item_csr(NVL(l_search_criteria_tbl(1).search_criteria,'ORG_ID'),
l_search_criteria_tbl(1).search_text,
NVL(l_search_criteria_tbl(2).search_criteria,'ITEM_NUMBER'),
l_search_criteria_tbl(2).search_text);
SELECT DISTINCT
msi.inventory_item_id,
msi.concatenated_segments,
msi.description
FROM
mtl_system_items_kfv msi,
mtl_parameters mp,
financials_system_params_all fspa,
oe_order_lines_all oola,
dpp_transaction_lines_all dtla
WHERE
dtla.transaction_header_id = TO_NUMBER(p_search_text1) and
p_search_criteria1 = 'TRANSACTION_HEADER_ID' AND
oola.inventory_item_id = dtla.inventory_item_id and
(dtla.prior_price - NVL(dtla.supplier_new_price,0)) > 0 and
dtla.org_id = oola.org_id and
purchasing_item_flag = 'Y' AND
shippable_item_flag = 'Y' AND
msi.mtl_transactions_enabled_flag = 'Y' AND
msi.organization_id = mp.organization_id AND
mp.organization_id = fspa.inventory_organization_id AND
UPPER(segment1) like UPPER(p_search_text2) AND
p_search_criteria2 = 'ITEM_NUMBER' AND
oola.org_id = fspa.org_id AND
oola.inventory_item_id = msi.inventory_item_id AND
oola.sold_to_org_id = to_number(p_search_text3) AND
p_search_criteria3 = 'CUST_ACCOUNT_ID'
ORDER BY msi.concatenated_segments;
SELECT
msi.inventory_item_id,
msi.concatenated_segments,
msi.description
FROM
mtl_system_items_kfv msi,
mtl_parameters mp,
financials_system_params_all fspa,
dpp_transaction_lines_all dtla
WHERE
dtla.transaction_header_id = TO_NUMBER(p_search_text1) AND
p_search_criteria1 = 'TRANSACTION_HEADER_ID' AND
(dtla.prior_price - NVL(dtla.supplier_new_price,0)) > 0 AND
purchasing_item_flag = 'Y' AND
shippable_item_flag = 'Y' AND
msi.mtl_transactions_enabled_flag = 'Y' AND
msi.organization_id = mp.organization_id AND
mp.organization_id = fspa.inventory_organization_id AND
dtla.org_id = fspa.org_id AND
dtla.inventory_item_id = msi.inventory_item_id AND
UPPER(segment1) like UPPER(p_search_text2) || '%' AND
p_search_criteria2 = 'ITEM_NUMBER'
ORDER BY msi.concatenated_segments;
SELECT
ood.organization_id warehouse_id,
ood.organization_code warehouse_code,
ood.organization_name warehouse_name
FROM
org_organization_definitions ood
WHERE
operating_unit = to_number(p_search_text2)
AND p_search_criteria2 = 'ORG_ID'
AND ((ood.organization_code LIKE p_search_text1 || '%'
AND p_search_criteria1 = 'WAREHOUSE_CODE') OR
(ood.organization_name LIKE p_search_text1 || '%'
AND p_search_criteria1 = 'WAREHOUSE_NAME'))
AND NVL(ood.disable_date,SYSDATE+1) > SYSDATE
ORDER BY ood.organization_name;
SELECT
sum(case when ( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date)
and (NVL(moqd.orig_date_received,moqd.date_received) < p_effective_start_date))
--BETWEEN p_effective_start_date and p_effective_end_date)
then moqd.transaction_quantity else 0 end) covered_qty,
sum(moqd.transaction_quantity) onhand_qty,
moqd.transaction_uom_code
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
mp.organization_id = ood.organization_id AND
-- NVL(mp.consigned_flag,'N') = 'N' AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
ood.operating_unit = p_org_id AND
moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY moqd.transaction_uom_code;
SELECT
SUM(moqd.transaction_quantity) sum,
ood.organization_name warehouse,
ood.organization_id warehouse_id
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
ood.operating_unit = p_org_id AND
mp.organization_id = ood.organization_id AND
-- NVL(mp.consigned_flag,'N') = 'N' AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date)
AND (NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date ) )
--BETWEEN p_effective_start_date and p_effective_end_date
AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY ood.organization_name,
ood.organization_id;
SELECT
TRUNC(NVL(moqd.orig_date_received,moqd.date_received)) date_received,
SUM(moqd.transaction_quantity) sum
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
ood.operating_unit = p_org_id AND
mp.organization_id = ood.organization_id AND
-- NVL(mp.consigned_flag,'N') = 'N' AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
moqd.organization_id = p_warehouse_id AND
moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY TRUNC(NVL(moqd.orig_date_received,moqd.date_received));
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items msi,
mtl_parameters mp
WHERE inventory_item_id = l_covered_inv_tbl(i).inventory_item_id
AND mp.organization_id = msi.organization_id
AND mp.organization_id = mp.master_organization_id
AND rownum = 1;
SELECT
sum(case when ((trunc(NVL(moqd.orig_date_received,moqd.date_received)) >= trunc(p_effective_start_date))
AND (trunc(NVL(moqd.orig_date_received,moqd.date_received)) <= trunc(p_effective_end_date)))
--BETWEEN p_effective_start_date and p_effective_end_date)
then moqd.transaction_quantity else 0 end) covered_qty,
sum(moqd.transaction_quantity) onhand_qty,
moqd.transaction_uom_code
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
mp.organization_id = ood.organization_id AND
-- NVL(mp.consigned_flag,'N') = 'N' AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
ood.operating_unit = p_org_id AND
moqd.planning_organization_id = mp.organization_id
AND moqd.owning_organization_id = mp.organization_id
AND moqd.planning_tp_type = 2
AND moqd.owning_tp_type = 2
AND moqd.is_consigned = 2
GROUP BY moqd.transaction_uom_code;
SELECT
SUM(moqd.transaction_quantity) Covered_quantity,
ood.organization_name warehouse,
ood.organization_id warehouse_id
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
ood.operating_unit = p_org_id AND
mp.organization_id = ood.organization_id AND
-- NVL(mp.consigned_flag,'N') = 'N' AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
((trunc(NVL(moqd.orig_date_received, moqd.date_received)) >= trunc(p_effective_start_date))
AND (trunc(NVL(moqd.orig_date_received, moqd.date_received)) <= trunc(p_effective_end_date))) AND
--BETWEEN p_effective_start_date and p_effective_end_date AND
moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY ood.organization_name,
ood.organization_id;
SELECT
(NVL(moqd.orig_date_received,moqd.date_received)) date_received,
SUM(moqd.transaction_quantity) Onhand_quantity
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
ood.operating_unit = p_org_id AND
mp.organization_id = ood.organization_id AND
-- NVL(mp.consigned_flag,'N') = 'N' AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
moqd.organization_id = p_warehouse_id AND
moqd.planning_organization_id = mp.organization_id
AND moqd.owning_organization_id = mp.organization_id
AND moqd.planning_tp_type = 2
AND moqd.owning_tp_type = 2
AND moqd.is_consigned = 2
GROUP BY (NVL(moqd.orig_date_received,moqd.date_received));
DELETE
FROM DPP_INVENTORY_DETAILS_ADJ_ALL
WHERE INVENTORY_DETAILS_ID IN
(SELECT INVENTORY_DETAILS_ID
FROM DPP_INVENTORY_DETAILS_ALL
WHERE org_id = l_hdr_rec.org_id
AND transaction_line_id = l_inventorydetails_tbl(i).Transaction_Line_Id);
DELETE
FROM DPP_INVENTORY_DETAILS_ALL
WHERE org_id = l_hdr_rec.org_id
AND transaction_line_id = l_inventorydetails_tbl(i).Transaction_Line_Id;
SELECT DPP_INVENTORY_DETAILS_SEQ.nextval INTO l_inv_details_id FROM DUAL;
-- Insert new row
INSERT INTO DPP_INVENTORY_DETAILS_ALL(
inventory_details_id,
transaction_line_id,
quantity,
uom,
include_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
inventory_item_id,
org_id,
organization_id,
object_version_number)
VALUES ( l_inv_details_id,
l_inventorydetails_tbl(i).Transaction_Line_Id,
get_covered_inv_wh_rec.Covered_quantity,
l_inventorydetails_tbl(i).UOM_Code,
'N',
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
l_user_id,
l_inventorydetails_tbl(i).inventory_item_id,
l_hdr_rec.org_id,
get_covered_inv_wh_rec.Warehouse_id,
1
);
INSERT
INTO DPP_INVENTORY_DETAILS_ADJ_ALL(
inv_details_adj_id,
inventory_details_id,
date_received,
days_out,
quantity,
uom,
comments,
include_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id,
object_version_number)
VALUES(
dpp_inv_details_adj_id_seq.nextval,
l_inv_details_id,
get_covered_inv_rct_rec.date_received,
l_days_out,
get_covered_inv_rct_rec.Onhand_quantity,
l_inventorydetails_tbl(i).UOM_Code,
null,
l_include_flag,
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
l_user_id,
l_hdr_rec.org_id,
1
);
UPDATE DPP_INVENTORY_DETAILS_ALL
SET include_flag = 'Y',
object_version_number = object_version_number + 1,
last_update_date = l_sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id
WHERE inventory_details_id = l_inv_details_id;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items msi,
mtl_parameters mp
WHERE inventory_item_id = l_inventorydetails_tbl(i).inventory_item_id
AND mp.organization_id = msi.organization_id
AND mp.organization_id = mp.master_organization_id
AND rownum = 1;
SELECT osta.primary_uom_code uom,
sum(decode(osta.transfer_type, 'IN', nvl(osta.common_quantity,0), 0)) - sum(decode(osta.transfer_type, 'OUT', nvl(osta.common_quantity,0), 0)) as end_inventory
FROM
ozf_sales_transactions_all osta
,hz_parties hp
,hz_cust_accounts hca
WHERE osta.sold_to_party_id = hp.party_id
AND osta.error_flag='N'
AND osta.sold_to_party_id =hp.party_id
AND hca.cust_account_id = p_customer_id
AND hca.party_id = hp.party_id
AND osta.inventory_item_id =p_inventory_item_id--<>
AND ((osta.transaction_date >= p_effective_start_date) AND (osta.transaction_date < p_effective_end_date))
--between p_effective_start_date AND p_effective_end_date --<> and <> --//'DD-Mon-YYYY' fromat
GROUP BY
osta.primary_uom_code;
SELECT
oola.sold_to_org_id,
hz.account_number,
hp.party_name
FROM
oe_order_lines_all oola,
hz_cust_accounts hz,
hz_parties hp,
dpp_transaction_headers_all dtha,
dpp_transaction_lines_all dtla
WHERE
oola.org_id = dtla.org_id and
dtla.transaction_header_id = TO_NUMBER(p_search_text2) and
dtla.transaction_header_id = dtha.transaction_header_id and
p_search_criteria2 = 'TRANSACTION_HEADER_ID' and
oola.inventory_item_id = dtla.inventory_item_id and
(dtla.prior_price - NVL(dtla.supplier_new_price,0)) > 0 and
hz.cust_account_id = oola.sold_to_org_id and
hp.party_id = hz.party_id and
hz.status = 'A' and
(((actual_shipment_date >= (dtha.effective_start_date - dtha.days_covered))
AND (actual_shipment_date < dtha.effective_start_date))
OR (dtha.days_covered IS NULL AND actual_shipment_date < dtha.effective_start_date)) and
((UPPER(hp.party_name) like UPPER(p_search_text1) || '%' AND p_search_criteria1 = 'CUSTOMER_NAME')
OR (UPPER(hz.account_number) like UPPER(p_search_text1) || '%' AND p_search_criteria1 = 'CUSTOMER_NUMBER'))
GROUP BY oola.sold_to_org_id,
hz.account_number,
hp.party_name
ORDER BY hp.party_name desc, hz.account_number desc;
SELECT
hz.cust_account_id,
hz.account_number,
hp.party_name
FROM
hz_cust_accounts hz,
hz_parties hp
WHERE
hp.party_id = hz.party_id and
hz.status = 'A' and
((UPPER(hp.party_name) like UPPER(p_search_text1) || '%' AND p_search_criteria1 = 'CUSTOMER_NAME')
OR (UPPER(hz.account_number) like UPPER(p_search_text1) || '%' AND p_search_criteria1 = 'CUSTOMER_NUMBER'))
ORDER BY hp.party_name desc, hz.account_number desc;
SELECT
rctl.unit_selling_price last_price,
rct.invoice_currency_code
FROM
ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
ra_cust_trx_types_all rctt
WHERE
line_type = 'LINE' AND
inventory_item_id = p_inventory_item_id AND
uom_code = p_uom_code AND
rct.customer_trx_id = rctl.customer_trx_id AND
rct.org_id = p_org_id AND
rctt.cust_trx_type_id = rct.cust_trx_type_id AND
rct.org_id = rctt.org_id AND
rctt.name = 'Invoice' AND
rct.org_id = rctl.org_id AND
rct.sold_to_customer_id = p_customer_id AND
rct.complete_flag = 'Y' AND
rctl.customer_trx_line_id = (
SELECT
MAX(rctl1.customer_trx_line_id)
FROM
ra_customer_trx_lines_all rctl1,
ra_customer_trx_all rct1,
ra_cust_trx_types_all rctt1
WHERE
line_type = 'LINE' AND
inventory_item_id = p_inventory_item_id AND
uom_code = p_uom_code AND
rct1.customer_trx_id = rctl1.customer_trx_id AND
rct1.org_id = p_org_id AND
rctt1.cust_trx_type_id = rct1.cust_trx_type_id AND
rct1.org_id = rctt1.org_id AND
rctt1.name = 'Invoice' AND
rct1.org_id = rctl1.org_id AND
rct1.sold_to_customer_id = p_customer_id AND
rct1.complete_flag = 'Y');
SELECT
msi.LIST_PRICE_PER_UNIT list_price
FROM
mtl_system_items msi,
financials_system_params_all fspa
WHERE
msi.organization_id = fspa.inventory_organization_id and
fspa.org_id = p_organization_id and
msi.inventory_item_id = p_inventory_item_id;
SELECT inventory_organization_id
INTO l_inventory_organization_id
FROM financials_system_parameters;
SELECT gs.currency_code
INTO l_functional_currency
FROM gl_sets_of_books gs
, hr_operating_units hr
WHERE hr.set_of_books_id = gs.set_of_books_id
AND hr.organization_id = l_header_rec.org_id;
SELECT segment1 vendor_num,
vendor_name
FROM ap_suppliers pov
WHERE enabled_flag = 'Y'
AND hold_flag = 'N'
AND vendor_id = p_vendor_id;
SELECT
vendor_site_code,
address_line1,
address_line2,
address_line3,
city,
state,
zip,
country
FROM
ap_supplier_sites_all
WHERE
vendor_id = p_vendor_id AND
nvl(rfq_only_site_flag, 'N') ='N' AND
vendor_site_id = p_vendor_site_id;
SELECT poc.first_name ,
poc.middle_name ,
poc.last_name ,
poc.area_code||poc.phone phone,
poc.email_address ,
poc.fax
FROM ap_supplier_sites_all assa,
po_vendor_contacts poc
WHERE assa.vendor_site_id = p_vendor_site_id
AND assa.vendor_site_id = poc.vendor_site_id
AND assa.party_site_id = poc.org_party_site_id
AND poc.vendor_contact_id = p_vendor_contact_id;
SELECT
ood.organization_name warehouse
FROM
org_organization_definitions ood
WHERE
ood.organization_id = p_organization_id;
SELECT
hz.account_number,
hp.party_name
FROM
hz_cust_accounts hz,
hz_parties hp
WHERE
hp.party_id = hz.party_id and
hz.cust_account_id = p_customer_id;
SELECT concatenated_segments,
description
FROM mtl_system_items_kfv msi,
financials_system_params_all fspa
WHERE fspa.org_id = p_org_id and
fspa.inventory_organization_id = msi.organization_id and
msi.inventory_item_id = p_inventory_item_id;
INSERT INTO DPP_LOG_MESSAGES(LOG_ID,LOG_MESSAGE) VALUES(DPP_DEBUG_LOG_ID_SEQ.nextval, p_message_text);
select email_address
from fnd_user
where user_id = p_user_id;
SELECT gs.set_of_books_id
, gs.currency_code
FROM gl_sets_of_books gs
, hr_operating_units hr
WHERE hr.set_of_books_id = gs.set_of_books_id
AND hr.organization_id = p_org_id;