[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(po_distribution_id)
INTO X_num_of_distributions
FROM po_distributions
WHERE line_location_id = X_line_location_id;
SELECT Nvl(asn_line_flag,'N'),
po_distribution_id
INTO l_asn_line_flag,
l_po_distribution_id
FROM rcv_shipment_lines
WHERE shipment_line_id = X_SHIPMENT_LINE_ID ;
SELECT 'Y' into l_clm_document
FROM po_headers_all h,
po_doc_style_headers s
WHERE h.po_header_id = ( SELECT MIN(PO_HEADER_ID)
FROM po_distributions
WHERE line_location_id = X_line_location_id
)
AND h.style_id = s.style_id
AND Nvl(s.clm_flag,'N') = 'Y';
is added. Since only location_code is selected, there is no need of
joining with the tables hr_locations_all and hz_locations. */
SELECT pod.po_distribution_id,
pod.destination_type_code,
pod.deliver_to_location_id,
hl.location_code,
pod.deliver_to_person_id,
pod.destination_subinventory,
pod.wip_entity_id,
pod.WIP_REPETITIVE_SCHEDULE_ID,
pod.WIP_LINE_ID,
pod.WIP_OPERATION_SEQ_NUM,
pod.WIP_RESOURCE_SEQ_NUM,
pod.BOM_RESOURCE_ID,
pod.destination_organization_id,
round(pod.rate,28),
pod.rate_date,
mkc.kanban_card_number, --
pod.project_id, -- bug 3867151
pod.task_id, -- bug 3867151
pod.code_combination_id --
INTO X_po_distributions_id,
X_destination_type_code,
X_deliver_to_location_id,
X_deliver_to_location,
X_deliver_to_person_id,
X_deliver_to_sub,
X_wip_entity_id,
X_wip_repetitive_schedule_id,
X_wip_line_id,
X_wip_operation_seq_num,
X_wip_resource_seq_num,
X_bom_resource_id,
X_to_organization_id,
X_rate,
X_rate_date,
x_kanban_card_number, --
x_project_id, -- Bug 4684017 The variable should be x_project_id and not x_project_number
x_task_id, -- Bug 4684017 The variable should be x_project_id and not x_project_number
l_code_combination_id --
FROM po_distributions pod,
hr_locations_all_tl hl,
mtl_kanban_cards mkc --
WHERE pod.line_location_id = X_line_location_id
AND pod.po_distribution_id = Nvl(X_PO_DISTRIBUTIONS_ID,pod.po_distribution_id) -- bug 9742420
AND hl.location_id(+) = pod.deliver_to_location_id
AND hl.language(+) = USERENV('LANG')
AND pod.kanban_card_id = mkc.kanban_card_id (+); --
task_id related conditions in the above sql, writing a separate select
to retrieve the project and task numbers. This sql will be executed
only when project/task references are there in the PO distribution.
*/
x_progress := 21;
SELECT ppa.project_number,
pte.task_number
INTO x_project_number,
x_task_number
FROM pjm_projects_all_v ppa,
pa_tasks_expend_v pte
WHERE ppa.project_id = x_project_id
AND pte.task_id = x_task_id
AND ppa.project_id = pte.project_id;*/
select P.SEGMENT1 PROJECT_NUMBER ,
T.TASK_NUMBER
into x_project_number,
x_task_number
from PA_PROJECTS_ALL p,
PA_TASKS T
where P.PROJECT_ID = T.PROJECT_ID
and p.project_id = x_project_id
and T.task_id = x_task_id;
select SEGMENT1 PROJECT_NUMBER
into x_project_number
from PA_PROJECTS_ALL
where project_id = x_project_id;
select PROJECT_NUMBER
into x_project_number
from PJM_SEIBAN_NUMBERS
where project_id = x_project_id;
SELECT displayed_field
INTO X_destination_type_dsp
FROM po_lookup_codes
WHERE lookup_type = l_lookup_type
AND lookup_code = X_destination_type_code;
SELECT COUNT(po_distribution_id),
COUNT(kanban_card_id),
COUNT(project_id),
COUNT(task_id),
COUNT(code_combination_id),
COUNT(deliver_to_person_id),
COUNT(wip_entity_id),
COUNT(rate),
COUNT(rate_date),
COUNT(destination_subinventory)
INTO l_num_distributions,
l_num_kanban_cards,
l_num_projs,
l_num_tasks,
l_num_charge_accts,
l_num_requestors,
l_num_jobs,
l_num_rates,
l_num_rate_dates,
l_num_dest_subinv
FROM po_distributions
WHERE line_location_id = p_line_location_id;
SELECT MKC.kanban_card_number,
--PPA.project_number,
--PTE.task_number,
POD.project_id,
POD.task_id,
PO_COMPARE_REVISIONS.get_charge_account(POD.code_combination_id),
PO_INQ_SV.get_person_name(POD.deliver_to_person_id),
POD.destination_subinventory,
POD.wip_entity_id,
POD.wip_repetitive_schedule_id,
POD.wip_line_id,
POD.wip_operation_seq_num,
POD.wip_resource_seq_num,
POD.destination_organization_id,
ROUND(POD.rate, 28),
POD.rate_date
INTO x_kanban_card_number,
--x_project_number,
--x_task_number,
l_project_id,
l_task_id,
x_charge_account,
x_deliver_to_person,
x_dest_subinv,
l_wip_entity_id,
l_wip_rep_schedule_id,
l_wip_line_id,
l_wip_operation_seq_num,
l_wip_resource_seq_num,
l_to_organization_id,
x_rate,
x_rate_date
FROM po_distributions_all POD, -- Bug 10203707
--pjm_projects_all_v PPA,
--pa_tasks_expend_v PTE,
mtl_kanban_cards MKC
WHERE POD.po_distribution_id = NVL(p_po_distribution_id,
POD.po_distribution_id)
AND POD.line_location_id = p_line_location_id
--AND POD.project_id = PPA.project_id (+)
--AND POD.task_id = PTE.task_id (+)
AND POD.kanban_card_id = MKC.kanban_card_id (+);
select P.SEGMENT1 PROJECT_NUMBER ,
T.TASK_NUMBER
into x_project_number,
x_task_number
from PA_PROJECTS_ALL p,
PA_TASKS T
where P.PROJECT_ID = T.PROJECT_ID
and p.project_id = l_project_id
and T.task_id = l_task_id;
select SEGMENT1 PROJECT_NUMBER
into x_project_number
from PA_PROJECTS_ALL
where project_id = l_project_id;
select PROJECT_NUMBER
into x_project_number
from PJM_SEIBAN_NUMBERS
where project_id = l_project_id;
SELECT MKC.kanban_card_number,
PO_INQ_SV.get_person_name(PRL.to_person_id),
PRL.destination_subinventory,
PRD.project_id, -- bug 3867151
PRD.task_id, -- bug 3867151
PO_COMPARE_REVISIONS.get_charge_account(PRD.code_combination_id)
INTO x_kanban_card_number,
x_deliver_to_person,
x_dest_subinv,
x_project_id, -- bug 3867151
x_task_id, -- bug 3867151
x_charge_account
FROM po_requisition_lines PRL,
po_req_distributions PRD,
mtl_kanban_cards MKC
WHERE PRL.requisition_line_id = p_requisition_line_id
AND PRL.requisition_line_id = PRD.requisition_line_id
AND PRD.distribution_id = NVL(p_req_distribution_id,PRD.distribution_id)
AND PRL.kanban_card_id = MKC.kanban_card_id (+);
task_id related conditions in the above sql, writing a separate select
to retrieve the project and task numbers. This sql will be executed
only when project/task references are there in the PO distribution.
*/
IF (x_project_id IS NOT NULL) THEN
BEGIN
/*Bugfix 5217513: SQLID 17869671 Rewritten queries.
SELECT ppa.project_number,
pte.task_number
INTO x_project_number,
x_task_number
FROM pjm_projects_all_v ppa,
pa_tasks_expend_v pte
WHERE ppa.project_id = x_project_id
AND pte.task_id = x_task_id
AND ppa.project_id = pte.project_id;*/
select P.SEGMENT1 PROJECT_NUMBER ,
T.TASK_NUMBER
into x_project_number,
x_task_number
from PA_PROJECTS_ALL p,
PA_TASKS T
where P.PROJECT_ID = T.PROJECT_ID
and p.project_id = x_project_id
and T.task_id = x_task_id;
select SEGMENT1 PROJECT_NUMBER
into x_project_number
from PA_PROJECTS_ALL
where project_id = x_project_id;
select PROJECT_NUMBER
into x_project_number
from PJM_SEIBAN_NUMBERS
where project_id = x_project_id;