[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, P_Update_Yn IN VARCHAR2
, P_Header_ID IN NUMBER
, P_Line_ID IN NUMBER
, X_Return_Status OUT NOCOPY VARCHAR2
, X_Msg_Count OUT NOCOPY NUMBER
, X_Msg_Data OUT NOCOPY VARCHAR2
, X_Counter OUT NOCOPY NUMBER ) IS
l_api_version CONSTANT NUMBER := 1;
SELECT DECODE(Buy_Or_Sell, 'B', 'IN', 'OUT') Direction
, Inv_Organization_ID
FROM okc_k_headers_b
WHERE ID = P_Header_ID;
IF NVL(P_Update_Yn, 'N') = 'N' THEN /* New deliverable default */
IF P_Line_ID > 0 THEN /* Line Default */
Create_New_L (P_Initiate_Msg_List => G_False
, X_Return_Status => L_Return_Status
, X_Msg_Count => X_Msg_Count
, X_Msg_Data => X_Msg_Data
, P_Header_ID => P_Header_ID
, P_Line_ID => P_Line_ID
, P_Direction => L_Direction
, P_Inventory_Org_ID => L_Inventory_Org_ID
, X_Counter => X_Counter);
Update_Line( P_Init_Msg_List => G_False
, X_Return_Status => L_Return_Status
, X_Msg_Count => X_Msg_Count
, X_Msg_Data => X_Msg_Data
, P_Header_ID => P_Header_ID
, P_Line_ID => P_Line_ID
, P_Direction => L_Direction
, P_Inventory_Org_ID => L_Inventory_Org_ID
, X_Counter => X_Counter);
Update_Batch ( P_Init_Msg_List => G_False
, X_Return_Status => L_Return_Status
, X_Msg_Count => X_Msg_Count
, X_Msg_Data => X_Msg_Data
, P_Header_ID => P_Header_ID
, P_Direction => L_Direction
, P_Inventory_Org_ID => L_Inventory_Org_ID
, X_Counter => L_Counter);
select object1_id1, object1_id2, jtot_object1_code
from okc_k_party_roles_b
where dnz_chr_id = p_header_id and cle_id = p_id
and rle_code = p_code;
select object1_id1, object1_id2, jtot_object1_code
from okc_k_party_roles_b
where dnz_chr_id = p_header_id and chr_id = p_id
and rle_code = p_code;
select Max(a.level_sequence) from okc_ancestrys a
where a.cle_id = p_line_id
and exists(select 'x' from okc_k_party_roles_b b where dnz_chr_id = p_header_id and b.cle_id = a.cle_id_ascendant and b.rle_code = p_code and object1_id1 is not null);
select count(*) from okc_k_party_roles_b
where dnz_chr_id = p_header_id and chr_id = p_header_id
and rle_code = p_code
and object1_id1 is not null;
select buy_or_sell from okc_k_headers_b
where id = p_header_id;
select 'x' from okc_ancestrys
where cle_id = p_line_id;
Select 'x'
From HR_ALL_ORGANIZATION_UNITS hr, MTL_PARAMETERS mp
Where hr.Organization_Id = P_Id
And mp.Organization_Id = hr.Organization_Id;
select buy_or_sell into l_buy_or_sell
from okc_k_headers_b
where id = p_header_id;
SELECT COUNT(*) INTO l_row_count
FROM OKC_K_PARTY_ROLES_B
WHERE dnz_chr_id = p_header_id and cle_id = p_line_id
and rle_code = 'SHIP_FROM'
and object1_id1 is not null;
select cle_id_ascendant into l_id
from okc_ancestrys
where cle_id = p_line_id
and level_sequence = l_level;
select count(*) into l_row_count
from okc_k_party_roles_b
where dnz_chr_id = p_header_id and cle_id = l_id
and rle_code = 'SHIP_FROM';
select count(*) into l_row_count
from okc_k_party_roles_b
where dnz_chr_id = p_header_id and cle_id = p_line_id
and rle_code = 'SHIP_TO';
select cle_id_ascendant into l_id
from okc_ancestrys
where cle_id = p_line_id
and level_sequence = l_level;
select count(*) into l_row_count
from okc_k_party_roles_b
where dnz_chr_id = p_header_id and cle_id = l_id
and rle_code = 'SHIP_TO';
SELECT Count(*)
FROM oke_k_deliverables_b
WHERE K_Line_ID = P_Line_ID
AND NVL(Defaulted_Flag, 'N') = 'Y';
SELECT NVL(Create_Demand, 'N')
, NVL(Ready_To_Procure, 'N')
, NVL(Available_For_Ship_Flag, 'N')
, NVL(Ready_To_Bill, 'N')
, NVL(Completed_Flag, 'N')
FROM oke_k_deliverables_b
WHERE K_Line_ID = P_Line_ID
AND NVL(Defaulted_Flag, 'N') = 'Y';
ELSE /* Splited deliverable, not qualify for auto-update */
FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_SPLIT');
SELECT Cust_Account_ID, status
FROM oke_cust_site_uses_v
WHERE ID1 = P_ID;
SELECT Buy_Or_Sell
FROM OKC_K_HEADERS_B
WHERE ID = P_ID;
SELECT Vendor_ID, status
FROM oke_vendor_sites_v
WHERE ID1 = P_ID;
SELECT location_id
FROM hr_organization_units
WHERE ORGANIZATION_ID = p_ID;
SELECT ID1
FROM okx_locations_v
WHERE Organization_ID = P_ID AND status='A'
ORDER BY ID1;
SELECT 'x'
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = L_inventory_item_id
AND organization_id = L_inventory_org;
select l.k_line_id,
l.line_number,
l.project_id,
l.inventory_item_id,
SUBSTR(l.line_description, 1, 240) line_description,
l.delivery_date,
l.status_code,
l.start_date,
l.end_date,
k.priority_code,
h.currency_code,
DECODE(h.buy_or_sell, 'B', 'IN', 'OUT') Direction,
l.unit_price,
l.uom_code,
l.line_quantity,
k.country_of_origin_code,
l.subcontracted_flag,
l.billable_flag,
l.drop_shipped_flag,
-- l.completed_flag,
l.shippable_flag,
l.cfe_flag,
l.inspection_req_flag,
l.interim_rpt_req_flag,
l.customer_approval_req_flag,
l.as_of_date,
l.date_of_first_submission,
l.frequency,
l.data_item_subtitle,
l.copies_required,
l.cdrl_category,
l.data_item_name,
l.export_flag
from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
where h.id = l.header_id
and l.k_line_id = p_id
and h.id = k.k_header_id;
select l.k_line_id,
l.line_number,
l.project_id,
l.inventory_item_id,
substr(l.line_description, 1, 240) line_description,
l.delivery_date,
l.status_code,
l.start_date,
l.end_date,
k.priority_code,
h.currency_code,
DECODE(h.buy_or_sell, 'B', 'IN', 'OUT') Direction,
l.unit_price,
l.uom_code,
l.line_quantity,
k.country_of_origin_code,
l.subcontracted_flag,
l.billable_flag,
l.drop_shipped_flag,
l.completed_flag,
l.shippable_flag,
l.cfe_flag,
l.inspection_req_flag,
l.interim_rpt_req_flag,
l.customer_approval_req_flag,
l.as_of_date,
l.date_of_first_submission,
l.frequency,
l.data_item_subtitle,
l.copies_required,
l.cdrl_category,
l.data_item_name,
l.export_flag
from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
where h.id = p_id
and l.header_id = p_id
and h.id = k.k_header_id
and not exists (select 'x' from oke_k_deliverables_b where k_line_id = l.k_line_id and nvl(defaulted_flag, 'N') = 'Y')
and not exists (select 'x' from okc_k_lines_b s where s.cle_id = l.k_line_id)
and exists (select 'x' from okc_assents a
where a.opn_code = 'CREATE_DELV'
and a.sts_code = l.status_code
and a.scs_code = 'PROJECT'
and a.allowed_yn = 'Y');
PROCEDURE Update_Line( P_Init_Msg_List VARCHAR2
, X_Return_Status OUT NOCOPY VARCHAR2
, X_Msg_Count OUT NOCOPY NUMBER
, X_Msg_Data OUT NOCOPY VARCHAR2
, P_Header_ID IN NUMBER
, P_Line_ID IN NUMBER
, P_Direction IN VARCHAR2
, P_Inventory_Org_ID IN NUMBER
, X_Counter OUT NOCOPY NUMBER) IS
L_Api_Name CONSTANT VARCHAR2(30) := 'Update_Line';
CURSOR Update_L_C ( P_ID NUMBER ) IS
SELECT l.line_number,
l.project_id,
l.inventory_item_id,
SUBSTR(l.line_description, 1, 240) line_description,
l.delivery_date,
l.status_code,
l.start_date,
l.end_date,
k.priority_code,
h.currency_code,
DECODE(h.buy_or_sell, 'B', 'IN', 'OUT') Direction,
l.unit_price,
l.uom_code,
l.line_quantity,
k.country_of_origin_code,
l.subcontracted_flag,
l.billable_flag,
l.drop_shipped_flag,
-- l.completed_flag,
l.shippable_flag,
l.cfe_flag,
l.inspection_req_flag,
l.interim_rpt_req_flag,
l.customer_approval_req_flag,
l.as_of_date,
l.date_of_first_submission,
l.frequency,
l.data_item_subtitle,
l.copies_required,
l.cdrl_category,
l.data_item_name,
l.export_flag
from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
where l.k_line_id = p_id
and h.id = l.header_id
and k.k_header_id = l.header_id;
SELECT Deliverable_ID, Mps_Transaction_ID
FROM oke_k_deliverables_b
WHERE K_Line_ID = P_Line_ID
AND NVL(Defaulted_Flag, 'N') = 'Y';
Update_L_Rec Update_L_C%ROWTYPE;
OPEN Update_L_C(P_Line_ID);
FETCH Update_L_C INTO Update_L_Rec;
CLOSE Update_L_C;
IF Update_L_Rec.inventory_item_id is not null THEN -- need to check inventory
IF P_Direction = 'IN' THEN -- need to check ship to location
IF Check_Org_Items(update_l_rec.inventory_item_id,l_ship_to_org_id) = 'N' THEN
L_ship_to_org_id := NULL;
IF Check_Org_Items(update_l_rec.inventory_item_id,l_ship_from_org_id) = 'N' THEN
L_ship_from_org_id := NULL;
L_DEL_REC.project_id := UPDATE_L_REC.project_id;
L_DEL_REC.item_id := UPDATE_L_REC.inventory_item_id;
L_DEL_REC.description := UPDATE_L_REC.line_description;
L_DEL_REC.delivery_date := UPDATE_L_REC.delivery_date;
L_DEL_REC.status_code := UPDATE_L_REC.status_code;
L_DEL_REC.start_date := UPDATE_L_REC.start_date;
L_DEL_REC.end_date := UPDATE_L_REC.end_date;
L_DEL_REC.priority_code := UPDATE_L_REC.priority_code;
L_DEL_REC.currency_code := UPDATE_L_REC.currency_code;
L_DEL_REC.unit_price := UPDATE_L_REC.unit_price;
L_DEL_REC.uom_code := UPDATE_L_REC.uom_code;
L_DEL_REC.quantity := UPDATE_L_REC.line_quantity;
L_DEL_REC.country_of_origin_code := UPDATE_L_REC.country_of_origin_code;
L_DEL_REC.subcontracted_flag := UPDATE_L_REC.subcontracted_flag;
L_DEL_REC.billable_flag := UPDATE_L_REC.billable_flag;
L_DEL_REC.drop_shipped_flag := UPDATE_L_REC.drop_shipped_flag;
L_DEL_REC.shippable_flag := UPDATE_L_REC.shippable_flag;
L_DEL_REC.cfe_req_flag := UPDATE_L_REC.cfe_flag;
L_DEL_REC.inspection_req_flag := UPDATE_L_REC.inspection_req_flag;
L_DEL_REC.interim_rpt_req_flag := UPDATE_L_REC.interim_rpt_req_flag;
L_DEL_REC.customer_approval_req_flag := UPDATE_L_REC.customer_approval_req_flag;
L_DEL_REC.as_of_date := UPDATE_L_REC.as_of_date;
L_DEL_REC.date_of_first_submission := UPDATE_L_REC.date_of_first_submission;
L_DEL_REC.frequency := UPDATE_L_REC.frequency;
L_DEL_REC.data_item_subtitle := UPDATE_L_REC.data_item_subtitle;
L_DEL_REC.total_num_of_copies := UPDATE_L_REC.copies_required;
L_DEL_REC.cdrl_category := UPDATE_L_REC.cdrl_category;
L_DEL_REC.data_item_name := UPDATE_L_REC.data_item_name;
L_DEL_REC.export_flag := UPDATE_L_REC.export_flag;
-- Update deliverable
OKE_CONTRACT_PUB.update_deliverable(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_rec => l_del_rec,
x_del_rec => x_del_rec);
-- If planning is initiated, update MDS as well
/*
IF L_MDS_ID > 0 THEN
OKE_DTS_INTEGRATION_PKG.Create_MDS_Entry (
P_Deliverable_ID => L_DEL_REC.Deliverable_ID
, X_Out_ID => L_Out_ID
, X_Return_Status => L_Return_Status);
END Update_Line;
PROCEDURE Update_Batch ( P_Init_Msg_List VARCHAR2
, X_Return_Status OUT NOCOPY VARCHAR2
, X_Msg_Count OUT NOCOPY NUMBER
, X_Msg_Data OUT NOCOPY VARCHAR2
, P_Header_ID IN NUMBER
, P_Direction IN VARCHAR2
, P_Inventory_Org_ID IN NUMBER
, X_Counter OUT NOCOPY NUMBER) IS
L_Api_Name CONSTANT VARCHAR2(30) := 'Update_Batch';
CURSOR Update_C ( P_ID NUMBER ) IS
SELECT l.k_line_id, b.deliverable_ID, l.line_number
from oke_k_lines_v l, oke_k_deliverables_b b
where b.k_header_id = p_id
and b.k_line_id = l.k_line_id
and nvl(l.scheduled_delv_default, 'N') = 'Y'
and nvl(defaulted_flag, 'N') = 'Y'
GROUP BY l.k_line_id, b.deliverable_ID, l.line_number
HAVING count(*)=1;
FOR Update_Rec IN Update_C(P_Header_ID) LOOP
Verify_Defaults (
P_Line_ID => UPDATE_REC.K_Line_ID
, X_Msg_1 => L_Msg1
, X_Msg_2 => L_Msg2
, X_Msg_3 => L_Msg3
, X_Return_Status => L_Return_Status
, P_Calling_Level => 'H');
Update_Line(
P_Init_Msg_List => G_False
, X_Return_Status => L_Return_Status
, X_Msg_Count => X_Msg_Count
, X_Msg_Data => X_Msg_Data
, P_Header_ID => P_Header_ID
, P_Line_ID => Update_Rec.K_Line_ID
, P_Direction => P_Direction
, P_Inventory_Org_ID => P_Inventory_Org_ID
, X_Counter => X_Counter);
UPDATE oke_k_lines
SET Scheduled_Delv_Default = 'N'
WHERE K_Line_ID = Update_Rec.K_Line_ID;
IF Check_Mps_Valid ( Update_Rec.K_Line_ID, L_Mps_S, L_Mps_F ) THEN
OKE_DTS_ACTION_PKG.Initiate_Actions( P_Action => 'PLAN'
, P_Action_Level => 3
, P_Header_ID => P_Header_ID
, P_Line_ID => Update_Rec.K_Line_ID
, P_Deliverable_ID => Update_Rec.Deliverable_ID
, X_Return_Status => L_Return_Status
, X_Msg_Data => L_Msg_Data
, X_Msg_Count => L_Msg_Count );
UPDATE oke_k_deliverables_b
SET Create_Demand = 'N'
WHERE Deliverable_ID = Update_Rec.Deliverable_ID;
L_Token6_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token6_Value := L_Token6_Value || ', ' || UPDATE_REC.Line_Number;
L_Token7_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token7_Value := L_Token7_Value || ', ' || UPDATE_REC.Line_Number;
L_Token1_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token1_Value := L_Token1_Value || ', ' || UPDATE_REC.Line_Number;
L_Token2_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token2_Value := L_Token2_Value || ', ' || UPDATE_REC.Line_Number ;
L_Token3_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token3_Value := L_Token3_Value || ', ' || UPDATE_REC.Line_Number ;
L_Token4_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token4_Value := L_Token4_Value || ', ' || UPDATE_REC.Line_Number ;
L_Token5_Value := UPDATE_REC.Line_Number;
,p_new_token_value => UPDATE_REC.Line_Number) THEN
L_Token5_Value := L_Token5_Value || ', ' || UPDATE_REC.Line_Number ;
END Update_Batch;
SELECT Mps_Transaction_ID, Deliverable_ID, Create_Demand
FROM OKE_K_DELIVERABLES_B
WHERE K_Line_ID = P_Line_ID
AND NVL(Defaulted_Flag, 'N') = 'Y'
AND NVL(Create_Demand, 'N') = 'Y';
SELECT DECODE(H.Buy_Or_Sell, 'B', 'IN', 'OUT')
, L.Header_ID
, L.Inventory_Item_ID
, L.Line_Quantity
, L.Line_Number
FROM okc_k_headers_b H, oke_k_lines_v L
WHERE H.ID = L.Header_ID
AND L.K_Line_ID = P_Line_ID;
SELECT 'X'
FROM oke_system_items_v
WHERE ID1 = L_Item_ID
AND ID2 = L_Inventory_Org_ID;