[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKE_API.G_FORM_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKE_API.G_RECORD_LOGICALLY_DELETED;
SELECT 'X'
FROM okc_k_headers_b
WHERE ID = P_ID;
SELECT 'X'
FROM okc_k_lines_b
WHERE ID = P_ID;
SELECT 'X'
FROM oke_k_deliverables_b
WHERE Deliverable_ID = P_DEL_ID
AND K_Line_ID = P_LINE_ID
AND Nvl(ready_to_bill,'N')='Y' ;
SELECT Project_ID
FROM oke_k_headers
WHERE K_Header_Id = P_Header_Id;
SELECT PROJECT_ID, TASK_ID, PARENT_LINE_ID
FROM oke_k_lines
WHERE K_Line_Id = L_Line_Id;
SELECT PROJECT_ID, TASK_ID
FROM OKE_K_LINES
WHERE K_LINE_ID = (SELECT CLE_ID_ASCENDANT FROM OKC_ANCESTRYS WHERE CLE_ID = P_LINE_ID AND LEVEL_SEQUENCE = P_SEQUENCE)
AND PROJECT_ID IS NOT NULL;
SELECT 'X'
FROM DUAL
WHERE P_ID IN (
SELECT P.Project_ID
FROM pa_projects_all p
WHERE p.Project_ID IN (SELECT To_Number(sub_project_id)
FROM pa_fin_structures_links_v
START WITH parent_project_id = P_ID1 AND (parent_task_id IN (SELECT Task_ID FROM pa_tasks WHERE Top_Task_ID = P_ID2) or P_ID2 IS NULL)
CONNECT BY parent_project_id = PRIOR sub_project_id)
UNION
SELECT Project_ID
FROM pa_projects_all
WHERE Project_ID = P_ID1);
SELECT MAX(LEVEL_SEQUENCE)
FROM OKC_ANCESTRYS
WHERE CLE_ID = P_ID;
SELECT 'X'
FROM pa_tasks
WHERE Task_ID = P_ID1
AND Project_ID = P_ID2;
SELECT 'X'
from PA_EVENT_types WHERE EVENT_TYPE_CLASSIFICATION='MANUAL' and
sysdate between start_date_active and nvl(end_date_active,sysdate+1)
AND event_type=p_event_type;
SELECT 'X' from mtl_item_flexfields where organization_id= p_inv_org
and invoiceable_item_flag='Y' AND inventory_item_id= p_id;
SELECT Inv_Organization_ID
FROM okc_k_headers_b WHERE id= p_k_billing_event_rec.k_header_id;
SELECT 'X' FROM oke_chg_requests_v WHERE k_header_id=P_HEADER_ID
AND chg_status_type_code='COMPLETED' AND chg_request_id=p_id ;
SELECT 'X' from pa_all_organizations paorg,hr_organization_units hrorg
WHERE paorg.organization_id=hrorg.organization_id and nvl(paorg.org_id,-99)=nvl(p_bill_ou,-99)
and sysdate between hrorg.date_from and nvl(hrorg.date_to,sysdate+1) AND hrorg.organization_id=p_id ;
SELECT org_id
FROM pa_projects_all
WHERE project_id = p_k_billing_event_rec.bill_project_id;
select 'X'
from oke_k_fund_allocations where object_id=p_k_billing_event_rec.k_header_id
and (k_line_id is null or k_line_id= p_k_billing_event_rec.bill_line_id)
and nvl(project_id,-1)=nvl(p_k_billing_event_rec.bill_project_id,-1)
and (task_id is null or task_id = p_k_billing_event_rec.bill_task_id )
AND reference1=Nvl(p_k_billing_event_rec.BILL_FUND_REF1,reference1)
AND reference2=Nvl(p_k_billing_event_rec.BILL_FUND_REF2,reference2)
AND reference3=Nvl(p_k_billing_event_rec.BILL_FUND_REF3 ,reference3);
SELECT multi_currency_billing_flag
FROM pa_projects_all
WHERE project_id = C_Project_Id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = C_header_Id;
SELECT 'X'
FROM gl_daily_conversion_types
WHERE conversion_type =p_BILL_RATE_TYPE;
SELECT 'X' FROM FND_CURRENCIES_VL WHERE CURRENCY_FLAG = 'Y' AND CURRENCY_CODE= p_cur_code;
Select Unit_Price
From oke_k_deliverables_vl
Where Deliverable_Id = p_k_billing_event_rec.deliverable_id;
SELECT currency_code INTO l_bill_currency
FROM okc_k_headers_b
WHERE id = p_k_billing_event_rec.k_header_id;
Select Project_Id, K_Line_Id, Inventory_Org_id, Item_Id, Currency_Code, Quantity, Unit_Price, Description
From oke_k_deliverables_vl
Where Deliverable_Id = P_Deliverable_Id;
SELECT segment1
, org_id
, carrying_out_organization_id
, project_level_funding_flag
FROM pa_projects_all
WHERE project_id = C_Project_ID;
SELECT item_number
, description
FROM mtl_item_flexfields
WHERE organization_id = C_Org_ID
AND inventory_item_id = C_Item_ID;
Select Nvl(Sum( Bill_Quantity ), 0)
From oke_k_billing_events
Where Deliverable_Id = P_Deliverable_Id;
SELECT di.sequence_number
FROM wsh_document_instances di
, wsh_delivery_legs dl
, wsh_delivery_details dd
, wsh_delivery_assignments da
WHERE dd.source_code = 'OKE'
AND dd.source_line_id = C_Deliverable_ID
AND da.delivery_detail_id = dd.delivery_detail_id
AND dl.delivery_id = da.delivery_id
AND di.entity_id = dl.delivery_leg_id
AND di.entity_name = 'WSH_DELIVERY_LEGS'
AND di.status <> 'CANCELED';
select nvl(sum(bill_quantity),0)
from oke_k_billing_events e
where Deliverable_Id = P_Deliverable_Id
and exists ( Select 'Y'
from pa_events p,pa_draft_invoice_items i
where p.event_id=e.pa_event_id
--and i.draft_inv_line_num_credited is null
and p.event_num=i.event_num
and p.project_id=i.project_id
and nvl(p.task_id,0)=nvl(i.task_id,0)
and exists( select 'X' from pa_draft_invoices_all iv
where iv.project_id=i.project_id and iv.draft_invoice_num=i.draft_invoice_num and iv.canceled_flag='Y')
and pa_events_pkg.is_event_billed(p.project_id,p.task_id,p.event_num,p.Bill_trans_bill_amount)='N');
IF l_k_billing_event_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
l_k_billing_event_rec.LAST_UPDATE_DATE := sysdate;
IF l_k_billing_event_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
l_k_billing_event_rec.LAST_UPDATED_BY := Fnd_Profile.Value('USER_ID');
IF l_k_billing_event_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
l_k_billing_event_rec.LAST_UPDATE_LOGIN := Fnd_Profile.Value('LOGIN_ID');
Select oke_k_billing_events_s.nextval into l_event_id from dual;
OKE_DELIVERABLE_BILLING_PVT.Insert_Billing_Info
( P_Deliverable_Id => l_k_billing_event_rec.DELIVERABLE_ID
, P_Billing_Event_Id => l_k_billing_event_rec.BILLING_EVENT_ID
, P_Pa_Event_Id => Null -- not populated at this moment
, P_K_Header_Id => l_k_billing_event_rec.K_HEADER_ID
, P_K_Line_Id => l_k_billing_event_rec.K_LINE_ID
, P_Bill_Event_Type => l_k_billing_event_rec.BILL_EVENT_TYPE
, P_Bill_Event_Date => l_k_billing_event_rec.BILL_EVENT_DATE
, P_Bill_Item_Id => l_k_billing_event_rec.BILL_ITEM_ID
, P_Bill_Line_Id => l_k_billing_event_rec.BILL_LINE_ID
, P_Bill_Chg_Req_Id => l_k_billing_event_rec.BILL_CHG_REQ_ID
, P_Bill_Project_Id => l_k_billing_event_rec.BILL_PROJECT_ID
, P_Bill_Task_Id => l_k_billing_event_rec.BILL_TASK_ID
, P_Bill_Organization_Id => l_k_billing_event_rec.BILL_ORGANIZATION_ID
, P_Bill_Fund_Ref1 => l_k_billing_event_rec.BILL_FUND_REF1
, P_Bill_Fund_Ref2 => l_k_billing_event_rec.BILL_FUND_REF2
, P_Bill_Fund_Ref3 => l_k_billing_event_rec.BILL_FUND_REF3
, P_Bill_Bill_Of_Lading => l_k_billing_event_rec.BILL_BILL_OF_LADING
, P_Bill_Serial_Num => l_k_billing_event_rec.BILL_SERIAL_NUM
, P_Bill_Currency_Code => l_k_billing_event_rec.BILL_CURRENCY_CODE
, P_Bill_Rate_Type => l_k_billing_event_rec.BILL_RATE_TYPE
, P_Bill_Rate_Date => l_k_billing_event_rec.BILL_RATE_DATE
, P_Bill_Exchange_Rate => l_k_billing_event_rec.BILL_EXCHANGE_RATE
, P_Bill_Description => l_k_billing_event_rec.BILL_DESCRIPTION
, P_Bill_Quantity => l_k_billing_event_rec.BILL_QUANTITY
, P_Bill_Unit_Price => l_k_billing_event_rec.BILL_UNIT_PRICE
, P_Revenue_Amount => l_k_billing_event_rec.REVENUE_AMOUNT
, P_created_By => Fnd_Profile.Value('USER_ID')
, P_Creation_Date => SYSDATE
, P_Last_Updated_By => Fnd_Profile.Value('USER_ID')
, P_Last_Update_Login => Fnd_Profile.Value('LOGIN_ID')
, P_Last_Update_Date => SYSDATE);
SELECT
billing_event_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
k_header_id ,
k_line_id ,
deliverable_id ,
bill_event_date ,
bill_event_type ,
pa_event_id ,
bill_item_id ,
bill_line_id ,
bill_chg_req_id ,
bill_project_id ,
bill_task_id ,
bill_organization_id ,
bill_fund_ref1 ,
bill_fund_ref2 ,
bill_fund_ref3 ,
bill_bill_of_lading ,
bill_serial_num ,
bill_currency_code ,
bill_rate_type ,
bill_rate_date ,
bill_exchange_rate ,
bill_description ,
bill_quantity ,
bill_unit_price ,
revenue_amount ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
initiated_flag
from oke_k_billing_events
where Billing_Event_Id = p_k_billing_event_rec.billing_event_id;
l_k_billing_event_rec.last_update_date ,
l_k_billing_event_rec.last_updated_by ,
l_k_billing_event_rec.last_update_login ,
l_k_billing_event_rec.k_header_id ,
l_k_billing_event_rec.k_line_id ,
l_k_billing_event_rec.deliverable_id ,
l_k_billing_event_rec.bill_event_date ,
l_k_billing_event_rec.bill_event_type ,
l_k_billing_event_rec.pa_event_id ,
l_k_billing_event_rec.bill_item_id ,
l_k_billing_event_rec.bill_line_id ,
l_k_billing_event_rec.bill_chg_req_id ,
l_k_billing_event_rec.bill_project_id ,
l_k_billing_event_rec.bill_task_id ,
l_k_billing_event_rec.bill_organization_id ,
l_k_billing_event_rec.bill_fund_ref1 ,
l_k_billing_event_rec.bill_fund_ref2 ,
l_k_billing_event_rec.bill_fund_ref3 ,
l_k_billing_event_rec.bill_bill_of_lading ,
l_k_billing_event_rec.bill_serial_num ,
l_k_billing_event_rec.bill_currency_code ,
l_k_billing_event_rec.bill_rate_type ,
l_k_billing_event_rec.bill_rate_date ,
l_k_billing_event_rec.bill_exchange_rate ,
l_k_billing_event_rec.bill_description ,
l_k_billing_event_rec.bill_quantity ,
l_k_billing_event_rec.bill_unit_price ,
l_k_billing_event_rec.revenue_amount ,
l_k_billing_event_rec.attribute_category ,
l_k_billing_event_rec.attribute1 ,
l_k_billing_event_rec.attribute2 ,
l_k_billing_event_rec.attribute3 ,
l_k_billing_event_rec.attribute4 ,
l_k_billing_event_rec.attribute5 ,
l_k_billing_event_rec.attribute6 ,
l_k_billing_event_rec.attribute7 ,
l_k_billing_event_rec.attribute8 ,
l_k_billing_event_rec.attribute9 ,
l_k_billing_event_rec.attribute10 ,
l_k_billing_event_rec.attribute11 ,
l_k_billing_event_rec.attribute12 ,
l_k_billing_event_rec.attribute13 ,
l_k_billing_event_rec.attribute14 ,
l_k_billing_event_rec.attribute15 ,
l_k_billing_event_rec.initiated_flag ;
SELECT currency_code INTO l_bill_currency
FROM okc_k_headers_b
WHERE id = x_k_billing_event_rec.k_header_id;
IF X_k_billing_event_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
X_k_billing_event_rec.LAST_UPDATED_BY := l_k_billing_event_rec.LAST_UPDATED_BY;
IF X_k_billing_event_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
X_k_billing_event_rec.LAST_UPDATE_LOGIN := l_k_billing_event_rec.LAST_UPDATE_LOGIN;
IF X_k_billing_event_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
X_k_billing_event_rec.LAST_UPDATE_DATE := l_k_billing_event_rec.LAST_UPDATE_DATE;
PROCEDURE update_k_billing_event(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_event_id NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'update_k_billing_event';
OKE_DELIVERABLE_BILLING_PVT.Update_Billing_Info
( P_Deliverable_ID => l_k_billing_event_rec.DELIVERABLE_ID
, P_Billing_Event_ID => l_k_billing_event_rec.BILLING_EVENT_ID
, P_Bill_Event_Type => l_k_billing_event_rec.BILL_EVENT_TYPE
, P_Bill_Event_Date => l_k_billing_event_rec.BILL_EVENT_DATE
, P_Bill_Project_ID => l_k_billing_event_rec.BILL_PROJECT_ID
, P_Bill_Task_ID => l_k_billing_event_rec.BILL_TASK_ID
, P_Bill_Org_ID => l_k_billing_event_rec.BILL_ORGANIZATION_ID
, P_Bill_Line_ID => l_k_billing_event_rec.BILL_LINE_ID
, P_Bill_Chg_Req_ID => l_k_billing_event_rec.BILL_CHG_REQ_ID
, P_Bill_Item_ID => l_k_billing_event_rec.BILL_ITEM_ID
, P_Bill_Description => l_k_billing_event_rec.BILL_DESCRIPTION
, P_Bill_Unit_Price => l_k_billing_event_rec.BILL_UNIT_PRICE
, P_Bill_Quantity => l_k_billing_event_rec.BILL_QUANTITY
, P_Bill_Currency_Code => l_k_billing_event_rec.BILL_CURRENCY_CODE
, P_Bill_Rate_Type => l_k_billing_event_rec.BILL_RATE_TYPE
, P_Bill_Rate_Date => l_k_billing_event_rec.BILL_RATE_DATE
, P_Bill_Exchange_Rate => l_k_billing_event_rec.BILL_EXCHANGE_RATE
, P_Revenue_Amount => l_k_billing_event_rec.REVENUE_AMOUNT
, P_Bill_Of_Lading => l_k_billing_event_rec.BILL_BILL_OF_LADING
, P_Bill_Serial_Num =>l_k_billing_event_rec.BILL_SERIAL_NUM
, P_Bill_Fund_Ref1 => l_k_billing_event_rec.BILL_FUND_REF1
, P_Bill_Fund_Ref2 => l_k_billing_event_rec.BILL_FUND_REF2
, P_Bill_Fund_Ref3 => l_k_billing_event_rec.BILL_FUND_REF3
, P_LAST_UPDATED_BY => FND_PROFILE.VALUE('USER_ID')
, P_LAST_UPDATE_LOGIN => FND_PROFILE.VALUE('LOGIN_ID')
, P_LAST_UPDATE_DATE => SYSDATE
);
END update_k_billing_event;
PROCEDURE delete_k_billing_event(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
p_billing_event_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_BILLING_EVENT';
SELECT PA_EVENT_ID
FROM oke_k_billing_events
WHERE BILLING_EVENT_ID = p_BILLING_EVENT_ID;
SELECT event_num
, project_id
, task_id
, rowid
, bill_amount
FROM pa_events
WHERE event_id = p_PA_EVENT_ID;
OKE_DELIVERABLE_BILLING_PVT.Delete_Billing_Info (
P_Billing_Event_ID => p_billing_event_id
);
END delete_k_billing_event;